Asterisk App_odbcexec

This is a module for Asterisk PBX that supports the unixODBC library. It's functionality is to add two commands for extensions.conf.
ODBCexec(sql_statement)
ODBCquery(value=query)

Get the sourcecode of app_odbcexec.c

In any sql_statement or query, before every "," must be "\".

Install ODBC


First you need unixODBC installed. You can install it with the --disable-gui if you don't have/want X. Lots of people have had problems with use ODBC from RPMs, so when possible, please use the ODBC source package.

unixODBC install instructions:

 cd /usr/src
 tar zxf unixODBC-2.2.7.tar.gz
 cd unixODBC-2.2.7
 ./configure --disable-gui
 make
 make install

Install

cp app_odbcexec.c  /usr/src/asterisk/apps/
vi /usr/src/asterisk/apps/Makefile +28
app_setcdruserfield.so app_random.so app_odbcexec.so
app_odbcexec.so: app_odbcexec.o
       $(CC) $(SOLINK) -o $@ $< -lodbc

make
make install

Configuring ODBC

Basic odbcinst.ini:

This is where you configure your various odbc drivers.

 [MySQL]]
 Description     = MySQL ODBC MyODBC Driver
 Driver          = /usr/lib/libmyodbc3.so
 FileUsage       = 1

 [Text]]
 Description     = ODBC for Text Files
 Driver          = /usr/lib/libodbctxt.so
 Setup           = /usr/lib/libodbctxtS.so
 FileUsage       = 1
 CPTimeout       =
 CPReuse         =

 [PostgreSQL]]
 Description     = PostgreSQL driver for Linux & Win32
 Driver          = /usr/lib/libodbcpsql.so
 Setup           = /usr/lib/libodbcpsqlS.so
 FileUsage       = 1


Note: you may have to change the path to the .so files depending on where they are installed.

Basic odbc.ini:

This is where you configure your datasources.

 [MySQL-asterisk]]
 Description     = MySQL ODBC Driver Testing
 Driver          = MySQL
 Socket          = /var/run/mysqld/mysqld.sock
 Server          = localhost
 User            = username
 Password        = password
 Database        = database
 Option          = 3
 #Port           =

 [Text-asterisk]]
 Description     = ODBC for Text Files test
 Driver          = Text
 Database        = /root/flatfile
 Trace           = Yes
 Tracefile       = /root/trace.log

 [PostgreSQL-asterisk]]
 Description         = Test to Postgres
 Driver              = PostgreSQL
 Trace               = Yes
 TraceFile           = sql.log
 Database            = asterisk
 Servername          = localhost
 UserName            = username
 Password            = password
 Port                = 5432
 Protocol            = 7.4
 ReadOnly            = No
 RowVersioning       = No
 ShowSystemTables    = No
 ShowOidColumn       = No
 FakeOidIndex        = No
 ConnSettings        =

Configuring Asterisk


odbcexec.conf:

 [global]]
 dsn=MySQL-asterisk
 username=username
 password=password

Usage

ODBCexec(sql_statement)
Executes a sql_statement in default database. Always returns 0. If the sql_statement fails, jumps to priority n+101 if available.

ODBCquery(value=query)
Retrieves a value from the database query and stores it in the given variable. Always returns 0. If the query fails, jumps to priority n+101 if available.

Example

extensions.conf:

exten => 99,1,ODBCquery(temp=select credit from credits where custommer=${CALLERIDNUM} limit 1)
exten => 99,2,GotoIf($[${temp} = 0]?10:3)
exten => 99,3,Dial(channel)
exten => 99,10,ODBCexec(insert into unapproved(custommer\,ext) values(${CALLERIDNUM}\,${EXTEN}))
exten => 99,11,Playback(no-credit,noanswer)
exten => 99,12,Hangup