Thread: A consult

<div style="background-color:"><div></div><div><div><font face="Arial"><em>Hello, I have a
problem.</em></font></div><div><fontface="Arial"></font> </div><div><font face="Arial"><strong><u>Bug using ADODB and
VisualBasic for acceding PSQL<br /></u></strong></font></div><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´sproperty "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><fontface="Arial" size="2">in comparison to other databases such as SQLServer. </font><font face="Arial"
size="2">Investigatingthis problem, we have detected where that delay is produced. </font><font face="Arial"
size="2">Theproblem 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
(tracingit) we have seen that arrives three select </font><font face="Arial" size="2">results, but only one of them
correspondsto the sentence of the query but the other two are not.</font></div><div><font face="Arial" size="2">We
concludethat the last two results are important to recover the recordset structure </font><font face="Arial"
size="2">thatwill be disconnected later.</font></div><div><font face="Arial" size="2">The problem is that one of this
twoextra queries runs a complete select over the table that we </font><font face="Arial" size="2">are accessing with
thewritten select.</font><font face="Arial" size="2">This table has a lot of records, and it turns the answer really
slowbecause it brings all the </font><font face="Arial" size="2">talbe recordsets.</font><font face="Arial"
size="2">Butthis 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
accessthe database and, after that, a log obtained </font><font face="Arial" size="2">through POSTGRE
ODBC.</font></div><div><fontface="Arial" size="2"></font> </div><font face="Arial" size="2"><div><br /><u><strong><font
size="3">VisualBasic Code</font></strong></u><br /><br /> <br />  Dim objRs As New ADODB.Recordset<br />  Dim objConn
AsNew 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>LogODBC POSTGRE <br /></strong></font></u><br /> <br /> conn=409745072, query='select * from agentes
whereid_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_attributeia, 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>Thankyou!</div><div>Gastón</div></font></div></div><br clear="all" /><hr />Hable con sus amigos en
línea,pruebe MSN Messenger: <a href="http://g.msn.com/1HM105901/M">http://messenger.msn.es</a><br /> 

Gaston,

Hi. you may want to cross post this in the pgsql-odbc and pgsql-general list (both can be signed up on at majordomo.postgresql.org), as that may be a better place for the type of support you are after. You have posted this in the pgAdmin list, a list for discussing specific problems with a VB written program called pgAdmin, a front end tool to administer PostgreSQL, not a email-list for general VB/ODBC related issues. Personally I cant help you with this one although the other pgAdmin programmers (who use VB & PGSQL) may be able to, and if so will reply accordingly.

Cheers,
Tim.

At 16:57 06/03/2002 -0300, Gastón Micheri wrote:

Hello, I have a problem.
 
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 que! ry 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!
Gastón

---

Hable con sus amigos en línea, pruebe MSN Messenger: http://messenger.msn.es