EDIT: nevermind …
My total fail in copy/paste
zzzzaccount <> zzzzaccounts
I’m trying to query and update a MS SQL server database and when the FROM clause of the query is in func_odbc.conf rather than being passed from the dialplan.
I started off with code pieced together from various examples, and the examples where the SQL code is in func_odbc.conf failed, but the “GENERIC” examples where the SQL code is in the dialplan and passed as a single variable worked.
In an effort to figure out what is going wrong, I tried moving the SQL code clause by clause from the dial plan to func_odbc.conf. I could move "SELECT Balance " out of the dialplan and into and func_odbc.conf and it still worked, but when I tried also moving the “FROM” clause, it failed …
[dbo].[zzzzaccount] (
[AccountID] [int] NULL ,
[Balance] [money] NULL ,
[AccountIDchar] [char] (10) NULL
(I separated out AccountIDchar to understand how quoting ${ARG1} worked with various data types)
func_odbc.conf
[code][SQL]
prefix=GENERIC
dsn=SALESdsn
readsql=${SQL_ESC(${ARG1})}
[SQLFROM]
prefix=GENERIC
dsn=SALESdsn
readsql=${SQL_ESC(SELECT Balance ${ARG1})}
[SQLWHERE]
prefix=GENERIC
dsn=SALESdsn
readsql=${SQL_ESC(SELECT Balance FROM zzzzaccounts ${ARG1})}
[/code]
extensions.conf
[code][LocalSets]
;works
exten => 101,1,Read(AccountID,account_number,4,5)
same => n,Set(result=${GENERIC_SQL(SELECT Balance FROM zzzzaccount WHERE AccountID = ${AccountID})})
same => n,SayNumber(${result})
same => n,playback(digits/dollars)
same => n,Hangup()
;works
exten => 102,1,Read(AccountID,account_number,4,5)
same => n,Set(result=${GENERIC_SQL(SELECT Balance FROM zzzzaccount WHERE AccountIDchar = “${AccountID}”)})
same => n,SayNumber(${result})
same => n,playback(digits/dollars)
same => n,Hangup()
;works
exten => 201,1,Read(AccountID,account_number,4,5)
same => n,Set(result=${GENERIC_SQLFROM(FROM zzzzaccount WHERE AccountID = ${AccountID})})
same => n,SayNumber(${result})
same => n,playback(digits/dollars)
same => n,Hangup()
;works
exten => 202,1,Read(AccountID,account_number,4,5)
same => n,Set(result=${GENERIC_SQLFROM(FROM zzzzaccount WHERE AccountIDchar = “${AccountID}”)})
same => n,SayNumber(${result})
same => n,playback(digits/dollars)
same => n,Hangup()
;fails
exten => 301,1,Read(AccountID,account_number,4,5)
same => n,Set(result=${GENERIC_SQLWHERE(WHERE AccountID = ${AccountID})})
same => n,SayNumber(${result})
same => n,playback(digits/dollars)
same => n,Hangup()
;fails
exten => 302,1,Read(AccountID,account_number,4,5)
same => n,Set(result=${GENERIC_SQLWHERE(WHERE AccountIDchar = “${AccountID}”)})
same => n,SayNumber(${result})
same => n,playback(digits/dollars)
same => n,Hangup()[/code]
I really don’t want to put all the SQL in the dialplan … I’d much rather put it in func_odbc.conf
This is on a new install of asterisk 11 on Ubuntu 12.04, using Asterisk™: The Definitive Guide 4th as a guide, so everything should be set as recommended.
I’m not an expert on Linux, and obviously not on asterisk, so I’m not sure even where to start looking … any guidance would be appreciated