Asterisk 10 with odbc in dialplan

Asterisk Asterisk Support
1

Hello,
i want to use a mysql-Select within my dialplan.
the CLI shows me:

[code]CLI> odbc show

ODBC DSN Settings

Name: asterisk
DSN: asterisk
Last connection attempt: 2012-06-04 18:33:17

CLI> odbc read ODBC_ANKOMMEND 012345 exec
Failed to execute query. [SELECT allow_incomming FROM incoming WHERE Telefonnummer =‘012345’]
…: res_odbc.c:1527 odbc_obj_connect: Connecting asterisk
… WARNING: res_odbc.c:1552 odbc_obj_connect: res_odbc: Error SQLConnect=-1 errno=-1222579639 [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not
… WARNING: res_odbc.c:1367 _ast_odbc_request_obj2: Failed to connect to asterisk

[/code]

I have test a lot of settings in the configuration-Files.
The actual settings are:

***** func_odbc.conf *****
[ANKOMMEND]
dsn=asterisk
readsql=SELECT allow_incomming FROM incoming WHERE Telefonnummer =’${SQL_ESC(${ARG1})}’

*****res_odbc.conf *****
[ENV]

[asterisk]
enabled => yes
dsn => asterisk
pre-connect => yes
username => asterisk_user
password => geheim

/etc/unixODBC/odbc.ini
[ODBC Data Sources]
asterisk=MySQL

[asterisk]
driver = MySQL
Database = asterisk
Server = localhost
Socket = /var/run/mysql/mysql.sock ;Pfad geprüft, ist richtig
UserName = asterisk_user
Password = geheim

/etc/unixODBC/odbcinst.ini
[MySQL]
Driver = /usr/lib/libmyodbc5.so
SETUP = /usr/lib/libiodbcinst.so.2.1.19
UsageCount = 3

I can’t understood why “odbc show” shows me that he has connected, but “odbc select” says that he can’t find driver. (How he can connect without a driver??)

The Files are readable by asterisk:

linux-zrp2:/etc/unixODBC # ls -l insgesamt 12 drwxr-xr-x 2 asterisk asterisk 4096 29. Okt 2011 ODBCDataSources -rw-r--r-- 1 asterisk asterisk 423 4. Jun 18:30 odbc.ini -rw-r--r-- 1 asterisk asterisk 99 4. Jun 18:08 odbcinst.ini

within odbc.ini i also have testet with an replacement of the line “MySQL” with path of driver “/usr/lib/libmyodbc5.so”

Operating System ist openSuse 12.1
asterisk 10.4.1
everything is installed with yast (rpm Packages)

Any Ideas?

My original Post in german language http://www.ip-phone-forum.de/showthread.php?t=249198

Thx for help
Thomas

2

your odbc is not connected . if it was connected you could see it when you run odbc show …

AsterServer*CLI> odbc show asterisk

ODBC DSN Settings

Name: asterisk
DSN: asterisk-connector
Pooled: No
Connected: Yes

3

Please take a look at the following link

linux-notes.blogfa.com/post-117.aspx

I wrote it for myself to save CDR in mysql database through odbc .

4

Thx for help, but i have the same error.

Here the config files and system messages i get:

/etc/unixODBC/odbcinst.ini

linux-zrp2:/ # cat /etc/unixODBC/odbcinst.ini
[MySQL]
Description     = MySQL for ODBC DSN
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/unixODBC/libodbcmyS.so
FileUsage       = 1
;UsageCount     = 1

/etc/unixODBC/odbc.ini

linux-zrp2:/ # cat /etc/unixODBC/odbc.ini
[ODBC Data Sources]
asterisk-connector=MySQL

[asterisk-connector]
Driver          = MySQL
Description     = MySQL connection to 'asterisk' database
SERVER          = localhost
PORT            = 3306
USER            = root
Password        = secret
Database        = asterisk
;OPTION         = 3
SOCKET          = /var/run/mysql/mysql.sock

Test ODBC:

linux-zrp2:/ # odbcinst -q -d # [MySQL] linux-zrp2:/ # echo "select 1" | isql -v asterisk-connector +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> +---------------------+ | 1 | +---------------------+ | 1 | +---------------------+ SQLRowCount returns 1 1 rows fetched SQL>

/etc/asterisk/res_odbc.conf

[asterisk]
enabled => yes
dsn => asterisk-connector
username => root
passwort => secret
pooling => no
limit => 1
pre-connect => yes

I changed limit from 0 to 1 because with parameter 0 i get the message “816 load_odbc_config: Limit should be a number, not a boolean: ‘0’. Disabling ODBC class ‘asterisk’.”

CLI odbc show

[code]linux-zrp2*CLI> odbc show

ODBC DSN Settings

Name: asterisk
DSN: asterisk-connector
Last connection attempt: 2012-06-12 20:24:40
[/code]

Error Log

Jun 12 20:24:40 linux-zrp2 asterisk[591]: [Jun 12 20:24:40] NOTICE[591]: loader.c:1135 load_modules: 208 modules will be loaded. Jun 12 20:24:40 linux-zrp2 asterisk[591]: [Jun 12 20:24:40] NOTICE[591]: res_odbc.c:1527 odbc_obj_connect: Connecting asterisk Jun 12 20:24:40 linux-zrp2 asterisk[591]: [Jun 12 20:24:40] WARNING[591]: res_odbc.c:1552 odbc_obj_connect: res_odbc: Error SQLConnect=-1 errno=-121653394 8 [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not Jun 12 20:24:40 linux-zrp2 asterisk[591]: [Jun 12 20:24:40] WARNING[591]: res_odbc.c:1367 _ast_odbc_request_obj2: Failed to connect to asterisk Jun 12 20:24:40 linux-zrp2 asterisk[591]: [Jun 12 20:24:40] NOTICE[591]: res_odbc.c:916 load_odbc_config: Registered ODBC class 'asterisk' dsn->[asterisk- connector] Jun 12 20:24:40 linux-zrp2 asterisk[591]: [Jun 12 20:24:40] NOTICE[591]: res_odbc.c:1889 load_module: res_odbc loaded.

these are the files i have installed

[code]linux-zrp2:/usr/lib # ls odbc
libiodbcinst.so.2 libiodbc.so.2.1.19 libodbccr.la libodbccr.so.1.0.0 libodbcinst.so.1 libodbc.so
libiodbcinst.so.2.1.19 libmyodbc5-5.1.8.so libodbccr.so libodbcinst.la libodbcinst.so.1.0.0 libodbc.so.1
libiodbc.so.2 libmyodbc5.so libodbccr.so.1 libodbcinst.so libodbc.la libodbc.so.1.0.0
linux-zrp2:/usr/lib # ls my
libmyodbc5-5.1.8.so libmyodbc5.so libmysqlclient.so.18 libmysqlclient.so.18.0.0

mysql:
mysqld.sym plugin
[/code]
and second directory:

linux-zrp2:/usr/lib/unixODBC # ls libesoobS.la libnn.so.1.0.0 libodbcminiS.so.1 libodbcpsqlS.la libodbctxtS.so.1 libsapdbS.so libesoobS.so libodbcdrvcfg1S.la libodbcminiS.so.1.0.0 libodbcpsql.so libodbctxtS.so.1.0.0 libsapdbS.so.1 libesoobS.so.1 libodbcdrvcfg1S.so libodbcmyS.la libodbcpsql.so.1 liboplodbcS.la libsapdbS.so.1.0.0 libesoobS.so.1.0.0 libodbcdrvcfg1S.so.1 libodbcmyS.so libodbcpsql.so.1.0.0 liboplodbcS.so libtdsS.la libmimerS.la libodbcdrvcfg1S.so.1.0.0 libodbcmyS.so.1 libodbcpsql.so.2 liboplodbcS.so.1 libtdsS.so libmimerS.so libodbcdrvcfg2S.la libodbcmyS.so.1.0.0 libodbcpsql.so.2.0.0 liboplodbcS.so.1.0.0 libtdsS.so.1 libmimerS.so.1 libodbcdrvcfg2S.so libodbcnnS.la libodbcpsqlS.so liboraodbcS.la libtdsS.so.1.0.0 libmimerS.so.1.0.0 libodbcdrvcfg2S.so.1 libodbcnnS.so libodbcpsqlS.so.1 liboraodbcS.so libtemplate.la libnn.la libodbcdrvcfg2S.so.1.0.0 libodbcnnS.so.1 libodbcpsqlS.so.1.0.0 liboraodbcS.so.1 libtemplate.so libnn.so libodbcminiS.la libodbcnnS.so.1.0.0 libodbctxtS.la liboraodbcS.so.1.0.0 libtemplate.so.1 libnn.so.1 libodbcminiS.so libodbcpsql.la libodbctxtS.so libsapdbS.la libtemplate.so.1.0.0

Maybe i use the wrong files?

Now i recognize that it is a difference between unixODBC and iODBC.
The Testprogram “echo “select 1” | isql -v asterisk-connector” connect to the mysql, but the test iodbctest gives me the result:

[code]linux-zrp2:/var/log # iodbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0709.0909

Enter ODBC connect string (? shows list): ?

DSN | Driver

asterisk | MySQL

Enter ODBC connect string (? shows list): asterisk
1: SQLDriverConnect = [iODBC][Driver Manager]MySQL: Kann die Shared-Object-Datei nicht öffnen: Datei oder Verzeichnis nicht gefunden (0) SQLSTATE=00000
2: SQLDriverConnect = [iODBC][Driver Manager]Specified driver could not be loaded (0) SQLSTATE=IM003
[/code]

Thx for help
Thomas