I HAVE A DREAM ODBC CNAM Lookup for AsteriDex

[Rrrr]

I am trying out post #13 from this thread: CNAM lookup with Asteridex.
I would appreciate a correction on this:

root@incrediblepbx:~/Google/gdata-2.0.18# echo "read=SELECT `name` FROM `user1` WHERE `out`='${SQL_ESC(${ARG1})}'" >> /etc/asterisk/func_odbc.conf
-bash: name: command not found
-bash: user1: command not found
-bash: out: command not found
-bash: read=SELECT `name` FROM `user1` WHERE `out`='${SQL_ESC(${ARG1})}': bad substitution

 

[Rrrr]

No luck trying to work through Asteridex lookup.

I set func_odbc.conf as follows (I also made it executable):

[ASTERIDEXCNAM]
dsn=MySQL-asteridex
read=SELECT `name` FROM `user1` WHERE `out`='${SQL_ESC(${ARG1})}'

Resulting output:

-- Executing [s@macro-outbound-callerid:1] NoOp("SIP/221-00000006", "*** Dial Number: 31123123123***") in new stack
-- Executing [s@macro-outbound-callerid:2] Set("SIP/221-00000006", "num2find=0031123123123") in new stack
-- Executing [s@macro-outbound-callerid:3] Set("SIP/221-00000006", "CALLERID(num)=0031123123123") in new stack
-- Executing [s@macro-outbound-callerid:4] Set("SIP/221-00000006", "USERDATA=") in new stack
-- Executing [s@macro-outbound-callerid:5] Set("SIP/221-00000006", "USER=") in new stack
-- Executing [s@macro-outbound-callerid:6] Set("SIP/221-00000006", "CALLERID(name)=") in new stack
-- Executing [s@macro-outbound-callerid:7] NoOp("SIP/221-00000006", "*** Dial Name: ***") in new stack
-- Executing [s@macro-outbound-callerid:8] Set("SIP/221-00000006", "CDR(accountcode,r)=") in new stack

The SQL statement works in PHPMYADMIN
How do I troubleshoot this further?

 

[wardmundy]

No luck trying to work through Asteridex lookup.

I set func_odbc.conf as follows (I also made it executable):

[ASTERIDEXCNAM]
dsn=MySQL-asteridex
read=SELECT `name` FROM `user1` WHERE `out`='${SQL_ESC(${ARG1})}'

Resulting output:


The SQL statement works in PHPMYADMIN
How do I troubleshoot this further?

Well, you've changed the dialplan code from what was posted. We should probably start there. Are you using the lgaetz code or mine? Kinda hard to troubleshoot without knowing what you've put in place.

I suspect it's looking for a match on 0031123123123 in the AsteriDex database. Is that what you have in your AsteriDex record, or do you have 31123123123?

 

[Rrrr]

I uninstalled @Igaetz module.
I moved [macro-dialout-trunk-predial-hook] to extensions_custom.conf and removed it from extensions_override_freepbx.conf
In [macro-dialout-trunk-predial-hook] I commented out the superfecta line and uncommented the asteridex line.

I copied [macro-outbound-callerid] code from extensions_additional.conf to extensions_override_freepbx.conf and inserted your code, with a change:

exten => s,n,Set(num2find=00${DIAL_NUMBER})

I commented out the 5 lines for superfecta and uncommented the 3 lines for Asteridex.
I have all contacts in Asteridex with 00+e164. So 0031123123123 exists in the Asteridex database and when I use the SQL statement manually in phpmyadmin, then two matching contacts are found (!).
amportal restart
and CLI> reload


------
addition: I tried another number where there is only one match in Asteridex. Also here the output does not show. How do I know if func_odbc.conf is executed?

 

[wardmundy]

But you're not using MySQL for the query. You're using ODBC. Something is misconfigured in your ODBC setup. Do you have the following entry in /etc/odbc.ini...

[MySQL-asteridex]
Description    = asteridex MySQL ODBC
Driver          = MySQL
Socket          = /var/lib/mysql/mysql.sock
Server          = localhost
User            = root
Password        = raspberry
Database        = asteridex
Option          = 3

Have you run /root/odbc-gen.sh??

 

[wardmundy]

I've answered my own question. I see that you're running on a Raspberry Pi. Incredible PBX on that platform does not yet include many of the components required to support ODBC, nor is Asterisk properly configured to support it. You can take a look at this article, but it was written for CentOS whereas the Raspberry Pi uses a modified version of Debian. Think French and Chinese!

 

[Rrrr]

Right, thanks, I saw the article and it looked complicated. I speak 5 languages, but not Chinese.
I will go back to the (much slower) Superfecta, unless I can write a little script to do the MySql lookup and call it from the dialplan?
Do you have a suggestion what might work? I have recently had some experience in python if that helps.

 

[wardmundy]

Rrrr: Asterisk no longer supports MySQL, just ODBC. I'll add it to the next refresh of Incredible Pi.