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.

So I compared the 8.02.04 driver and driver 8.02.05 sources and positioned the traces of the driver to find the bug: it is in the SC_setInsertedTable function, when parsing the name of the table to find the schema.

Explanation :

With the driver 8.02.04, in traces, we see this:

[11.819]conn=02354D10, query='INSERT INTO "iamaudit"."public"."AuditEvents" ("EvtCategory","EvtType","EvtOperation","TimeStamp","ResultCode","UserAuditID","SessionID","AccessPointID","AccessPointName","ClientIP","ClientHostname","ExtendedInfo") VALUES (1,1234,3,'2013-09-23 12:30:52'::timestamp,-2147467259,'champi','','','DEROBEE','','','')'

[11.841]conn=02354D10, query='SELECT lastval()'

[11.856]    [ fetched 1 rows ]

[11.892]conn=02354D10, query='COMMIT'

With the driver 8.02.05, in traces, we see this:

[17.297]conn=02354D10, query='INSERT INTO "iamaudit"."public"."AuditEvents" ("EvtCategory","EvtType","EvtOperation","TimeStamp","ResultCode","UserAuditID","SessionID","AccessPointID","AccessPointName","ClientIP","ClientHostname","ExtendedInfo") VALUES (1,1234,3,'2013-09-23 12:41:27'::timestamp,-2147467259,'champi','','','DEROBEE','','','')'

[17.336]conn=02354D10, query='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, d.adsrc 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'

[17.351]    [ fetched 0 rows ]

[17.401]conn=02354D10, query='SELECT NULL'

[17.405]    [ fetched 1 rows ]

[17.439]conn=02354D10, query='COMMIT'

The problem is revealed by the correction in 8.02.0402:

3.) Put back the @@IDENTITY implementation so as not to use lastval().

During this correction, changed the use of lastval() that is dangerous by the use of curval (sequence) much more secure. Then, why this correction does not work in my case? In the trace, seen just after the insert, there is a select that returns no rows! This select is directed to search the sequences in the table. It does not work because it passes 'public' as table name  and 'iamaudit' as schema name instead of 'AuditEvents' and 'public'. Because in the insert, I use the string "iamaudit"."public"."AuditEvents"to identify the table. The table name is prefixed by the name of the base and the schema, which was not expected. Note that it is the OLE DB driver which writes the query and that uses this syntax.

The analysis of the insert query is performed in the function SC_setInsertedTable (file execute.c). So I modified this function to update the parsing  to my case and I tested successfully this correction.

Here in attachment some traces, a test case and the correction I made. Beware, it is a correction "quick & dirty" just to check that the bug was in this function. And I have not tested it in all possible cases.

I can provide a testcase as needed.

Best Regards,

Philippe CHAMPIGNON