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

[Johan Wilfer]

On 2011-07-18 00:03, Tilghman Lesher wrote:
> 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).

Thank you Tilghman! This SELECT LAST_INSERT_ID() is mysql specific but
it seems like there is a postgresql variant also (1). I use mysql
(innodb) at the moment, but I like to keep a migration path and document
any non-standard sql I use...

However, do you know of any ODBC commands that give you the row id after
a INSERT or an UPDATE have taken place? Because if there is we can
always run this command after an INSERT/UPDATE operation and expose the
information as a channel variable.

I'm guessing here but I imagine this operation should be cheap as this
information are tied to the current connection. This would also
eliminate the need to use transactions just to use the right connection.
If this functionality is a part of the ODBC protocol the need for
different syntax for different database-engines are also eliminated.

/Johan

(1) http://archives.postgresql.org/pgsql-general/2008-08/msg01058.php ,
http://archives.postgresql.org/pgsql-general/2008-08/msg01061.php

-- 
Johan Wilfer                 email: johan at jttech.se
JT Tech | Utvecklare         webb: http://jttech.se
direkt: +46 31 380 91 01  support: +46 31 380 91 00