Re: [asterisk-users] Return variables from func_odbc calls?
On Tuesday 01 February 2011 11:49:51 Paul Belanger wrote: On 11-01-26 02:59 PM, Tilghman Lesher wrote: On Wednesday 26 January 2011 07:01:12 Paul Belanger wrote: [CREATECALL] dsn=Example writesql=INSERT INTO x (y) VALUES (z) readsql=SELECT LAST_INSERT_ID(); That assumes you have only one call in existence at a time. If two calls came in and executed the query at about the same time, it's possible for both reads to return the same value. Yup, didn't even think of that. My testing of ODBC was a single channel. Guess I need another method to return the last ID of the record that was just inserted. Assuming you were using a MySQL backend that supported transactions, you could use the transaction layer in Asterisk 1.6.2 and greater to ensure that each channel got a serialized view. That would make this approach work. -- Tilghman -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Return variables from func_odbc calls?
Paul Belanger wrote: On 11-01-26 02:59 PM, Tilghman Lesher wrote: On Wednesday 26 January 2011 07:01:12 Paul Belanger wrote: [CREATECALL] dsn=Example writesql=INSERT INTO x (y) VALUES (z) readsql=SELECT LAST_INSERT_ID(); That assumes you have only one call in existence at a time. If two calls came in and executed the query at about the same time, it's possible for both reads to return the same value. Yup, didn't even think of that. My testing of ODBC was a single channel. Guess I need another method to return the last ID of the record that was just inserted. In this case, does the Asterisk connection to MySQL through odbc counts as a unique 'client', or does each call to a function will count as a 'client'? I ask because of this: For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0). Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid. Each client will receive the last inserted ID for the last statement that client executed. at: http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html -- Jose P. Espinal http://www.eSlackware.com IRC: Khratos @ #asterisk / -doc / -bugs -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Return variables from func_odbc calls?
On Tuesday 01 February 2011 12:36:46 Jose P. Espinal wrote: Paul Belanger wrote: On 11-01-26 02:59 PM, Tilghman Lesher wrote: On Wednesday 26 January 2011 07:01:12 Paul Belanger wrote: [CREATECALL] dsn=Example writesql=INSERT INTO x (y) VALUES (z) readsql=SELECT LAST_INSERT_ID(); That assumes you have only one call in existence at a time. If two calls came in and executed the query at about the same time, it's possible for both reads to return the same value. Yup, didn't even think of that. My testing of ODBC was a single channel. Guess I need another method to return the last ID of the record that was just inserted. In this case, does the Asterisk connection to MySQL through odbc counts as a unique 'client', or does each call to a function will count as a 'client'? The first. But you need to also understand that unless you use transactions, and specifically the transaction support in Asterisk, each channel is not guaranteed to be using the same connection on the second query. Or even if they all use the same connection, the queries are not serialized in the way that you might otherwise expect. The transaction support introduced in Asterisk 1.6.2 allows a connection to be reserved exclusively to a single channel, thus ensuring that the second query on a channel really was the very next query on the connection. -- Tilghman -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Return variables from func_odbc calls?
On 11-02-01 01:21 PM, Tilghman Lesher wrote: Assuming you were using a MySQL backend that supported transactions, you could use the transaction layer in Asterisk 1.6.2 and greater to ensure that each channel got a serialized view. That would make this approach work. Ya, I think I'm going to use this approach for the test. I was able to find some limited information on the wiki, let me see if I can get it working. -- Paul Belanger Digium, Inc. | Software Developer twitter: pabelanger | IRC: pabelanger (Freenode) Check us out at: http://digium.com http://asterisk.org -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Return variables from func_odbc calls?
On Wednesday 26 January 2011 03:02:19 Sherwood McGowan wrote: This is primarily aimed at Sir Lesher, whose name graces the source code for func_odbc that I'm currently trying to read to answer this question. Tilghman (or anyone else who has determined the answer to this query), I have googled, searched wikis, and I'm currently perusing the source code, but the long and short of it is that I cannot seem to find any reference to variables set by func_odbc calls such as something that would indicate if a query worked so that I can (in the dialplan) handle errors on the fly. Another item I'm trying to determine is the LAST_INSERT_ID... Thoughts/Comments? I hope very much that I haven't overlooked something, but then again I'm no longer a spring chicken either Well, it depends upon what type of query you're performing. If it is a query which inserts/updates, then ODBC_ROWS will contain an integer specifying the number of rows affected. -1 is reserved for a statement which failed, since it is perfectly possible for an UPDATE to succeed, yet affect 0 rows. For SELECT queries, however, that is a much more difficult question, since it depends upon the particular query. Again, it is perfectly possible for a SELECT query to successfully run, yet return 0 rows. Or it might be that with your dataset, you should never get 0 rows returned. These are questions that must be pondered by the particular data administrator, not answers that I can provide as the author of the tool. As far as LAST_INSERT_ID, that is a MySQL-ism that is not supported, since it is not portable across database types. -- Tilghman -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Return variables from func_odbc calls?
On Wed, Jan 26, 2011 at 3:56 AM, Tilghman Lesher tilgh...@meg.abyt.es wrote: Well, it depends upon what type of query you're performing. If it is a query which inserts/updates, then ODBC_ROWS will contain an integer specifying the number of rows affected. -1 is reserved for a statement which failed, since it is perfectly possible for an UPDATE to succeed, yet affect 0 rows. For SELECT queries, however, that is a much more difficult question, since it depends upon the particular query. Again, it is perfectly possible for a SELECT query to successfully run, yet return 0 rows. Or it might be that with your dataset, you should never get 0 rows returned. These are questions that must be pondered by the particular data administrator, not answers that I can provide as the author of the tool. As far as LAST_INSERT_ID, that is a MySQL-ism that is not supported, since it is not portable across database types. -- Tilghman Thanks mate, that's what I was looking for :D Forgot about LAST_INSERT_ID being a MySQL-ism, but that's no big deal, I'll get by without it ;-) The ODBC_ROWS variable was what I was looking for, thanks! -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Return variables from func_odbc calls?
On Wed, Jan 26, 2011 at 5:17 AM, Sherwood McGowan
sherwood.mcgo...@gmail.com wrote:
On Wed, Jan 26, 2011 at 3:56 AM, Tilghman Lesher tilgh...@meg.abyt.es wrote:
Well, it depends upon what type of query you're performing. If it is
a query which inserts/updates, then ODBC_ROWS will contain an
integer specifying the number of rows affected. -1 is reserved for
a statement which failed, since it is perfectly possible for an UPDATE
to succeed, yet affect 0 rows. For SELECT queries, however, that is a
much more difficult question, since it depends upon the particular query.
Again, it is perfectly possible for a SELECT query to successfully run, yet
return 0 rows. Or it might be that with your dataset, you should never get
0 rows returned. These are questions that must be pondered by the
particular data administrator, not answers that I can provide as the author
of the tool.
As far as LAST_INSERT_ID, that is a MySQL-ism that is not supported,
since it is not portable across database types.
--
Tilghman
Thanks mate, that's what I was looking for :D Forgot about
LAST_INSERT_ID being a MySQL-ism, but that's no big deal, I'll get by
without it ;-) The ODBC_ROWS variable was what I was looking for,
thanks!
Would ${ODBCSTATUS} properly return SUCCESS or FAILED/FAILURE per
insert or update query status?
--
_
-- Bandwidth and Colocation Provided by http://www.api-digital.com --
New to Asterisk? Join us for a live introductory webinar every Thurs:
http://www.asterisk.org/hello
asterisk-users mailing list
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Return variables from func_odbc calls?
On 11-01-26 04:56 AM, Tilghman Lesher wrote: As far as LAST_INSERT_ID, that is a MySQL-ism that is not supported, since it is not portable across database types. While, LAST_INSERTID(); is a MySQL-ism, I've been able to use it with func_ODBC. Of cource, my database is MySQL and this function would not work on anything else. [CREATECALL] dsn=Example writesql=INSERT INTO x (y) VALUES (z) readsql=SELECT LAST_INSERT_ID(); -- Paul Belanger Digium, Inc. | Software Developer twitter: pabelanger | IRC: pabelanger (Freenode) Check us out at: http://digium.com http://asterisk.org -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Return variables from func_odbc calls?
On Wed, Jan 26, 2011 at 7:01 AM, Paul Belanger pabelan...@digium.com wrote: On 11-01-26 04:56 AM, Tilghman Lesher wrote: As far as LAST_INSERT_ID, that is a MySQL-ism that is not supported, since it is not portable across database types. While, LAST_INSERTID(); is a MySQL-ism, I've been able to use it with func_ODBC. Of cource, my database is MySQL and this function would not work on anything else. [CREATECALL] dsn=Example writesql=INSERT INTO x (y) VALUES (z) readsql=SELECT LAST_INSERT_ID(); -- Paul Belanger Digium, Inc. | Software Developer twitter: pabelanger | IRC: pabelanger (Freenode) Check us out at: http://digium.com http://asterisk.org Hey, thanks for the tip Paul! -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Return variables from func_odbc calls?
On 11-01-26 08:19 AM, Sherwood McGowan wrote: While, LAST_INSERTID(); is a MySQL-ism, I've been able to use it with func_ODBC. Of cource, my database is MySQL and this function would not work on anything else. [CREATECALL] dsn=Example writesql=INSERT INTO x (y) VALUES (z) readsql=SELECT LAST_INSERT_ID(); Hey, thanks for the tip Paul! I should also note, make sure you create a 2nd DSN for your specific ODBC commands that will use LAST_INSERT_ID(), otherwise if you are using ODBC CDR or CEL, there is a chance LAST_INSERT_ID() will return the ID of those records. -- Paul Belanger Digium, Inc. | Software Developer twitter: pabelanger | IRC: pabelanger (Freenode) Check us out at: http://digium.com http://asterisk.org -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Return variables from func_odbc calls?
I actually was pondering that same thing :D On Wed, Jan 26, 2011 at 7:33 AM, Paul Belanger pabelan...@digium.com wrote: On 11-01-26 08:19 AM, Sherwood McGowan wrote: While, LAST_INSERTID(); is a MySQL-ism, I've been able to use it with func_ODBC. Of cource, my database is MySQL and this function would not work on anything else. [CREATECALL] dsn=Example writesql=INSERT INTO x (y) VALUES (z) readsql=SELECT LAST_INSERT_ID(); Hey, thanks for the tip Paul! I should also note, make sure you create a 2nd DSN for your specific ODBC commands that will use LAST_INSERT_ID(), otherwise if you are using ODBC CDR or CEL, there is a chance LAST_INSERT_ID() will return the ID of those records. -- Paul Belanger Digium, Inc. | Software Developer twitter: pabelanger | IRC: pabelanger (Freenode) Check us out at: http://digium.com http://asterisk.org -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] Return variables from func_odbc calls?
On Wednesday 26 January 2011 07:01:12 Paul Belanger wrote: On 11-01-26 04:56 AM, Tilghman Lesher wrote: As far as LAST_INSERT_ID, that is a MySQL-ism that is not supported, since it is not portable across database types. While, LAST_INSERTID(); is a MySQL-ism, I've been able to use it with func_ODBC. Of cource, my database is MySQL and this function would not work on anything else. [CREATECALL] dsn=Example writesql=INSERT INTO x (y) VALUES (z) readsql=SELECT LAST_INSERT_ID(); That assumes you have only one call in existence at a time. If two calls came in and executed the query at about the same time, it's possible for both reads to return the same value. -- Tilghman -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
