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

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

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

Indeed, sorry I forgot to mention that. Still in sqlite3 case, you need to wrap it in a transaction in case another thread get a lock on the db between your two queries. François. On Sun, 2013-05-26 at 11:46 +0100, Steven Ayre wrote:

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

_________________________________________________________________________ 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

http://lmgtfy.com/?q=sql+returning+clause http://en.wikipedia.org/wiki/Insert_%28SQL%29#Retrieving_the_key http://www.postgresql.org/docs/9.2/static/sql-insert.html Much less useful unless you have no other choice than to use mysql: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_l... 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/24/2013 08:59 PM, John M 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 <http://www.sparrowmailapp.com/?sig>

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 <mailto: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 <mailto: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 <mailto: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 <mailto: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

William, Thank you for the reply. Question was related to freeswitch dbh driver. Not related to sql specific. Most of the SQL Driver have a method to get the id after the insert record. My questions was does freeswitch dbh have any method to get the id? For Example dbh have a method dbh:affected_rows() for insert , update. Like this do we have a method for ID? Thanks Lloyd * * * <http://www.sunteltech.ca/blog/>* On Sat, May 25, 2013 at 10:23 AM, John M <j_mj@aol.com> wrote:

Yep, super helpful.. /sarcasm

Point us to general sql related pages, none of which are related to freeswitch and the way the language interacts with the db ..

We all know that insert_id exists that's why we are asking. What we don't know is how specifically the freeswitch:dbh handler interacts and can return the inserted record id.

-----Original Message----- From: William King <william.king@quentustech.com> To: freeswitch-users <freeswitch-users@lists.freeswitch.org> Sent: Sat, May 25, 2013 11:24 pm Subject: Re: [Freeswitch-users] dbh:query - insert id

http://lmgtfy.com/?q=sql+returning+clause http://en.wikipedia.org/wiki/Insert_%28SQL%29#Retrieving_the_key http://www.postgresql.org/docs/9.2/static/sql-insert.html

Much less useful unless you have no other choice than to use mysql:http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_l...

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-5518william.king@quentustech.com

On 05/24/2013 08:59 PM, John M 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 <http://www.sparrowmailapp.com/?sig>

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 <mailto:consulting@freeswitch.org <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 <mailto:FreeSWITCH-users@lists.freeswitch.org <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 <mailto:consulting@freeswitch.org <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 <mailto:FreeSWITCH-users@lists.freeswitch.org <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.orghttp://www.freeswitchsolutions.com

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

Official FreeSWITCH Siteshttp://www.freeswitch.orghttp://wiki.freeswitch.orghttp://www.cluecon.com

FreeSWITCH-users mailing listFreeSWITCH-users@lists.freeswitch.orghttp://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-usershttp://www.frees...

_________________________________________________________________________ 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

Hi Nathan, Yes, generally the current connection can make a select query and get the last inserted id and return that. It's possible, as suggested by Daniel earlier that doing a subsequent query for 'select last_insert_id()' may be the best way but that will be assuming the db connection doesn't refresh as that could potentially return the last inserted id of a concurrent channel. For me, I have implemented a solution where I have an extra column where I insert the channel uuid along with the data I insert as well. Then I make a select query to the db collecting the id where the uuid matches the current channel uuid and order by uuid desc. My expectation/hope is that I won't get 2 uuid's generated by freeswitch within a few seconds and should be able to rely on the id coming back as being the last inserted id for the insert query I had just made. Cheers, -Jm -----Original Message----- From: Nathan Neulinger <nneul@mst.edu> To: FreeSWITCH Users Help <freeswitch-users@lists.freeswitch.org> Sent: Sun, May 26, 2013 1:35 am Subject: Re: [Freeswitch-users] dbh:query - insert id Looking at the code, I'm not seeing anything that would do this. Affected rows is directly implemented as part of ODBC, that's why it's easily available. You could probably do a subsequent select on last_insert_id(), but there is a high risk of race condition - some other thread could use the dbh driver in the intervening time. I don't believe that the last insert id is returned automatically on any of the mysql api (underlying calls not FS) calls, and then you have the odbc layer on top of that. The way I've seen some other packages do it is to implement a mysql specific sql execute call that runs the statement, and then immediately runs a select last_insert_id() and returns it. -- Nathan On 05/25/2013 10:01 AM, Lloyd Aloysius wrote:

William,

Thank you for the reply. Question was related to freeswitch dbh driver. Not related to sql specific.

Most of the SQL Driver have a method to get the id after the insert record. My questions was does freeswitch dbh have any method to get the id?

For Example dbh have a method dbh:affected_rows() for insert , update.

Like this do we have a method for ID?

Thanks Lloyd * --

Nathan Neulinger nneul@mst.edu Missouri S&T Information Technology (573) 612-1412 System Administrator - Architect _________________________________________________________________________ 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

I don¹t know if mysql even supports the returning keywork... The way RETURNING works is its an addon to your insert statements INSERT INTO table_foo (col_a, col_b) values (Œfoo¹, Œbar¹) RETURNING row_id; Where row_id is an auto incrementing field such as a SERIAL (sequence) in PGSQL or an auto-increment field in mySQL. While this is an ³insert² per say, it looks more like a ³select² when executing as data is returned On 5/24/13 10:59 PM, "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 ?

-- Ken http://www.FreeSWITCH.org http://www.ClueCon.com http://www.OSTAG.org irc.freenode.net #freeswitch

I'd like to offer an apology to you Seven and to the list in general for an inappropriate comment I made about your offer of free assistance regarding the dbh insert id. My comment was rude and uncalled for and I will behave more appropriately in the future. The mailing list is an invaluable source of help and any offers of assistance large or small should be accepted graciously. I hope you will accept this apology. Humbly, sincerely, John -----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