ODBC and OIDs 1

[stoolpigeon]

I am writing a VB app that uses PostgreSQL on the back end. This question has more to do w/the odbc than the database.

My database tables are built with the no oid option.

When I use ADO to open a recordset as read only everything is fine. If I send it a query like "SELECT * FROM foo" that is what ODBC sends to the DB.

If I use a Keyset recordset ODBC changes my query and sends the above as "SELECT *, CTOID, OID FROM foo". Then I get an error because there is no OID column on the table. Interestingly enough this only happens if I do a simple select on a single table. If I do a JOIN across 2 or more tables- my query does not get touched.

I would appreciate it if anyone knows of a setting in my DSN that needs to be changed or if there is a way I can work around this.

thanks,
Ron

 

[stoolpigeon]

Well - just in case somebody comes across this down the road- i was able to get things working by changing the locking on the recordset. Here's the code for a recordset that worked properly with the datagrid- and did not query the database for an oid on the table.

rs.Open query, cn, adOpenKeyset, adLockPessimistic, adCmdText

thanks,
Ron

 

[SkyHigh]

Hi Ron
I am a PostgreSQL DBA and I given an assignment to develop an MS Access Client connecting to Postgresql via ODBC, I have a very little experience with Access.

I would need to use ADO to talk to the db, can you please help me out with some code for:

1) connection string for ADO
2) setting PGTZ=GMT in Access
3) etc

The code snippets would be much apprecited.
Brenda

 

[stoolpigeon]

Brenda,

I don't work with Access much myself. I primarily write my clients in VB 6.0. Here is what a little routine looks like that connects to a PostgreSQL database. I am not sure that VBA would be the same or not.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

Set cn = New ADODB.Connection
cn.ConnectionString = "DRIVER={PostgreSQL};DATABASE=taa;SERVER=10.1.1.42;PORT=5432;UID=postgres;PWD=foo"
cn.Open

sql = "select count(*) as ct from clock_type"
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
MsgBox rs!ct

There are many more options that you can set in the connection string than these. A longer connection string might look like this:

DRIVER={PostgreSQL};DATABASE=taa;SERVER=10.1.1.42;PORT=5432;UID=postgres;PWD=foo;
ReadOnly=0;Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=;
Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=1;
Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;
ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;
ByteaAsLongVarBinary=0;UseServerSidePrepare=0

This is done with the Insight ODBC driver available here:

http://gborg.postgresql.org/project/psqlodbc/projdisplay.php

I don't use time zones in my time records. I handle a database that does cover 2 time zones but I do the conversion for the time difference in my code and store all values in relation to where the records belong. I have a little php script that calculates the difference between the 2 places. (I am in Arizona and we do not observe daylight savings but the other office is in Chicago- so the difference shifts twice a year)

Hope this is helpful.

Ron