<html><div style='background-color:'><DIV>
<DIV>
<DIV></DIV>
<DIV>
<DIV><FONT face=Arial><EM>Hello, I have this problem. Anybody can help me?</EM></FONT></DIV>
<DIV><EM><FONT face=Arial>Thanx!</FONT></EM></DIV>
<DIV><FONT face=Arial></FONT> </DIV>
<DIV><FONT face=Arial><STRONG><U>Bug using ADODB and Visual Basic for acceding PSQL<BR></DIV></U></STRONG></FONT>
<DIV><FONT face=Arial size=2>We have a problem using ADODB (from Visual Basic) and POSTGRESQL when trying to use an unconnected recordset </FONT><FONT face=Arial size=2>which´s property "locktype" is set to "adLockBatchOptimistic" (Rs.locktype=adLockBatchOptimistic). </FONT><FONT face=Arial size=2>When trying to access to a database through ODBC, using ADODB, we have noticed an important delay </FONT><FONT face=Arial size=2>in comparison to other databases such as SQLServer. </FONT><FONT face=Arial size=2>Investigating this problem, we have detected where that delay is produced. </FONT><FONT face=Arial size=2>The problem is produced when we are trying to get an unconnected recordset with the </FONT><FONT face=Arial size=2>"adLockBatchOptimistic" block type. </FONT><FONT face=Arial size=2>When it is trying to execute the query (tracing it) we have seen that arrives three select </FONT><FONT face=Arial size=2>results, but only one of them corresponds to the sentence of the query but the other two are not.</FONT></DIV>
<DIV><FONT face=Arial size=2>We conclude that the last two results are important to recover the recordset structure </FONT><FONT face=Arial size=2>that will be disconnected later.</FONT></DIV>
<DIV><FONT face=Arial size=2>The problem is that one of this two extra queries runs a complete select over the table that we </FONT><FONT face=Arial size=2>are accessing with the written select.</FONT><FONT face=Arial size=2>This table has a lot of records, and it turns the answer really slow because it brings all the </FONT><FONT face=Arial size=2>talbe recordsets.</FONT><FONT face=Arial size=2>But this records are not kept into the recordset. It only brings the data requested by the </FONT><FONT face=Arial size=2>written sentence.</FONT><FONT face=Arial size=2>Now, we present the Visual Basic code used to access the database and, after that, a log obtained </FONT><FONT face=Arial size=2>through POSTGRE ODBC.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV><FONT face=Arial size=2>
<DIV><BR><U><STRONG><FONT size=3>Visual Basic Code</FONT></STRONG></U><BR><BR> <BR> Dim objRs As New ADODB.Recordset<BR> Dim objConn As New ADODB.Connection<BR> Dim strSql As String<BR> Dim strCadenaConexion As String<BR> <BR> strCadenaConexion = _<BR> "DSN=contabilidad_psql;DATABASE=contabilidad;" & _<BR> "SERVER=192.168.1.41;PORT=5432;UID=credito;PWD=;"<BR> <BR> objConn.Open strCadenaConexion<BR> <BR> strSql = "select * from agentes where id_sucursal = 7 and id_agente = 100"<BR> <BR> objRs.CursorLocation = adUseClient<BR> objRs.CursorType = adOpenStatic<BR> objRs.LockType = adLockBatchOptimistic<BR> <BR> Set objRs.ActiveConnection = objConn<BR> objRs.Open strSql<BR> <BR> objRs.ActiveConnection = Nothing<BR> objConn.Close<BR> <BR> MsgBox "Cant: " & objRs.RecordCount<BR> <BR> <BR> <BR><U><FONT size=3><STRONG>Log ODBC POSTGRE <BR></STRONG></FONT></U><BR> <BR> conn=409745072, query='select * from agentes where id_sucursal = 7 and id_agente = 100'<BR> [ fetched 1 rows ]<BR> conn=409745072, query='SELECT * FROM agentes'<BR> [ fetched 10773 rows ]<BR> conn=409745072, query='select ta.attname, ia.attnum from pg_attribute ta, pg_attribute ia, pg_class c, pg_index i where c.relname = 'agentes' AND c.oid = i.indrelid AND i.indisprimary = 't' AND ia.attrelid = i.indexrelid AND ta.attrelid = i.indrelid AND ta.attnum = i.indkey[ia.attnum-1] order by ia.attnum'<BR> [ fetched 2 rows ]<BR> conn=409745072, PGAPI_Disconnect<BR></DIV>
<DIV>I hope you could help us to solve this problem.</DIV>
<DIV>Thank you!</DIV>
<DIV>Gastón</DIV></FONT></DIV></DIV></DIV></div><br clear=all><hr>Descargue GRATUITAMENTE MSN Explorer en <a href='http://g.msn.com/1HM105901/N'>http://explorer.yupimsn.com/intl.asp</a>.<br></html>