How to get ID of inserted row, like Informix's sqlca.sqlerrd[1]?

[Curtiss Murphy]

Heya Everyone,

Anyone know of a way to get the ID or Primary Key of a row that
was just inserted via SQLPrepare and then SQLExecute? In
Informix ESQL/C you could access the sqlca.sqlerrd[1] field to
get the long primary key value of the inserted row (if applicable).

Is there a way to do this in ODBC 3.0?

Thanks,
Curtiss.

*************************************************************************
So powerful is the light of unity that it can illuminate the whole earth.

Curtiss Murphy -- Senior Software Engineer -- Appropriate Technologies
cmu...@NOSPAMapp-tech.com -- www.app-tech.com/staff/cmurphy
*************************************************************************

[Dietrich Neuman]

I just rerun the query for the row I just added -- in other words, call
SQLExecute for a SELECT statement that selects on the non-Primary Key
columns for the newly added row's column values. Seems like there should
be a cleaner way to do it than that, but I don't know of any. . . .

Dietrich Neuman

Curtiss Murphy <cmu...@NOSPAMapp-tech.com> wrote in article
<01bd5e7e$e8f80320$9310...@ati147.app-tech.com>...

[Jonathan Eric Miller]

I was wondering the same thing. For the application that I am currently
working on, all the tables have a column containing the id of the user that
last modified the row and a timestamp indicating when the row was last
modified. I'm thinking that I'll probably do what was suggested below;
requery filtering on user id and timestamp columns. It seems like there
should be an easier way though. If there is and I just don't know about it,
please let me know.

Thanks, Jon

--
To reply to me, please remove the _ from my email address.

Dietrich Neuman wrote in message
<01bd5f46$e2ce9220$1e2da8c0@dietrichn_pc2>...

[Per Olsson]

I call GetCurrentThread to get a unique ID of the running thread. I then
execute an SQL INSERT statement putting the thread ID in one of the
fields. Next I execute a SELECT MAX(ID) FROM Table WHERE
ThreadField=CurrentThread. Finally I execute an UPDATE Table ... WHERE
ID=the_read_ID.

The ThreadID is needed in the DB table only temporary, so you can use
almost any field except the ID field to store the ThreadID and in the
UPDATE statement overwrite it with the correct value of the field.

I dont know if this is a good way, so any comments are welcome.

/Per Olsson

P.S. If the ODBC driver supports it, it is possible to use
SQLBulkOperations to add record(s) and get bookmark(s) to the added
record(s) in return. You cant use an SQL INSERT statement to add the
record(s) with this method. See the ODBC API Help file for
SQLBulkOperations.

----

--
If everything else fails, read the instructions.

[Michael Bowman]

Guess one could always interogate the target table- looking for the primary
key fields, then do the insert, then retrieve the record based on the keys
that you just inserted... Of course, if we are talking multi tiers and
stored procedures and packages in ORACLE, then its a different game... This
"pass back" would happen on the server via the stored procedure or package
using the Oracle data control.

Hope this helps
Michael Bowman
Client/Server Y2K team COMSYS & VERTICAL WORKS

Per Olsson wrote in message <3530FCD6...@algonet.se>...

[chri...@earthlink.net]

> > >> Anyone know of a way to get the ID or Primary Key of a row that
> > >> was just inserted via SQLPrepare and then SQLExecute? In
> > >> Informix ESQL/C you could access the sqlca.sqlerrd[1] field to
> > >> get the long primary key value of the inserted row (if applicable).

I just follow the insert with a query based on some fields in the record I
just inserted. Since there ought to be some combiantion of columns not
including the primary key that make the record unique, the query should
return exactly one row.

CF

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading