Gentlemen,
I have googled, searched the mailing list archives, and even spoke on
the IRC channel, but have not found an answer to the following
problem. I am attempting to retrieve multiple columns in an ODBC query
using ARRAY per the solutions offered by many individuals. My dialplan
code is as follows:
exten => _.,n,Set(ARRAY(var1,var2,var3)=${ODBC_LOOKUP(${KEYVAL})})
exten => _.,n,Verbose(2,var1 = ${var1})
exten => _.,n,Verbose(2,var2 = ${var2})
exten => _.,n,Verbose(2,var3 = ${var3})
Here's the func_odbc.conf code:
[LOOKUP]
dsn=mysql-asterisk
readsql=SELECT col1, col2, col3 from table1 WHERE keycol = '${ARG1}'
and here's the full log:
[Jan 22 20:12:50] VERBOSE[32348] pbx.c: -- Executing
[123 at dolookup:8] Set("SIP/sip1-inbound-00000f99",
"ARRAY(var1,var2,var3)=96829,-3,Name Unavailabl") in new stack
[Jan 22 20:12:50] DEBUG[32348] func_strings.c: array (var1,var2,var3=96829)
[Jan 22 20:12:50] DEBUG[32348] func_strings.c: array set value (var1=96829)
[Jan 22 20:12:50] DEBUG[32348] func_strings.c: array set value (var2=(null))
[Jan 22 20:12:50] DEBUG[32348] func_strings.c: array set value (var3=(null))
[Jan 22 20:12:50] WARNING[32348] pbx.c: MSet: ignoring entry '-3' with
no '=' (in +17322761300 at getcnam:8
Asterisk version: 1.6.2.14
MySQL: 5.1.37
Thoughts?
> -----Original Message----- > From: asterisk-users-bounces at lists.digium.com [mailto:asterisk-users- > bounces at lists.digium.com] On Behalf Of Sherwood McGowan > Sent: Saturday, January 22, 2011 8:46 PM > To: Asterisk Users Mailing List - Non-Commercial Discussion > Subject: [asterisk-users] FUNC_ODBC and ARRAY > > Gentlemen, > > I have googled, searched the mailing list archives, and even spoke on > the IRC channel, but have not found an answer to the following > problem. I am attempting to retrieve multiple columns in an ODBC query > using ARRAY per the solutions offered by many individuals. My dialplan > code is as follows: > > exten => _.,n,Set(ARRAY(var1,var2,var3)=${ODBC_LOOKUP(${KEYVAL})}) > exten => _.,n,Verbose(2,var1 = ${var1}) > exten => _.,n,Verbose(2,var2 = ${var2}) > exten => _.,n,Verbose(2,var3 = ${var3}) > > Here's the func_odbc.conf code: > > [LOOKUP] > dsn=mysql-asterisk > readsql=SELECT col1, col2, col3 from table1 WHERE keycol = '${ARG1}' > > and here's the full log: > [Jan 22 20:12:50] VERBOSE[32348] pbx.c: -- Executing > [123 at dolookup:8] Set("SIP/sip1-inbound-00000f99", > "ARRAY(var1,var2,var3)=96829,-3,Name Unavailabl") in new stack > [Jan 22 20:12:50] DEBUG[32348] func_strings.c: array > (var1,var2,var3=96829) > [Jan 22 20:12:50] DEBUG[32348] func_strings.c: array set value > (var1=96829) > [Jan 22 20:12:50] DEBUG[32348] func_strings.c: array set value > (var2=(null)) > [Jan 22 20:12:50] DEBUG[32348] func_strings.c: array set value > (var3=(null)) > [Jan 22 20:12:50] WARNING[32348] pbx.c: MSet: ignoring entry '-3' with > no '=' (in +17322761300 at getcnam:8 > > Asterisk version: 1.6.2.14 > MySQL: 5.1.37 > > Thoughts? >Did you try this for your readsql? Select col1 + ',' + col2 + ',' + col3 from table1 where keycol = '${ARG1}' The function array split its results by the comma, but odbc_lookup is not going to return 1 value separated by commas. Worth a shot anyways.
On 11-01-22 08:46 PM, Sherwood McGowan wrote:> Gentlemen, > > I have googled, searched the mailing list archives, and even spoke on > the IRC channel, but have not found an answer to the following > problem. I am attempting to retrieve multiple columns in an ODBC query > using ARRAY per the solutions offered by many individuals. My dialplan > code is as follows: >Here is some code I use on Asterisk 1.8: extensions.conf --- [SQL_CheckCallback] exten => s,1,CELGenUserEvent(ODBC, ${CONTEXT}) same => n,Set(ARRAY(SCHEDULEID,CALLDATE,CONTACTID)=${ODBC_CHECKCALLBACK(${ARG1})}) same => n,Return(${ISNULL(${SCHEDULEID})}) func_odbc.conf --- [CHECKCALLBACK] dsn=asterisk readsql=SELECT s.Id, c.CallDate, c.Id FROM Schedules s, ListContacts c WHERE s.user='${SQL_ESC(${ARG1})}' AND s.Contact=c.Id AND s.Active=True Hope it helps. -- Paul Belanger Digium, Inc. | Software Developer twitter: pabelanger | IRC: pabelanger (Freenode) Check us out at: http://digium.com & http://asterisk.org
On Saturday 22 January 2011 19:46:16 Sherwood McGowan wrote:> Gentlemen, > > I have googled, searched the mailing list archives, and even spoke on > the IRC channel, but have not found an answer to the following > problem. I am attempting to retrieve multiple columns in an ODBC query > using ARRAY per the solutions offered by many individuals. My dialplan > code is as follows: > > exten => _.,n,Set(ARRAY(var1,var2,var3)=${ODBC_LOOKUP(${KEYVAL})}) > exten => _.,n,Verbose(2,var1 = ${var1}) > exten => _.,n,Verbose(2,var2 = ${var2}) > exten => _.,n,Verbose(2,var3 = ${var3}) > > Here's the func_odbc.conf code: > > [LOOKUP] > dsn=mysql-asterisk > readsql=SELECT col1, col2, col3 from table1 WHERE keycol = '${ARG1}' > > and here's the full log: > [Jan 22 20:12:50] VERBOSE[32348] pbx.c: -- Executing > [123 at dolookup:8] Set("SIP/sip1-inbound-00000f99", > "ARRAY(var1,var2,var3)=96829,-3,Name Unavailabl") in new stack > [Jan 22 20:12:50] DEBUG[32348] func_strings.c: array > (var1,var2,var3=96829) [Jan 22 20:12:50] DEBUG[32348] func_strings.c: > array set value (var1=96829) [Jan 22 20:12:50] DEBUG[32348] > func_strings.c: array set value (var2=(null)) [Jan 22 20:12:50] > DEBUG[32348] func_strings.c: array set value (var3=(null)) [Jan 22 > 20:12:50] WARNING[32348] pbx.c: MSet: ignoring entry '-3' with no '=' > (in +17322761300 at getcnam:8Add: [compat] app_set=1.6 to your asterisk.conf and restart. Basically, someone a long time ago decided that making Set take multiple key value pairs would be a good idea. This misfeature led to a great deal of dialplan confusion, with lots of escaping needed to make it work correctly. This is what I attempted to fix in 1.6, with 1.4 upgraders getting the old behavior by default (so their dialplans would not break on the upgrade) and new users getting the new behavior by default. This is all detailed in UPGRADE.txt, by the way. -- Tilghman