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

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 (view raw or flat)
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

pgsql-odbc by date

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

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