[asterisk-dev] func_odbc: Get the primary key for inserted row?

[Tilghman Lesher]

On Sunday 17 July 2011 14:43:20 Johan Wilfer wrote:
> I'm in the process of migrating some asterisk 1.4 systems to 1.6.2 and
> after that to 1.8, and I must say I'm very impressed with func_odbc. It
> worked well with 1.4 but now the multi-row queries for example making it
> an even sharper tool.
> 
> One thing I havn't been able to do in a "clean" way however is to grab
> the primary key of the row that has just been inserted. It's possible to
> insert something unique and then select for it - that's the workaround I
> use right now.
> 
> To take php as an example here there is the function mysql_insert_id (1)
> (2) that returns this information. I did go through the files in
> funcs/func_odbc.c and res/res_odbc.c in asterisk trunk to see if there
> was a reference to get the primary key or the id but I couldn't find it.
> Google told me there was something called "cursors" in ODBC related to
> this, but maybe I got that wrong (3), no reference to this in the source
> either.
> 
> Have I missed anything obvious? Is this hard to implement?

You can, but you'll need the transaction support in 1.6.2, because without
that support, there's no guarantee that you'd be using the same connection,
and insert IDs are maintained on a per-connection basis.  To obtain a
transaction ID, you use the ODBC(transaction) function to request a
particular handle (and name the transaction), which will remain associated
with your channel until you a) commit the transaction, b) rollback the
transaction, or c) your channel is destroyed after hangup.  This third item
is purely about housekeeping.

Then you do your insert statement, and call SELECT LAST_INSERT_ID() on the
same handle (implicitly the same handle as long as the readhandle on the
SELECT and the writehandle on the INSERT reference the same entry in
res_odbc.conf), and you'll have your row ID.  Remember to ODBC_Commit() the
transaction name to avoid a rollback (not really possible with MyISAM
tables, but it never hurts to be sure).

-- 
Tilghman