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

[Tilghman Lesher]

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.

-- 
Tilghman