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

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

am 07.08.2006 16:42:16 von noreply

Bugs item #1000681, was opened at 2006-07-06 16:54
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=10006 81&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=10005 28&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: Greg Campbell (gregc)
Date: 2006-08-07 17: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\Co nnectionTimeout



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

Comment By: Nobody (None)
Date: 2006-08-07 14: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\C onnectionTimeout



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

Comment By: Ludek Finstrle (luf)
Date: 2006-07-24 13: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 14: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 14: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 15: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 18: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=researc h;SERVER=xxxxxxx.co.uk;PORT=5432;SSLMODE=prefer;A6=;A7=100;A 8=8192;B0=4000;B1=8190;BI=0;C2=dd_;CX=1b50389;', fDriverCompletion=0
DSN info: DSN='PGResearch',server='xxxxxxx.co.uk',port='5432',dbase='r esearch',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=researc h;SERVER=xxxxxxx.co.uk;PORT=5432;SSLMODE=prefer;A6=;A7=100;A 8=8192;B0=4000;B1=8190;BI=0;C2=dd_;CX=1b50389;', fDriverCompletion=0
DSN info: DSN='PGResearch',server='xxxxxxx.co.uk',port='5432',dbase='r esearch',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=10006 81&group_id=1000125

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend