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: 42D44150.5030407@us.michelin.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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).

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

Troubleshooting:
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
returned).

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 Content-Type Size
greg.campbell.vcf text/x-vcard 241 bytes

Responses

Browse pgsql-odbc by date

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