Help me please!! Here I describe my problem.
Thank you!!

Bug using ADODB and Visual Basic for acceding PSQL

We have a problem using ADODB (from Visual Basic) and POSTGRESQL when trying 
to use an unconnected recordset whichï¿½s property "locktype" is set to 
"adLockBatchOptimistic" (Rs.locktype=adLockBatchOptimistic). When trying to 
access to a database through ODBC, using ADODB, we have noticed an important 
delay in comparison to other databases such as SQLServer. Investigating this 
problem, we have detected where that delay is produced. The problem is 
produced when we are trying to get an unconnected recordset with the 
"adLockBatchOptimistic" block type. When it is trying to execute the query 
(tracing it) we have seen that arrives three select results, but only one of 
them corresponds to the sentence of the query but the other two are not.
We conclude that the last two results are important to recover the recordset 
structure that will be disconnected later.
The problem is that one of this two extra queries runs a complete select 
over the table that we are accessing with the written select.This table has 
a lot of records, and it turns the answer really slow because it brings all 
the talbe recordsets.But this records are not kept into the recordset. It 
only brings the data requested by the written sentence.Now, we present the 
Visual Basic code used to access the database and, after that, a log 
obtained through POSTGRE ODBC.


Visual Basic Code


  Dim objRs As New ADODB.Recordset
  Dim objConn As New ADODB.Connection
  Dim strSql As String
  Dim strCadenaConexion As String

  strCadenaConexion = _
      "DSN=contabilidad_psql;DATABASE=contabilidad;" & _
      "SERVER=192.168.1.41;PORT=5432;UID=credito;PWD=;"

  objConn.Open strCadenaConexion

  strSql = "select * from agentes where id_sucursal = 7 and id_agente = 100"

  objRs.CursorLocation = adUseClient
  objRs.CursorType = adOpenStatic
  objRs.LockType = adLockBatchOptimistic

  Set objRs.ActiveConnection = objConn
  objRs.Open strSql

  objRs.ActiveConnection = Nothing
  objConn.Close

  MsgBox "Cant: " & objRs.RecordCount



Log ODBC POSTGRE


conn=409745072, query='select * from agentes where id_sucursal = 7 and 
id_agente = 100'
[ fetched 1 rows ]
conn=409745072, query='SELECT * FROM agentes'
[ fetched 10773 rows ]
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'
[ fetched 2 rows ]
conn=409745072, PGAPI_Disconnect

I hope you could help us to solve this problem.
Thank you!
Gaston.-

_________________________________________________________________
MSN Photos es la manera mï¿½s sencilla de compartir e imprimir sus fotos: 
http://photos.latam.msn.com/Support/WorldWide.aspx


Wed, 06 Mar 2002 20:19:23 -0300
"Gaston Micheri" <ggmsistemas@hotmail.com>
Odbc, postgresql and disconnected recordsets