[ psqlodbc-Bugs-1000681 ] Error when using ODBC driver with Microsoft Access

Bugs item #1000681, was opened at 2006-07-06 13:54
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000681&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Nobody (None)
Assigned to: Nobody (None)
Summary: Error when using ODBC driver with Microsoft Access

Initial Comment:
I have a similar problem as http://pgfoundry.org/tracker/index.php?func=detail&aid=1000528&group_id=1000125&atid=538, although not after a foreign key error, it just occurs at random multiple times a day.

I am using Access 2002, with tables link to PostgreSQL using version 8.01.02.00 ANSI of the ODBC driver. After logging in to Access, everything will work without problem for some time, and then just suddenly fail.

Once the problem occurs any table I open contains the correct number of rows, but ever column contains the text '#Name?'. And I will get something similar to the following in the postgreSQL logs:

postgres[19930]: [22-1] 2006-07-06 12:33:51 BST guycallaghan research ERROR: invalid input syntax for integer: "^C"
postgres[19930]: [22-2] 2006-07-06 12:33:51 BST guycallaghan research STATEMENT: SELECT
postgres[19930]: [22-3] "codeid","colourcode","hexvalue","image","timestamp","userid" FROM "admin"."code" WHERE "codeid" = '^C' OR
postgres[19930]: [22-4] "codeid" = '^A' OR "codeid"= '^B' OR "codeid" = '^D' OR "codeid" = '^D' OR "codeid" = '^D' OR "codeid" = '^D' OR
postgres[19930]: [22-5] "codeid" = '^D' OR "codeid"= '^D' OR "codeid" = '^D'

Restarting Access always resolves the problem.

----------------------------------------------------------------------

Comment By: Nobody (None)
Date: 2007-02-28 10:57

Message:
<a href= http://knifeinfo.com.ua/forum/adresa-magazinov-nozhi.html
></a> [url=http://knifeinfo.com.ua/forum/adresa-magazinov-nozhi.html
][/url] <a href= http://knifeinfo.com.ua/forum/aksessuary-dlya-nozhey.html
></a> [url=http://knifeinfo.com.ua/forum/aksessuary-dlya-nozhey.html
][/url] <a href= http://knifeinfo.com.ua/forum/amerikanskie-nozhi.html
></a> [url=http://knifeinfo.com.ua/forum/amerikanskie-nozhi.html
][/url] <a href= http://knifeinfo.com.ua/forum/antikvarnye-nozhi-iz-damasskoy-stali.html
></a> [url=http://knifeinfo.com.ua/forum/antikvarnye-nozhi-iz-damasskoy-stali.html
][/url] <a href= http://knifeinfo.com.ua/forum/antikvarnyy-magazin--gde-est-nastoyaschiy-katana-16-veka.html , 16
></a> [url=http://knifeinfo.com.ua/forum/antikvarnyy-magazin--gde-est-nastoyaschiy-katana-16-veka.html , 16
][/url]

----------------------------------------------------------------------

Comment By: Nobody (None)
Date: 2007-02-28 10:57

Message:
<a href= http://knifeinfo.com.ua/forum/adresa-magazinov-nozhi.html
></a> [url=http://knifeinfo.com.ua/forum/adresa-magazinov-nozhi.html
][/url] <a href= http://knifeinfo.com.ua/forum/aksessuary-dlya-nozhey.html
></a> [url=http://knifeinfo.com.ua/forum/aksessuary-dlya-nozhey.html
][/url] <a href= http://knifeinfo.com.ua/forum/amerikanskie-nozhi.html
></a> [url=http://knifeinfo.com.ua/forum/amerikanskie-nozhi.html
][/url] <a href= http://knifeinfo.com.ua/forum/antikvarnye-nozhi-iz-damasskoy-stali.html
></a> [url=http://knifeinfo.com.ua/forum/antikvarnye-nozhi-iz-damasskoy-stali.html
][/url] <a href= http://knifeinfo.com.ua/forum/antikvarnyy-magazin--gde-est-nastoyaschiy-katana-16-veka.html , 16
></a> [url=http://knifeinfo.com.ua/forum/antikvarnyy-magazin--gde-est-nastoyaschiy-katana-16-veka.html , 16
][/url]

----------------------------------------------------------------------

Comment By: Nobody (None)
Date: 2007-02-28 00:20

Message:
<a href= http://cholesterolgood.page.by/cholestae/cholestae.html cholestae
></a> [url=http://cholesterolgood.page.by/cholestae/cholestae.html cholestae
][/url] <a href= http://cholesterolgood.page.by/cholestae/cholesterol-diet-healthy-high.html cholesterol diet healthy high
></a> [url=http://cholesterolgood.page.by/cholestae/cholesterol-diet-healthy-high.html cholesterol diet healthy high
][/url] <a href= http://cholesterolgood.page.by/cholestae/cholesterol-alternative-treatments.html cholesterol alternative treatments
></a> [url=http://cholesterolgood.page.by/cholestae/cholesterol-alternative-treatments.html cholesterol alternative treatments
][/url] <a href= http://cholesterolgood.page.by/cholestae/cholesterol-alternative-treatment.html cholesterol alternative treatment
></a> [url=http://cholesterolgood.page.by/cholestae/cholesterol-alternative-treatment.html cholesterol alternative treatment
][/url] <a href= http://cholesterolgood.page.by/cholestae/low-cholesterol-meal-plan.html low cholesterol meal plan
></a> [url=http://cholesterolgood.page.by/cholestae/low-cholesterol-meal-plan.html low cholesterol meal plan
][/url]

----------------------------------------------------------------------

Comment By: Nobody (None)
Date: 2007-02-28 00:20

Message:
<a href= http://cholesterolgood.page.by/cholestae/cholestae.html cholestae
></a> [url=http://cholesterolgood.page.by/cholestae/cholestae.html cholestae
][/url] <a href= http://cholesterolgood.page.by/cholestae/cholesterol-diet-healthy-high.html cholesterol diet healthy high
></a> [url=http://cholesterolgood.page.by/cholestae/cholesterol-diet-healthy-high.html cholesterol diet healthy high
][/url] <a href= http://cholesterolgood.page.by/cholestae/cholesterol-alternative-treatments.html cholesterol alternative treatments
></a> [url=http://cholesterolgood.page.by/cholestae/cholesterol-alternative-treatments.html cholesterol alternative treatments
][/url] <a href= http://cholesterolgood.page.by/cholestae/cholesterol-alternative-treatment.html cholesterol alternative treatment
></a> [url=http://cholesterolgood.page.by/cholestae/cholesterol-alternative-treatment.html cholesterol alternative treatment
][/url] <a href= http://cholesterolgood.page.by/cholestae/low-cholesterol-meal-plan.html low cholesterol meal plan
></a> [url=http://cholesterolgood.page.by/cholestae/low-cholesterol-meal-plan.html low cholesterol meal plan
][/url]

----------------------------------------------------------------------

Comment By: Nobody (None)
Date: 2007-02-28 00:20

Message:
<a href= http://cholesterolgood.page.by/cholestae/cholestae.html cholestae
></a> [url=http://cholesterolgood.page.by/cholestae/cholestae.html cholestae
][/url] <a href= http://cholesterolgood.page.by/cholestae/cholesterol-diet-healthy-high.html cholesterol diet healthy high
></a> [url=http://cholesterolgood.page.by/cholestae/cholesterol-diet-healthy-high.html cholesterol diet healthy high
][/url] <a href= http://cholesterolgood.page.by/cholestae/cholesterol-alternative-treatments.html cholesterol alternative treatments
></a> [url=http://cholesterolgood.page.by/cholestae/cholesterol-alternative-treatments.html cholesterol alternative treatments
][/url] <a href= http://cholesterolgood.page.by/cholestae/cholesterol-alternative-treatment.html cholesterol alternative treatment
></a> [url=http://cholesterolgood.page.by/cholestae/cholesterol-alternative-treatment.html cholesterol alternative treatment
][/url] <a href= http://cholesterolgood.page.by/cholestae/low-cholesterol-meal-plan.html low cholesterol meal plan
></a> [url=http://cholesterolgood.page.by/cholestae/low-cholesterol-meal-plan.html low cholesterol meal plan
][/url]

----------------------------------------------------------------------

Comment By: Greg Campbell (gregc)
Date: 2006-08-09 13:09

Message:
I highly recommend ConnectionTimeout of 0 where pgodbc is used with Jet/Access. That means never timeout. Many problems seem to disappear.

Your latest posting seems to imply a timeout factor, and a multi-thread problem, possibly a connection pooling issue. One thread seems to stay alive, while a secondary thread, used after a timeout, fails to reconnect, or more correctly it is a thread that tries to reuse a connection that has timed out. It obviously is unaware of the timeout or the Access code is ill-prepared to handle the timeout on that thread. This means the only thing you can do is to not allow the timeouts.

You might also consider getting an account/logging in on pgfoundry so that we have some name to address you by.

----------------------------------------------------------------------

Comment By: Nobody (None)
Date: 2006-08-09 09:02

Message:
Greg,

The standard procedure for Access when you open a table appears to be ... First it fetches a list of primary keys with one query. It then follows-up with a second query to fetch the actual full row data.

This procedure is the same whether the connection is the first one, or a re-created one after the timeout.

When I capture the network traffic, I can see the first query always works, irrespective of whether it is the intital database connection, or one re-connected after a timeout.

It is the second query that fails when we have a re-connected connection.

The default setting for HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.\Engines\ODBC\Connect
ionTimeout is 600 (i.e. 10 minutes). I have increased ours to 3600 (i.e. one hour).

----------------------------------------------------------------------

Comment By: Greg Campbell (gregc)
Date: 2006-08-07 14:42

Message:
You indicate a time factor, and after that Access sends these querie do not use the primary keys and queries each row by all possible values. Yes?

What is your setting for
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.\Engines\ODBC\ConnectionTimeout

----------------------------------------------------------------------

Comment By: Nobody (None)
Date: 2006-08-07 11:24

Message:
Hi Luf,

I eliminated the protocol change (see comment 2006-07-10 11:52), but the problem still occurred.

I will try the latest dll as suggested.

For anybody else suffering with this problem, you can reduce the impact by extending the Jet ODBC connection timeout by editing the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC\ConnectionTimeout

----------------------------------------------------------------------

Comment By: Ludek Finstrle (luf)
Date: 2006-07-24 10:49

Message:
Hello,

the change from 6.4 to 7.4 protocol is significant. You should use 7.4.

Could you try the latest dll at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html
?
If the problem still persist it would be nice you post us the mylog output (maybe only the problematic part - it depends on mylog size).

Regards,

Luf

----------------------------------------------------------------------

Comment By: Nobody (None)
Date: 2006-07-12 11:18

Message:
I captured the network traffic to see if I can spot any difference.

As with the myLog, it would appear the connection details are identical, the difference occurs with the queries.

Opening a table triggers two queries. The first fetches the primary key, the second then fetches the full row for each primary key.

With the initial connection and the connection after timeout, the query to fetch primary keys is identical, and correctly returns the data both times. I.e.

Q...ASELECT "admin"."code"."codeid" FROM "admin"."code" .

T..."..codeid...o6..............D..........3D..........1D..........2D..........4C....SELECT.Z....I

(NB: There are 4 rows in this table with primary keys are 1,2,3,4)

The followup query is where the difference is.

Initial connection:

Q...:SELECT "codeid","colourcode","hexvalue","image","timestamp","userid" FROM "admin"."code" WHERE "codeid" = '3' OR "codeid" = '1' OR "codeid" = '2' OR "codeid" = '4' OR "codeid" = '4' OR "codeid" = '4' OR "codeid" = '4' OR "codeid" = '4' OR "codeid" = '4' OR "codeid" = '4'.

However, after a timeout and reconnection we get ...

Q...:SELECT "codeid","colourcode","hexvalue","image","timestamp","userid" FROM "admin"."code" WHERE "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.' OR "codeid" = '.'.

Which the server then responds with an error:

E...QSERROR.C22P02.Minvalid input syntax for integer: ".".Fint8.c.L102.Rscanint8..Z....I

----------------------------------------------------------------------

Comment By: Nobody (None)
Date: 2006-07-10 11:52

Message:
I tried the latest driver 08.02.0002 but got the same problem. However I seem to of eliminated the protocol difference as significant.

With my DSN entry set to use the latest driver, I recreated the links to the tables in Access. Now myLog shows the protocol is set to '7.4-1' on both the initial connection, and any reconnections.

In fact myLog is now nearly identical for both the initial connection and the reconnection. The only difference being that the initial connection has the following additional entries:

[2244][SQLGetConnectAttrW][2244]PGAPI_GetConnectAttr 30002
[2244]PGAPI_GetConnectOption: entering...
[2244]CONN ERROR: func=PGAPI_GetConnectOption, desc='', errnum=205, errmsg='Unknown connect option (Get)'
[2244]CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=30002', errnum=205, errmsg='Unknown connect option (Get)'
[2244][SQLSetConnectAttrW][2244]PGAPI_SetConnectAttr 30002 9a81478
[2244]PGAPI_SetConnectOption: entering fOption = 30002 vParam = 162010232
[2244]CONN ERROR: func=PGAPI_SetConnectOption, desc='', errnum=205, errmsg='Unknown connect option (Set)'
[2244]Microsoft Jet !!!!

----------------------------------------------------------------------

Comment By: Nobody (None)
Date: 2006-07-07 12:02

Message:
I switched on myLog and spotted a minor difference in the connection string when it reconnects after the connection closes.

After login, in mylog produces a set of entries including the following line ...

[2952]copyAttributes: DSN='PGResearch',server='xxxxx.co.uk',dbase='research',user='xxxxx',passwd='xxxxx',port='5432',onlyread='0',protocol='6.2',conn_settings='',disallow_premature=0)

... after the connection closes, if I open a table the log shows a new connection attempt, but this time there is a minor difference ...

[2952]copyAttributes: DSN='PGResearch',server='xxxxx.co.uk',dbase='research',user='xxxxx',passwd='xxxxx',port='5432',onlyread='0',protocol='7.4',conn_settings='',disallow_premature=0)

Could protocol='7.4' be significant?

----------------------------------------------------------------------

Comment By: Nobody (None)
Date: 2006-07-06 15:16

Message:
I switched on the CommLog and got the following:

I login to Access and the following appears in the log ...

CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=30002', errnum=205, sqlstate=, errmsg='Unknown connect option (Get)'
------------------------------------------------------------
henv=162610200, conn=162610256, status=0, num_stmts=16
conn=162610256, PGAPI_DriverConnect( in)='DSN=PGResearch;UID=xxxxxxx;PWD=xxxxxxx;DATABASE=research;SERVER=xxxxxxx.co.uk;PORT=5432;SSLMODE=prefer;A6=;A7=100;A8=8192;B0=4000;B1=8190;BI=0;C2=dd_;CX=1b50389;', fDriverCompletion=0
DSN info: DSN='PGResearch',server='xxxxxxx.co.uk',port='5432',dbase='research',user='xxxxxxx',passwd='xxxxx'
onlyread='0',showoid='0',fakeoidindex='0',showsystable='0'
conn_settings='',conn_encoding='OTHER'
translation_dll='',translation_option=''

... things then run fine, but if I leave Access for a while and then return the log show a new entry and this is when the error occurs ...

conn=162610256, PGAPI_DriverConnect( in)='DSN=PGResearch;UID=xxxxxxx;PWD=xxxxxxx;DATABASE=research;SERVER=xxxxxxx.co.uk;PORT=5432;SSLMODE=prefer;A6=;A7=100;A8=8192;B0=4000;B1=8190;BI=0;C2=dd_;CX=1b50389;', fDriverCompletion=0
DSN info: DSN='PGResearch',server='xxxxxxx.co.uk',port='5432',dbase='research',user='xxxxxxx',passwd='xxxxx'
onlyread='0',showoid='0',fakeoidindex='0',showsystable='0'
conn_settings='',conn_encoding='OTHER'
translation_dll='',translation_option=''

----------------------------------------------------------------------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000681&group_id=1000125