Serial columns and ADO

From: Dmitry Samokhin <sdld(at)mail(dot)ru>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Serial columns and ADO
Date: 2006-11-09 10:32:27
Message-ID: E1Gi7Ct-0004cO-00.sdld-mail-ru@f76.mail.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

As far as I understand, since the driver exposes 'serial' columns
as 'AutoNumber', ADO issues 'SELECT @@IDENTITY' to retreive the new
generated values just after inserts. The driver, in turn, has to modify that
query into 'SELECT currval(...)'.

When working with PG tables from inside MS Access, it all works perfectly!
But here is a small test suite that doesn't work as expected:

----------------------------------------------------------------------------
Server side:

CREATE TABLE t1
(
col1 serial NOT NULL,
col2 varchar(50)
)
WITHOUT OIDS;

Client side (Visual Basic 6.0 or VBA):

Sub Test()
Dim strSql As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Open "Provider=MSDASQL.1;Data Source=Test_pg;"

strSql = "SELECT * FROM t1"

rs.ActiveConnection = cn
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Source = strSql

rs.Open

rs.AddNew
rs("col2") = "Sample string"
rs.Update
MsgBox rs("col1")

rs.Close
End Sub
----------------------------------------------------------------------------

Try this and you will see '0' in the messagebox shown and the entries in
psqlodbc_xxxx.log similar to the following:

.................
conn=04973D90, query='INSERT INTO "public"."t1" ("col2") VALUES (E'Sample
string')'
conn=04973D90, query='SELECT 0'
.................

My environment:
PostgreSQL 8.1.0 backend
ODBC driver 08.02.0200 Unicode (the latest at the moment of writing) on
WinXP SP2

Any ideas?
Dmitry.

Responses

Browse pgsql-odbc by date

  From Date Subject
Next Message Hiroshi Inoue 2006-11-10 00:41:42 Re: Serial columns and ADO
Previous Message Peter Eisentraut 2006-11-09 09:26:58 Re: Help building 08.02.0200 on Solaris 10 (sparc)