Asterisk func_odbc or strange ael

According to the results of voting in the group, I start the series of articles “Asterisk realtime” and the first article will be devoted to func_odbc or how to simplify my life.

The article is designed for people who already use asterisk and have "basic" skills.

Func_odbc why is he?

And so why should I use Function_ODBC if I can make the same request in ael?

• in extensions.ael, it looks “tidier” if your request is repeated dozens of times in the configuration
• more convenient to work with multiple databases

Otherwise, if you are satisfied with the house in the configuration, as well as writing or copying the same request several times, then this article is not for you. For the rest, let's get started:
')

Func_odbc.conf

We will consider the simplest example of asterisk + mysql.

So, we have a sipuser (many sipusers) and they have an external cid number, which, due to reasons independent of us, is constantly changing, and we need to ask for it every time.

Second example: we need forwarding.
We skip the odbc.ini and res_odbc.conf settings, since you already know how to do this.

We proceed to paragraph 1:

Register in func_odbc.conf the following

[cid] dsn=asterisk readsql=SELECT cid FROM sipusers WHERE username = ${ARG1} 

And disassembled in parts

dsn=asterisk - The DSN parameter is responsible for connecting Asterisk to the database specified in the /etc/odbc.ini file.

readsql=SELECT cid FROM sipusers WHERE username = ${ARG1} - the sql query you need, but with a variable.

Now we look what we got in ael:

 _89. => { Set(cid=${ODBC_cid(${CALLERID(num)})}); SET(CALLERID(num)=${cid}); SET(CALLERID(name)=${cid}); ...... } 

 Set(cid=${ODBC_cid(${CALLERID(num)})}); -   SELECT cid FROM sipusers WHERE username = ${CALLERID(num)})} ,   . SET(CALLERID(num)=${cid}) -  CALLERID(num) SET(CALLERID(name)=${cid}) - CALLERID(name) 

Actually further your imagination in requests and variables.

We proceed to paragraph 2:

Add func_odbc.conf

 [forward] dsn=asterisk readsql=SELECT numforward, `type` FROM call_forwarding WHERE number = ${ARG1} 

It should be noted that in this answer we get an array.

Now we look what we got in ael:

 macro redirect(number, from){ Set(ARRAY(forward,type)=${ODBC_forward(${number})}); } if (${EXISTS(${forward})}) { switch(${type}) { case all: .... case noanswer: .... case noanswer-worktime: .... break; default: break; } hangup; } return; }; 

Here, for re-addressing, I decided that it is more convenient to use macros

Set(ARRAY(forward,type)=${ODBC_forward(${number})}); - we get two parameters from the request, therefore, we need to use an array.
if (${EXISTS(${forward})}) - if there is a forwarding number, then proceed further ...

switch(${type}) - we define the type of redirection and, depending on the type you need, we make the conditions for the redirection.

I deliberately missed the configurations of redirects, since everyone can have it different.

PS: This is certainly not realtime. But the bottom line is that you get some of the parameters at the right time with a specific call, and you don’t need to go into the config to change it and reload it to change the cid.

PPS: except for readsql, there is writesql, which works on the same principle.