Re: Odbc, postgresql and disconnected recordsets

Please check the disallow premature option.

regards,
Hiroshi Inoue

Gaston Micheri wrote:
>
> Hello, 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