Skip site navigation (1) Skip section navigation (2)

MS Access and #deleted due to timeouts

From: "Greg Campbell" <greg(dot)campbell(at)us(dot)michelin(dot)com>
To: "Pgsql-Odbc (E-mail)" <pgsql-odbc(at)postgresql(dot)org>
Subject: MS Access and #deleted due to timeouts
Date: 2005-07-12 22:16:48
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-odbc
I have distinctly noticed that when I leave my Access database with 
PostgreSQL linked tables for any considerable length of time, all fields 
in ALL linked tables say #deleted in data table view (and I'd assume 
form view as well).

I have OIDS and Primary keys on the tables. Access is aware of this. And 
I have row versioning turned on.

I turned on the ODBC trace.
What I see is a simple select query does a SQLExecDirectW (e.g. "SELECT 
"public"."employees"."first_name" from "public"."employees" /0").
Then it does an SQLFetch and SQLGetData for each row. (No obvious data 

Then it does a SQLPrepareW that look like
"SELECT "public"."employees"."first_name" FROM "public"."employees" 
WHERE  "emp_id"=? or "emp_id"=? or "emp_id"=? or "emp_id"=? or 
"emp_id"=? or "emp_id"=? or "emp_id"=? or "emp_id"=? or "emp_id"=? or 
"emp_id"=? \0"

Then it does a SQLBindParamter for each of the 10 instance of emp_id. 
Why it enumerates the same paramter 10 times is a mystery to me.

Then it does a fetch and three (3) SQLGetDatas for each row.
I don't know what the first ones doing. The 2nd returns data,data_type, 
data_length. The 3rd,...well I'm not sure what that one's doing.

In data view I see my data.

Then I let 20 or 30 minutes pass.

Same as above, except after binding parameters, my first fetch returns a 
code 100 (SQL_NO_DATA_FOUND).
In the data view I see a #deleted for each record.

Here are my question?
1. Are two passes of fetches absolutely necessary each time I run my query?
2. What's up with all the parameters to my primary key.
3. I noticed that my connection handle (HSTMT) was the same before and 
after my 30 minute time-out. Could the Postgresql server have timed out 
my connection while my ODBC driver still thinks it is a viable 
connection, and tries to re-use it?
4. What the best way to handle this open connection pooling? My general 
practice has been that very short lived connections are good (say a 
minute or so), but I am not sure if that applies with a thick client 
like Access.

Attachment: greg.campbell.vcf
Description: text/x-vcard (241 bytes)


pgsql-odbc by date

Next:From: Zlatko MaticDate: 2005-07-12 22:35:39
Subject: Re: MS Access and #deleted due to timeouts
Previous:From: Dave PageDate: 2005-07-12 18:55:41
Subject: Re: libpq enabled psqlodbc

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group