A consult - Mailing list pgadmin-support

From Gastón Micheri
Subject A consult
Date
Msg-id F65hpDrwuce0Xh5AS8q000092cc@hotmail.com
Whole thread Raw
Responses Re: A consult
List pgadmin-support
<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 /> 

pgadmin-support by date:

Previous
From: Dave Page
Date:
Subject: Re: beginner problem connecting to database
Next
From: "Tim Finch, FosterFinch Ltd"
Date:
Subject: Re: A consult