Cannot retrieve sequence number after an insert with OLE DB - Mailing list pgsql-odbc

Hello,

I just submit a bug in the psqlODBC driver 9.2/9.3 (32-bit and 64-bit).

The context :

I have a Windows program that uses the database postgresql through OLE DB and psqlODBC driver. In the database, I have a table with a sequence that generates a number whenever you insert a new row in the table. The program inserts data into the table and retrieves the auto incremented sequence value.

With Postgresql 8.2 and the psqlODBC 8.02.0400 driver, the program works perfectly. But with Postgresql 9.2 or 9.3 and the psqlODBC 9.02.0100 or 9.03.0300 driver it is impossible to retrieve the sequence number after the insertion of the line. There are always 0.

Tests:

To identify the problem, I did several tests, which allowed me to find that it was the ODBC driver that is problematic.

With version 8.02.04 of the driver, the problem does not occur. But with the 8.02.05 driver version, I reproduce the problem.

Trace:

I see  in the trace (ODBC driver  9.03.0300), the insert statement (line 1130):

INSERT INTO "iamaudit"."public"."AuditEvents" ("EvtCategory","EvtType","EvtOperation","TimeStamp","ResultCode","UserAuditID","SessionID","AccessPointID","AccessPointName","ClientIP","ClientHostname","ExtendedInfo") VALUES (?,?,?,?,?,?,?,?,?,?,?,?)

And after, for retrieve the value of the sequence (line 1311):

SELECT @@IDENTITY

Then (line 1349):

select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then t.typbasetype else 0 end, t.typtypmod, c.relhasoids from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname = 'public' and n.nspname = 'iamaudit') inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum

But, if I execute this latest request, I found no row !

When I modify some values, I found the correct result:

select n.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, c.relhasrules, c.relkind, c.oid, pg_get_expr(d.adbin, d.adrelid), case t.typtype when 'd' then t.typbasetype else 0 end, t.typtypmod, c.relhasoids from (((pg_catalog.pg_class c inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and c.relname = 'AuditEvents' and n.nspname = 'public') inner join pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum

Best Regards,

Philippe CHAMPIGNON