Thread: Odbc, postgresql and disconnected recordsets
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
_________________________________________________________________
Descargue GRATUITAMENTE MSN Explorer en http://explorer.yupimsn.com/intl.asp
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