Thread: ODBC Problem
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Greetings people,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"><span style="mso-tab-count:1"> </span>Before I go nuts, I wonder if anyone has solved
thisproblem before; I have been using the ODBC driver for our <span class="SpellE">Postgres</span> server to develop
softwarein Visual Basic with great success until just recently.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Symptoms of the problem are that queries on some machines execute correctly, but taking the same
codeand executing it on another machine returns empty <span class="SpellE">recordsets</span>.<span
style="mso-spacerun:yes"> </span>I made sure all the drivers were the same and that the latest MDAC 2.7 was installed
onall machines.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">On further investigation, I found that on the affected machines, queries to tables with an index
comeback with an empty <span class="SpellE">recordset</span>.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Win2k Terminal Server SP2: okay – queries return data</span></font><p class="MsoNormal"><span
class="SpellE"><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">WinXP</span></font></span><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Beta2: okay – queries return data</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial">WinNT4 Server: only queries on some tables return data</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial">Win2k Server SP2: only queries on some tables return data</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I guess this must be something to do with the setup of the ODBC driver on these machines, but I
can’tfor the life of me find any difference in settings. <span style="mso-spacerun:yes"> </span>All the machines use
identicalconnect strings.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I looked at the ODBC logs to find something that was failing and couldn’t really see any thing
bad:</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal" style="margin-left:36.0pt"><span class="SpellE"><span
class="GramE"><fontface="Courier New" size="2"><span style="font-size:10.0pt;
font-family:"Courier New"">conn</span></font></span></span><span class="GramE"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">=</span></font></span><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">35795448, <span
class="SpellE">SQLDriverConnect</span>(out)='DRIVER={<spanclass="SpellE">PostGreSQL</span>};DATABASE=<span
class="SpellE">bmf;SERVER</span>=<spanclass="SpellE">bm</span></span></font><p class="MsoNormal"
style="margin-left:36.0pt"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">f.some.domain<span
class="GramE">;PORT</span>=5432;UID=postgres;PWD=xxxxx;READONLY=0;PROTOCOL=6.4;FA</span></font><pclass="MsoNormal"
style="margin-left:36.0pt"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">KEOIDINDEX=0<span
class="GramE">;SHOWOIDCOLUMN</span>=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS='</span></font><p
class="MsoNormal"style="margin-left:36.0pt"><span class="SpellE"><span class="GramE"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt;
font-family:"Courier New"">conn</span></font></span></span><span class="GramE"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">=</span></font></span><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">35795448, query='SELECT * FROM <span
class="SpellE">userservices</span>WHERE <span class="SpellE">printform</span>=true AND <span
class="SpellE">formpr</span></span></font><pclass="MsoNormal" style="margin-left:36.0pt"><span class="SpellE"><span
class="GramE"><fontface="Courier New" size="2"><span style="font-size:10.0pt;
font-family:"Courier New"">inted</span></font></span></span><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew""> IS NULL'</span></font><p class="MsoNormal"
style="margin-left:36.0pt"><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New""><spanstyle="mso-spacerun:yes"> </span><span class="GramE">[ fetched</span> 1 rows ]</span></font><p
class="MsoNormal"style="margin-left:36.0pt"><span class="SpellE"><span class="GramE"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt;
font-family:"Courier New"">conn</span></font></span></span><span class="GramE"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">=</span></font></span><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">35795448, <span class="SpellE">SQLDisconnect</span></span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">So it looked like the row I wanted was retrieved but the <span class="SpellE">recordset</span> still
remainedempty </span></font><font face="Wingdings" size="2"><span
style="font-size:10.0pt;font-family:Wingdings;mso-ascii-font-family:
Arial;mso-hansi-font-family:Arial;mso-bidi-font-family:Arial;mso-char-type:
symbol;mso-symbol-font-family:Wingdings"><span style="mso-char-type:symbol;
mso-symbol-font-family:Wingdings">L</span></span></font><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"></span></font><pclass="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I believe the server is running <span class="SpellE">Postgres</span> <span class="GramE">7.0.3
:</span></span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal" style="margin-left:36.0pt"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"">[<span class="SpellE">root@bmf</span> bin]<span
class="GramE">#./</span><span class="SpellE">psql</span> -V</span></font><p class="MsoNormal"
style="margin-left:36.0pt"><spanclass="SpellE"><span class="GramE"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New"">psql</span></font></span></span><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew""> (<span class="SpellE">PostgreSQL</span>) 7.0.3</span></font><p
class="MsoNormal"style="margin-left:36.0pt"><span class="GramE"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">contains</span></font></span><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew""> <span class="SpellE">readline</span>, history
support</span></font><pclass="MsoNormal" style="margin-left:36.0pt"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">Portions Copyright (c) 1996-2000, <span
class="SpellE">PostgreSQL</span>,Inc</span></font><p class="MsoNormal" style="margin-left:36.0pt"><font face="Courier
New"size="2"><span style="font-size:10.0pt;font-family:"Courier New"">Portions Copyright (c) 1996 Regents of the
</span></font><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">University</span></font><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">of </span></font><font face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New"">California</span></font><fontface="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier
New""></span></font><pclass="MsoNormal" style="margin-left:36.0pt"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"">Read the file COPYRIGHT or use the command \copyright to see
the</span></font><pclass="MsoNormal" style="margin-left:36.0pt"><span class="GramE"><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"">usage</span></font></span><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New""> and distribution terms.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">All machines are using the same ODBC driver version 6.5, PSQLODBC.DLL </span></font><font
face="Arial"size="2"><span style="font-size:10.0pt;font-family:Arial">4/2/2000</span></font><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial"></span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">If anyone knows if there is a known problem with this combo please let me know and I will have a
caseto get the admin to upgrade.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Cheers & TIA,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"><span style="mso-tab-count:1"> </span><span class="GramE">Jamz.</span></span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt;
font-family:"Courier New";mso-no-proof:yes">=-=-=-=-=-=-=-=-=-=-=-=-</span></font><span
style="mso-no-proof:yes"></span><pclass="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt;
font-family:"Courier New";mso-no-proof:yes">"We should have laser guns!"</span></font><span
style="mso-no-proof:yes"></span><pclass="MsoNormal"><font face="Courier New" size="2"><span style="font-size:10.0pt;
font-family:"Courier New";mso-no-proof:yes"> - Andrew Hill (Post Support Call)</span></font><p
class="MsoNormal"><fontface="Times New Roman" size="3"><span style="font-size:
12.0pt"> </span></font></div>
Hello,the PostgreSQL ODBC driver is up to 7.01.0006 now. Try this out. It
is available on odbc.postgresql.org. (It may also be worth looking at the
archives of the pgsql-odbc mailing list or on techdocs.postgresql.org).
- Stuart
> -----Original Message-----
> From: jamzb@airnet.com.au [SMTP:jamzb@airnet.com.au]
> Sent: Wednesday, July 18, 2001 12:58 AM
> To: pgsql-interfaces@postgresql.org
> Subject: ODBC Problem
>
> Greetings people,
> Before I go nuts, I wonder if anyone has solved this problem
> before; I have been using the ODBC driver for our Postgres server to
> develop software in Visual Basic with great success until just recently.
>
> Symptoms of the problem are that queries on some machines execute
> correctly, but taking the same code and executing it on another machine
> returns empty recordsets. I made sure all the drivers were the same and
> that the latest MDAC 2.7 was installed on all machines.
>
> On further investigation, I found that on the affected machines, queries
> to tables with an index come back with an empty recordset.
>
> Win2k Terminal Server SP2: okay - queries return data
> WinXP Beta2: okay - queries return data
> WinNT4 Server: only queries on some tables return data
> Win2k Server SP2: only queries on some tables return data
>
> I guess this must be something to do with the setup of the ODBC driver on
> these machines, but I can't for the life of me find any difference in
> settings. All the machines use identical connect strings.
>
> I looked at the ODBC logs to find something that was failing and couldn't
> really see any thing bad:
>
> conn=35795448,
> SQLDriverConnect(out)='DRIVER={PostGreSQL};DATABASE=bmf;SERVER=bm
> f.some.domain;PORT=5432;UID=postgres;PWD=xxxxx;READONLY=0;PROTOCOL=6.4;FA
> KEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTIN
> GS='
> conn=35795448, query='SELECT * FROM userservices WHERE printform=true AND
> formpr
> inted IS NULL'
> [ fetched 1 rows ]
> conn=35795448, SQLDisconnect
>
> So it looked like the row I wanted was retrieved but the recordset still
> remained empty :-(
>
> I believe the server is running Postgres 7.0.3 :
>
> [root@bmf bin]# ./psql -V
> psql (PostgreSQL) 7.0.3
> contains readline, history support
> Portions Copyright (c) 1996-2000, PostgreSQL, Inc
> Portions Copyright (c) 1996 Regents of the University of California
> Read the file COPYRIGHT or use the command \copyright to see the
> usage and distribution terms.
>
> All machines are using the same ODBC driver version 6.5, PSQLODBC.DLL
> 4/2/2000
>
> If anyone knows if there is a known problem with this combo please let me
> know and I will have a case to get the admin to upgrade.
>
> Cheers & TIA,
> Jamz.
>
>
>
> =-=-=-=-=-=-=-=-=-=-=-=-
> "We should have laser guns!"
> - Andrew Hill (Post Support Call)
>