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

[Johan Wilfer]

On 2011-07-20 19:17, Tilghman Lesher wrote:
> On Monday 18 July 2011 04:27:49 Johan Wilfer wrote:
>> 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 do not.  Keep in mind that a database keeping an autogenerated row ID is
> pretty specific to MySQL (although as you noted, PostgreSQL does it, too,
> now).  ODBC is an older standard that tends to match with capabilities in
> MS SQL Server, Sybase, Oracle, and DB2.  Therefore, I'd be surprised to
> learn that ODBC has such a concept.
>
> At any rate, you'd still need transaction support, because even if ODBC
> provided such an API, Asterisk does not provide a guarantee that you're
> querying the same handle unless you employ transaction support.
>
> Add to that that 1.6.2 is in security-fix mode, 1.8 is in release mode, and
> 1.10 is branched, so the earliest that such a feature might appear in a
> release would be 1.12.  That's 18 months or more to wait for a feature you
> need now.
Actually my need for this is not immediate. I've worked around this
before, and I do not need to change it right away (that doesn't mean I
really like my current solution). What I wanted to do is get some
feedback on a more clean approach and maybe as you say help myself and
others in the future...

-- 
Med vänlig hälsning

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