Adding ODBC connection (SQL, new table)

More of an asterisk question, but hoping someone can assist me.

I’m trying to automate ALL THE THINGS, and as part of that, I want to make a Lookup for which voicemail box to route calls to, based on the dialed number.
i cant do this Directly in dynroute, as I want to automate the usage, as:
SQL → get (NextStep) from (table) where (calledNumber) like 12345


readsql=SELECT IFNULL((SELECT Target from voicemailLookup where DialedNumber LIKE \'${ARG1}\'),\'ext-local,vmu200,1\') as TargetNum FROM voicemailLookup

and my dynroute:

[dynroute-stephen] ; Stephens Testing Dyroute
exten => s,1,NoOp(STEPHEN: fromdid was ${FROM_DID})
exten => s,n,Set(TargetNum=${MYSQL_RouteToVoicemails(${FROM_DID})});
exten => s,n,NoOp(STEPHEN SQL IS ${TargetNum})

(I’ve got other logic wrapped around that…)

my problem is:
I can make the SQL table
I can query it by hand, flawlessly
I can call SQL from dialplan

but, I cant call This database / table.

Excerpt form Full log.

  • func_odbc.c: SQL Exec Direct failed (-1)![SELECT IFNULL((SELECT Target from voicemailLookup where DialedNumber LIKE '12345'),'ext-local,vmu200,1') as TargetNum FROM voicemailLookup]

  • func_odbc.c: Unable to execute query [SELECT IFNULL((SELECT Target from voicemailLookup where DialedNumber LIKE '123456'),'ext-local,vmu200,1') as TargetNum FROM voicemailLookup]

I think all I need is to update the ODBC to connect to this new table.

I’ve tried setting it in res_odbc_custom.conf,
I’ve verified that freepbxuser has access to the database and table

so how the heck does one get dialplan / FreePBX to be able to connect to a non-standard LOCAL SQL table? any help is greatly appreciated.

here is a working example I have on an active FreePBX 14 system.
Appended to the end of etc/odbc.ini

Description=MySQL connection for Custom database information

created in /etc/asterisk/res_odbc_custom.conf

;Custom Connection for Unique Database Queries
;this has to match what is in /etc/odbc.ini
dsn => sb-custom
;Use the AMPDBUSER value from /etc/amportal.conf
username => freepbxuser
;Use the AMPDBPASS value from /etc/amportal.conf
password => XXXXXXXXXXX
;This setting is in the default res_odbc_additional.conf
;The database to use
;These settings were in both files.
enabled => yes
pre-connect => yes

The sql query in /etc/asterisk/func_odbc.conf

; Return the CID to use based on the AUTCODE dialed.
; If the AUTCODE was valid, but there is no cid_num, populate with a defualt value.
readsql=SELECT cid_num FROM autcodes WHERE code = '${ARG1}'

Snippet of how it is used in custom dialplan

; Check if the AUTCODE has a CID associated with it. 

thats gotten me MOST of the way there!

When I try to trigger my code, I see this in /var/log/asterisk/full

pbx_functions.c: Function MYSQL_RouteToVoicemails not registered

but… when I do a core reload… it seems to say it IS registering it…

[2022-08-04 10:51:26] NOTICE[27651]: res_odbc.c:697 load_odbc_config: Registered ODBC class ‘asteriskcdrdb’ dsn->[MySQL-asteriskcdrdb]

so confused…
so my code is There, and its triggering, but the ODBC isn’t registered…
if I run odbc show, my DSN is listed…

on this server, i ONLY have odbc show, there is no odbc read or odbc write listed like another install I have…

i can isql to the database just fine.

What you’ve provided as a notice is merely for res_odbc, which is the connection. It’s not for func_odbc which is what handles the dialplan function and registers it. Is the func_odbc module actually loaded? Does it output any errors or warnings upon loading?

how would one find that?
I dont see it in the web under Module Admin
I done see anything referencing ODBC in fwconsole ma list on either a Working server or the one I’m working on now

ODBC DSN Settings

  Name:   asteriskcdrdb
  DSN:    MySQL-asteriskcdrdb
    Number of active connections: 1 (out of 5)
    Logging: Disabled

both servers show listed in /usr/lib64/asterisk/modules

/etc/asterisk/modules.conf includes it on both servers:

preload =
preload =
preload =
preload =
preload =
preload =
preload =
noload =
noload =
noload =
noload =

What does “module load” in the Asterisk console do/say?

As well, any output from func_odbc at Asterisk start time would be in /var/log/asterisk/full - or if Asterisk was loaded earlier it could have been rotated out.

module load
yeilds “already registered”

voip-ave*CLI> module load
voip-ave*CLI> module load 
Unable to load module
Command 'module load ' failed.
[2022-08-04 11:20:38] ERROR[11895]: pbx_functions.c:394 __ast_custom_function_register: Function ODBC_FETCH already registered.
[2022-08-04 11:20:38] WARNING[11895]: pbx_app.c:115 ast_register_application2: Already have an application 'ODBCFinish'
[2022-08-04 11:20:38] ERROR[11895]: pbx_functions.c:394 __ast_custom_function_register: Function MYSQL_RouteToVoicemails already registered.
[2022-08-04 11:20:38] ERROR[11895]: pbx_functions.c:394 __ast_custom_function_register: Function SQL_ESC already registered.
[2022-08-04 11:20:38] ERROR[11895]: pbx_functions.c:394 __ast_custom_function_register: Function SQL_ESC_BACKSLASHES already registered.

Function MYSQL_RouteToVoicemails already registered.

What the what?
NOW i have odbc {read, show, write}

and its alive!!! thank you!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.