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.