If you switch from luasql to freeswitch.Dbh then the freeswitch.Dbh object will give a single connection until you either call the release method or the variable goes out of scope. That means queries will happen on the same transaction.

-Steve


On 25 May 2013 19:30, William King <william.king@quentustech.com> wrote:
Unfortunately you may hit a problem with last_insert_id() because FS
uses connection pooling, and statement batches(at least for some select
statements). Someone could check the lua wrapper code to see how the dbh
handler code is implemented.

William King
Senior Engineer
Quentus Technologies, INC
1037 NE 65th St Suite 273
Seattle, WA 98115
Main:   (877) 211-9337
Office: (206) 388-4772
Cell:   (253) 686-5518
william.king@quentustech.com

On 05/25/2013 10:11 AM, François wrote:
> Thanks to Steven and William for mentioning the RETURNING clause. I
> checked it out and learned something cool about PostgreSQL, so to me
> there's no problem with one liners like that.
>
> About MySQL's last_insert_id(), it is fine to use because it returns the
> last id for that particular connection/dbh handler, so you shouldn't
> need to worry about race as long as you use it with the same handler
> right after your insert.
>
> For other DB where race could be an issue (ex: sqlite3), you could
> always wrap it in a transaction to be sure:
>
> begin transaction;
> insert <stuff>;
> select last_insert_id();
> end transaction;
>
> François.
>
> On Sat, 2013-05-25 at 04:01 -0400, John M wrote:
>> Hi Daniel,
>>
>> Thanks for your description, it is much appreciated. :-)
>>
>> 5 word one liners from people too lazy to explain properly would
>> really be best if they didn't reply at all.
>>
>> Cheers, thanks again.
>>
>> -Jm
>>
>>
>>
>>
>>
>>
>> -----Original Message-----
>> From: Daniel Ivanov <sertys@gmail.com>
>> To: FreeSWITCH Users Help <freeswitch-users@lists.freeswitch.org>
>> Sent: Sat, May 25, 2013 5:57 pm
>> Subject: Re: [Freeswitch-users] dbh:query - insert id
>>
>> It is true that the luasql driver is overly basic and poorly
>> documented . Unfortunately mysql doesn't support RETURNING clause like
>> pgsql and oracle. You should however try SELECT LAST_INSERT_ID();
>> right after the insert query. I cannot guarantee it works due to the
>> unknown nature(to me that is) of the luasql transaction handling, but
>> it should keep a transaction open for as long as a db handler lives.
>>
>> On May 25, 2013 7:03 AM, "John M" <j_mj@aol.com> wrote:
>>         Hi Seven Du,
>>
>>         I'd really like to know if this is possible too, couldn't find
>>         it documented anywhere.
>>
>>         Instead of being cryptic, if you know the answer won't you
>>         please help by explaining what the RETURNING clause is and how
>>         to use it?
>>
>>         Does it somehow return mysql_insert_id()?
>>
>>         How should we use it.
>>
>>         You help is invaluable and is contributing to the freeswitch
>>         community.
>>
>>         -Jm
>>
>>
>>
>>
>>
>>         -----Original Message-----
>>         From: Seven Du <dujinfang@gmail.com>
>>         To: FreeSWITCH Users Help
>>         <freeswitch-users@lists.freeswitch.org>
>>         Sent: Sat, May 25, 2013 12:52 pm
>>         Subject: Re: [Freeswitch-users] dbh:query - insert id
>>
>>         Maybe try the RETURNING clause ?
>>
>>
>>         --
>>         Seven Du
>>         http://www.freeswitch.org.cn
>>         http://about.me/dujinfang
>>         http://www.dujinfang.com
>>
>>
>>         Sent with Sparrow
>>
>>
>>         On Saturday, May 25, 2013 at 8:14 AM, Lloyd Aloysius wrote:
>>         > Hello All
>>         >
>>         >
>>         > How to get the id value after insert a record a record using
>>         > dbh:query
>>         >
>>         >
>>         > table_a - columns.
>>         >
>>         >
>>         > id - auto increment
>>         > field1
>>         > field2
>>         >
>>         >
>>         >
>>         >
>>         > dbh:query("insert into table_a ( field1,field2) values
>>         > ('11','Test')")
>>         >
>>         >
>>         >
>>         >
>>         > After insert how to get the table_a - id value for the
>>         > inserted record?
>>         >
>>         >
>>         > Thanks
>>         > Lloyd
>>         > _________________________________________________________________________
>>         > Professional FreeSWITCH Consulting Services:
>>         > consulting@freeswitch.org
>>         > http://www.freeswitchsolutions.com
>>         >
>>         >
>>         > FreeSWITCH-powered IP PBX: The CudaTel Communication Server
>>         > http://www.cudatel.com
>>         >
>>         >
>>         > Official FreeSWITCH Sites
>>         > http://www.freeswitch.org
>>         > http://wiki.freeswitch.org
>>         > http://www.cluecon.com
>>         >
>>         >
>>         > FreeSWITCH-users mailing list
>>         > FreeSWITCH-users@lists.freeswitch.org
>>         > http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>         > UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>>         > http://www.freeswitch.org
>>
>>
>>         _________________________________________________________________________
>>         Professional FreeSWITCH Consulting Services:
>>         consulting@freeswitch.org
>>         http://www.freeswitchsolutions.com
>>
>>         FreeSWITCH-powered IP PBX: The CudaTel Communication Server
>>         http://www.cudatel.com
>>
>>         Official FreeSWITCH Sites
>>         http://www.freeswitch.org
>>         http://wiki.freeswitch.org
>>         http://www.cluecon.com
>>
>>         FreeSWITCH-users mailing list
>>         FreeSWITCH-users@lists.freeswitch.org
>>         http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>         UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>>         http://www.freeswitch.org
>>
>>         _________________________________________________________________________
>>         Professional FreeSWITCH Consulting Services:
>>         consulting@freeswitch.org
>>         http://www.freeswitchsolutions.com
>>
>>         FreeSWITCH-powered IP PBX: The CudaTel Communication Server
>>         http://www.cudatel.com
>>
>>         Official FreeSWITCH Sites
>>         http://www.freeswitch.org
>>         http://wiki.freeswitch.org
>>         http://www.cluecon.com
>>
>>         FreeSWITCH-users mailing list
>>         FreeSWITCH-users@lists.freeswitch.org
>>         http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>         UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>>         http://www.freeswitch.org
>>
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting@freeswitch.org
>> http://www.freeswitchsolutions.com
>>
>> FreeSWITCH-powered IP PBX: The CudaTel Communication Server
>> http://www.cudatel.com
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>>
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users@lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting@freeswitch.org
>> http://www.freeswitchsolutions.com
>>
>> FreeSWITCH-powered IP PBX: The CudaTel Communication Server
>> http://www.cudatel.com
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>>
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users@lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>
>
>
> _________________________________________________________________________
> Professional FreeSWITCH Consulting Services:
> consulting@freeswitch.org
> http://www.freeswitchsolutions.com
>
> FreeSWITCH-powered IP PBX: The CudaTel Communication Server
> http://www.cudatel.com
>
> Official FreeSWITCH Sites
> http://www.freeswitch.org
> http://wiki.freeswitch.org
> http://www.cluecon.com
>
> FreeSWITCH-users mailing list
> FreeSWITCH-users@lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org
>

_________________________________________________________________________
Professional FreeSWITCH Consulting Services:
consulting@freeswitch.org
http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server
http://www.cudatel.com

Official FreeSWITCH Sites
http://www.freeswitch.org
http://wiki.freeswitch.org
http://www.cluecon.com

FreeSWITCH-users mailing list
FreeSWITCH-users@lists.freeswitch.org
http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
http://www.freeswitch.org