[asterisk-users] FUNC_ODBC and ARRAY

[Sherwood McGowan]

On Sat, Jan 22, 2011 at 8:31 PM, William Stillwell
<william at stillwellsoft.com> wrote:
>
>> -----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.
>
>
>
>
>
> --
> _____________________________________________________________________
> -- 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
>

Thanks for your input! I may try this, but I'm not sure it will make a
difference. The reason is, the log snippet:

-- Executing [123 at dolookup:8]
Set("SIP/sip1-inbound-00000f99","ARRAY(var1,var2,var3)=96829,-3,Name
Unavailabl") in new stack

shows that the returned result does indeed have commas between the
columns in the resultset, as it should.

I may still go ahead and try it, but I'm still curious if anyone has
other thoughts, or maybe even a (sanitized) code example that is
proven to work.

Thanks again mate!