Using func_odbc

By John Hass
john@asterikast.com

Keep in mind I wrote this from the top of my head so something may be broken.

func_odbc is a powerful utility allowing you to access your database from the dialplan.  In this example I am going to be using mysql.  You will need to have installed unixODBC and myodbc, today I am using ubuntu server 8.10.  I just used apt-get install unixodbc and apt-get install libmyodbc and it installed all the required libraries.  I had to recompile asterisk to get odbc compiled in, in 1.4.23.1 it is located under "Dialplan functions".  I then edited /etc/odbc.ini.  The table looks something like this (the word database is your database, and the word password is your password):


[ODBC Data Sources]
database    = MySQL ODBC 3.51 Driver DSN

[database]
Driver       = /usr/lib/odbc/libmyodbc.so
Description  = MySQL ODBC 3.51 Driver DSN
SERVER       = localhost
PORT         = 3306
USER         = root
Password     = password
Database     = database
OPTION       = 3

I then added the following to /etc/asterisk/res_odbc.conf
[ENV]
INFORMIXSERVER => my_special_database
INFORMIXDIR => /opt/informix

[asteriskMysql]
enabled => yes
dsn => database
pre-connect => yes
username=root
password=password

I then went into asterisk and typed:
module load func_odbc.so
The module loaded without incident.

if you are having issues you can easily go to your linux prompt and type:
"isql -vvv database" this is the interface from unixodbc to connect to your database.

Then you will need to edit /etc/asterisk/func_odbc.conf

I normally remove the contents of the file.

Then for the fun of it save I have a table with phone pins in it

id
pin

I want to check against my pin when the caller enters a pin code so in func_odbc.conf I write

[VALIDPIN]
dsn=asteriskMysl
read=select count(*) from phone_pins where pin="${SQL_ESC(${ARG1})}"

So let me go through this
[VALIDPIN] is similar to a context, it will be referenced from the dialplan.
dsn=asteriskMysql is the dsn you used in res_odbc.conf
read=... This is the sql query read means your returning data write means your changing data in the table.  SQL_ESC is a function that puts the correct slashes around your variables so you don't have to worry about it.  ARG1 is the first argument called from the set command.

Using this in your dialplan
exten => _900,1,answer
exten => _900,n,wait(20
exten => _900,n(getpin),read(pin,conf-getpin,30)
exten => _900,n,GotoIF($["${ODBC_VALIDPIN(${pin})}" != "1"]?getpin)
exten => _900,n,playback(tt-weasels)
exten => _900,n,hangup()

so you can see we called the ODBC function with a leading ODBC_ this is how the function is called.  but lets say you wanted to add new pins to the database. Using the same example.

func_odbc.conf
add the lines:
[ADDPIN]
dsn=asteriskMysl
write=insert into phone_pins (pin) values ("${SQL_ESC(${VAL1})}")

Notice I used VAL1 instead of ARG1 VAL1 is used in the set command.  I also changed read to write.

Here is an example dialplan
exten => _900,1,answer
exten => _900,n,wait(20
exten => _900,n(getpin),read(pin,conf-getpin,30)
exten => _900,n,GotoIF($["${ODBC_VALIDPIN(${pin})}" != "1"]?getpin)
exten => _900,n(enterpin),read(newpin,beep,10)
exten => _900,n,GotoIF($["${newpin}" = ""]?enterpin)
exten => _900,n,Set(ODBC_ADDPIN()=${newpin})
exten => _900,n,hangup()

So as you can see adding is just as easy as reading.

Returning 1 thing is nice, but lets say you wanted to return lots of data from the table, say maybe the whole table row.

in func_odbc.conf
[GETPINS]
dsn=asteriskMysql
read=select * from phone_pins where pin="${SQL_ESC(${ARG1})}"

this will return lots of data from the table in our case id and pin, not too useful but you get the idea.  Luckily the set command as the ability to use an array!

exten => _900,1,answer
exten => _900,n,wait(20
exten => _900,n(getpin),read(pin,conf-getpin,30)
exten => _900,n,Set(ARRAY(id,pin) = ${ODBC_GETPINS(${pin})})
exten => _900,n,noop(ID is ${id})
exten => _900,n,GotoIF($["${ODBC_VALIDPIN(${pin})}" != "1"]?getpin)
exten => _900,n(enterpin),read(newpin,beep,10)
exten => _900,n,GotoIF($["${newpin}" = ""]?enterpin)
exten => _900,n,Set(ODBC_ADDPIN()=${newpin})
exten => _900,n,hangup()

it is really that easy.

The cool thing about using func_odbc is you don't have to use MySQL or postgresql you could even use microsoft sql.  Of course I recommend MySQL.  It also allows you to add SQL processing to the dialplan without having to call agi scripts all the time.  You could see a potential boost in performance using func_odbc, because you Asterisk will no longer have to fork a new agi process everytime you want to mess with sql.