This will probably be a quick topic, but I wanted to ask any way.
Asterisk 1.8
MySQL Server version: 5.1.61
I have a connection that is up and working, which I have several calls to func_odbc that I would like to reduce down to as few as possible.
Below is my func_odbc.
[code]
[CID]
prefix=LOOKUP
dsn=tsb-connector
readsql=SELECT CASE WHEN telephonenumber1 = ‘${SQL_ESC(${ARG1})}’ then ‘${SQL_ESC(${ARG1})}’ else ‘0000000000’ end as telephonenumber1 from telephones group by telephonenumber1 order by telephonenumber1 desc limit 1
[COMPANY]
prefix=LOOKUP
dsn=tsb-connector
readsql=SELECT companyname,linestatus FROM telephones WHERE telephonenumber1 = ${SQL_ESC(${ARG1})}[/code]
Below is my extension.conf
[DBDIP]
exten => Start,1,Verbose(${CID})
exten => Start,n,Verbose(Looking for ${CID} in TSB DB)
exten => Start,n,Set(CID=${LOOKUP_CID(${CID})})
exten => Start,n,GoToIf($[${CID}=0000000000]?PromptMenu,CollectDigits,AcctNumber:)
exten => Start,n,Verbose(Acct. Number ${CID} was found in TSB DB)
exten => Start,n,Verbose(Getting Company Name Acct. Number ${CID})
exten => Start,n,Set(ARRAY(Company,Status=${LOOKUP_COMPANY(${CID})})
exten => Start,n,Verbose(Acct. Number ${CID} is with ${Company})
exten => Start,n,Verbose(Acct. Number ${CID} has a line status of : ${Status})
Output from CLI:
Executing [Start@DBDip:1] Verbose("SIP/201-00000011", "9994441212") in new stack
9994441212
-- Executing [Start@DBDip:2] Verbose("SIP/201-00000011", "Looking for 9994441212 in TSB DB") in new stack
Looking for 9994441212 in TSB DB
-- Executing [Start@DBDip:3] Set("SIP/201-00000011", "CID=9994441212") in new stack
-- Executing [Start@DBDip:4] GotoIf("SIP/201-00000011", "0?PromptMenu,CollectDigits,AcctNumber:") in new stack
-- Executing [Start@DBDip:5] Verbose("SIP/201-00000011", "Acct. Number 9994441212 was found in TSB DB") in new stack
Acct. Number 9994441212 was found in TSB DB
-- Executing [Start@DBDip:6] Verbose("SIP/201-00000011", "Getting Company Name Acct. Number 9994441212 in under") in new stack
Getting Company Name Acct. Number 9994441212
-- Executing [Start@DBDip:7] Set("SIP/201-00000011", "ARRAY(Company,Status=OTH,HOTLINE") in new stack
-- Executing [Start@DBDip:8] Verbose("SIP/201-00000011", "Acct. Number 9994441212 is with ") in new stack
Acct. Number 9994441212 is with
-- Executing [Start@DBDip:9] Verbose("SIP/201-00000011", "Acct. Number 9994441212 has a line status of : ") in new stack
Acct. Number 9994441212 has a line status of : Can I combine my “SELECT CASE WHEN” Statement from [LOOKUP_CID] with the “SELECT” Statement from [LOOKUP_COMPANY]
As you can see from the output the Array and SQL are working correctly but I need to be able to call the variables individually later on the the call process for further queries from several other DB’s.
Am I missing something or is there someway to do this besides hitting the database several times to SET each variable.
Question on a side note:
CID comes into asterisk as XXXXXXXXXX, the DB being searched has CID Stored as (XXX) XXXXXXX, yes there is a space between the first five character and the last seven, looking for some ideas on how to convert straight ten to include the parentheses and space, or ignore them in the query.
Thanks in advance for any help
RC