| Dmitry Samokhin 2006-10-25, 8:17 am |
| As far as I understand, since the driver started to expose '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.
|