Odbc issues Fail/Unable to execute query

Asterisk Asterisk Support
1

I am attempting to connect to a remote MySQL database and have had some measure of success as
evidenced by using the shell “isl” command or from the CLI odbc show.

pommeCLI> odbc show
pommeCLI>
ODBC DSN Settings

Name: debit
DSN: mysqldebit
Pooled: No
Connected: Yes

Therefore I think my data source is propely configured.

However from my dialplan when exectuting the read operation I receive
[Sep 10 09:31:19] ERROR[3518]: func_odbc.c:466 acf_odbc_read: Unable to execute query [SELECT AmtCurrent FROM account WHERE AccountNumber=‘1000340951’]

or from the CLI
pommeCLI> odbc read ODBC_ACCT_VER 12345 exec
Failed to execute query. [SELECT AmtCurrent FROM account WHERE AccountNumber=‘12345’]
pommeCLI>

/etc/asterisk res_odbc.conf

[debit]
enabled => yes
dsn => mysqldebit
pre-connect => yes
username => chuck
password => luck
sanitysql => select curtime();
idlecheck => 3600

/etc/asterisk/func_odbc.conf

[ACCT_VER]
dsn=mysqldebit
;dsn=xyz
readsql=SELECT AmtCurrent FROM account WHERE AccountNumber=’${SQL_ESC(${ARG1})}'
syntax=
synopsis=Check presence and current value of an Account.

/etc/odbc.ini
[mysqldebit]
Description = MySQL ODBC Driver Testing
Driver = MySQL
Socket = /var/run/mysqld/mysqld.sock
Server = 192.168.36.118
User = chuck
Password = luck
Database = debit
Option = 3
Port = 3306

It would seem that the dsn is not defined as I get the same result whether I point to mysqldebit or an
invalid xyz dsn in func_odbc.conf

I am running astesisk as a download binary.
Asterisk 1.6.2.5-0ubuntu1
I would have preferred to use the MYSQL addons but the downloadable binary did not agree with the main binary package so I resorted to experimenting wiht the odbc methods

Thoughts ?

Thanks

Dave

2

Hi Dave,

Are you able to resolve this issue. I have a similar issue and it seems ODBC is configured correctly.
I am able to insert values into my table through MySQL command line.

In Dialplan I am getting following error message

ERROR[2050]: func_odbc.c:515 acf_odbc_read: Unable to execute query [Select status from asterisk.ast_hotdesk where extension = ‘9999’

ODBC and Dial plan settings are
Ubuntu server
asterisk 1.8.4.2
mysql Ver 14.14 Distrib 5.1.54 for debian-linux-gnu (i686)

/etc/odbc.ini
Driver = MySQL
Database = asterisk
Server = localhost
Username = apbx
Password = 123456
Port = 3306
Socket = /var/run/mysqld/mysqld.sock
/var/lib/mysql/mysql.sock is not working in my case

/etc/asterisk/res_odbc.conf
[ast]
enabled => yes
dsn => asterisk-connector
username => apbx
password => 123456
pooling => no
limit => 5
pre-connect => yes

apbx has all the privileges and table ast_hotdesk is in database asterisk

echo “select status from asterisk.ast_hotdesk” | isql -v asterisk-connector
Connected!
sql-statement
help [tablename]
quit

SQL RowCount return 4
4 row fetched

It mean ODBC is working

/etc/asterisk/func_odbc.conf
[INFO]
prefix=HOTDESK
readsql=SELECT ${ARG1} from asterisk.ast_hotdesk where extension ‘${SQL_ESC(${ARG2})}’
– OR – I also tried
readsql=SELECT ${ARG1} from asterisk.ast_hotdesk where extension ‘${ARG2}’

/etc/asterisk/extension.conf
[hotdesk]
exten => _110[1-5],1,NoOp()
same => n,Set(E=${EXTEN})
same => n,Set(${E}_STATUS=${HOTDESK_INFO(status,${E})})

I can see ODBC connection in Dial plan
odbc show

Name = ast
DSN = asterisk-connector
Pooled = No
Connected = yes

3

I did check my settings , unixODBC setting is fine. Asterisk server is recognizing and able to connect with database.

However Dial plan is giving an error message when it receive a call and it say unable to execute query.

If I change the DSN then I can see the different error.

Looking for help

Thanks

4

in file func_odbc.conf, dsn must refer to the section defined in res_odbc.conf
Common error is to use dsn defined in /etc/odbc.ini.