OLE DB Driver Help

Question

I'm using Delphi 2006 and would like some help with fixing a bug in an OLE DB driver implementation.

The implementation is built on top of the OLEDB Toolkit from Binh Ly.

The problem I'm having is that after executing an Insert command inside the TOleDbCommand.ExecuteCommand method I need to update the row data with a record Id (this is the physical location of the record on disk).

The rowset contains a column called RecID which is required for locating a record when executing delete and update commands. For new records this will initially be 0 so after I've written the new data to disk I need to assign physical location (offset) number to the RecID column of the current rowset for the current row.

I'm not sure if that makes sense.

So I guess my question is, from the TOleDBCommand object how do I access the current row data (the row data I've just written to disk) for the rowset and how to I update the column RecID of the current row with the required value?

Answer 1

If I understand correctly you want to return the ID key (typically the primary key value) after a table insert. I believe you need to implement this yourself. We had to do the same thing. Our application works against MySql, Oracle, and Sql Server. Below are the sql statements to retrieve the record id of the last inserted record. So after you do your record insert, follow it with a query to fetch the value of the record id (on same database connection).

MySql

SELECT LAST_INSERT_ID()

Oracle

SELECT NEXTPK_TABLENAME.CURRVAL FROM DUAL

Note: Replace TABLENAME with the name of your table.

Sql Server

SELECT LAST_INSERT_ID()

Note: Replace TABLENAME with the name of your table for Oracle.