# \[Solved\] ODBC can't execute query **URL:** \ **Category:** Asterisk Support\ **Created:** [September 19, 2017, 12:30pm UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067 "2017-09-19T12:30:23Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![vinsbg](https://avatars.discourse-cdn.com/v4/letter/v/f08c70/32.png) [@vinsbg](https://community.asterisk.org/u/vinsbg)\ **Post date:** [September 19, 2017, 12:30pm UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/1 "2017-09-19T12:30:23Z")
I have set it up ODBC in order to be able to query the database. When I make a call it doesn’t query and the error which I receive is > [Sep 19 15:18:10] ERROR[35328][C-00000009]: func\_odbc.c:835 acf\_odbc\_read: Unable to execute query [SELECT language FROM sippeers WHERE name=‘1001’] This is the setup. res\_odbc.conf ```auto [general] enabled => yes dsn => asterisk username => asterisk password => mypass pre-connect => yes max_connections => 5 ``` This is func\_odbc.conf ```auto [LANG] dsn=asterisk readsql=SELECT language FROM sippeers WHERE name='${SQL_ESC(${ARG1})}' ``` This is odbc.ini ```auto [asterisk] Description = MySQL Connection for "Asterisk" Database Driver = MySQL Database = asterisk_test User = asterisk Password = mypass Port = 3310 Server = 127.0.0.1 Socket = /var/lib/mysql/mysql.sock ``` And this is in my extension.conf ```auto exten => 119,3,GotoIf($["${LANG}" = "en"]?Voicemail,6) ``` odbc show in CLI ```auto *CLI> odbc show ODBC DSN Settings ----------------- Name: general DSN: asterisk Number of active connections: 1 (out of 5) ``` Can anyone tell me why I get this error and can’t query the database? edit: isql query doesn’t seem okay too ```auto # echo "select 1" | isql -v asterisk +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select 1 Segmentation fault (core dumped) ``` ---
**Author:** ![mkozusnik](https://sea1.discourse-cdn.com/asterisk/user_avatar/community.asterisk.org/mkozusnik/32/1654_2.png) [@mkozusnik](https://community.asterisk.org/u/mkozusnik)\ **Post date:** [September 19, 2017, 4:44pm UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/2 "2017-09-19T16:44:55Z")
Not sure how it’s handled inside of Asterisk, but “name” is a reserved word in MySQL. Can you try to surround `name` with back-ticks? (On a US keyboard, it’s above the Tab, left of the 1, and on the same key with the tilde) “Language” may also cause you a problem. ---
**Author:** ![vinsbg](https://avatars.discourse-cdn.com/v4/letter/v/f08c70/32.png) [@vinsbg](https://community.asterisk.org/u/vinsbg)\ **Post date:** [September 20, 2017, 4:46am UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/3 "2017-09-20T04:46:10Z")
Thanks for the answer. I’ve tried what you suggested but no luck. Here is the output (still same) ```auto # echo "select name from sippeers where 'name' = 1001" | isql -v asterisk +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select name from sippeers where 'name' = 1001 Segmentation fault (core dumped) ``` Also this table structure is taken directly from asterisk “contrib/realtime/mysql/mysql\_config.sql” When I log into mysql and execute same query the result is correct ```auto mysql> select name from sippeers where `name` = 1001; +------+ | name | +------+ | 1001 | +------+ 1 row in set (0,00 sec) mysql> ``` ---
**Author:** ![mkozusnik](https://sea1.discourse-cdn.com/asterisk/user_avatar/community.asterisk.org/mkozusnik/32/1654_2.png) [@mkozusnik](https://community.asterisk.org/u/mkozusnik)\ **Post date:** [September 21, 2017, 10:55am UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/4 "2017-09-21T10:55:59Z")
In the upper one, with the seg fault, it still looks like a single quote, not a back tick as shown in the bottom example. ```auto `back ticks` 'single quote' ``` ---
**Author:** ![vinsbg](https://avatars.discourse-cdn.com/v4/letter/v/f08c70/32.png) [@vinsbg](https://community.asterisk.org/u/vinsbg)\ **Post date:** [September 21, 2017, 11:18am UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/5 "2017-09-21T11:18:10Z")
Here is what happens when I put backticks ```auto # echo "select name from sippeers where `name` = 1001" | isql -v asterisk -bash: name: command not found +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select name from sippeers where = 1001 Segmentation fault (core dumped) ``` ---
**Author:** ![navaismo](https://sea1.discourse-cdn.com/asterisk/user_avatar/community.asterisk.org/navaismo/32/2481_2.png) [@navaismo](https://community.asterisk.org/u/navaismo)\ **Post date:** [September 21, 2017, 11:54am UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/6 "2017-09-21T11:54:48Z")
Backticks are for “escape” bash commands inside another string or command. That’s why you got that error. Can you show the complete part of the dialplan where You invoke the query and also the enable the debug and check the full log if there is more information. ---
**Author:** ![vinsbg](https://avatars.discourse-cdn.com/v4/letter/v/f08c70/32.png) [@vinsbg](https://community.asterisk.org/u/vinsbg)\ **Post date:** [September 21, 2017, 12:01pm UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/7 "2017-09-21T12:01:26Z")
So this is the entire dialplan ( I mean not entire but the important part ) ```auto [internal] exten => 119,1,NoOp() exten => 119,2,Set(LANG=${ODBC_LANG(${CALLERID(num)})}) exten => 119,3,GotoIf($["${LANG}" = "en"]?Voicemail,6) ``` When I call I see this in the cli console ```auto Executing [119@internal:1] NoOp("SIP/1001-00000001", "") in new stack [Sep 21 14:58:45] ERROR[39677][C-00000002]: func_odbc.c:835 acf_odbc_read: Unable to execute query [SELECT language FROM sippeers WHERE name='1001'] -- Executing [119@internal:2] Set("SIP/1001-00000001", "LANG=") in new stack -- Executing [119@internal:3] GotoIf("SIP/1001-00000001", "0?Voicemail,6") in new stack -- Goto (internal,Voicemail,1) -- Executing [Voicemail@internal:1] NoOp("SIP/1001-00000001", "") in new stack -- Executing [Voicemail@internal:2] Playback("SIP/1001-00000001", "mainmenu") in new stack ``` In my case in database I have set ‘en’ and should Goto Voicemail,6 but as you can see “LANG=” This is in my func\_odbc.conf ```auto [LANG] dsn=asterisk readsql=SELECT language FROM sippeers WHERE name='${SQL_ESC(${ARG1})}' ``` ---
**Author:** ![navaismo](https://sea1.discourse-cdn.com/asterisk/user_avatar/community.asterisk.org/navaismo/32/2481_2.png) [@navaismo](https://community.asterisk.org/u/navaismo)\ **Post date:** [September 21, 2017, 12:19pm UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/8 "2017-09-21T12:19:20Z")
Please enable the debug by running: `core set debug 5` make sure you have the full enabled and then: `tail -f /var/log/asterisk/full` And try again the call. ---
**Author:** ![mkozusnik](https://sea1.discourse-cdn.com/asterisk/user_avatar/community.asterisk.org/mkozusnik/32/1654_2.png) [@mkozusnik](https://community.asterisk.org/u/mkozusnik)\ **Post date:** [September 21, 2017, 1:31pm UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/9 "2017-09-21T13:31:28Z")
Also try escaping the back ticks and possibly the quotes… ```auto \`name\` ``` ---
**Author:** ![mkozusnik](https://sea1.discourse-cdn.com/asterisk/user_avatar/community.asterisk.org/mkozusnik/32/1654_2.png) [@mkozusnik](https://community.asterisk.org/u/mkozusnik)\ **Post date:** [September 21, 2017, 1:34pm UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/10 "2017-09-21T13:34:46Z")
I prefer to have the SQL in the dialplan. It makes it easier for me to review, so here’s what I have… I guess single-quotes don’t need to be escaped. I’ll have to clean my code up. ```auto extensions.conf same => n,Set(exten=${ODBC_READSQL(select ext_dialed from extensions where ext_did=\'${CALLERID(num)}\' or ext_dialed=\'${CALLERID(num)}\')}) same => n,Set(ARRAY(canpickup,devtype)=${ODBC_READSQL(select count(*)\, ext_dev_type from pickup left outer join extensions on extension=ext_dialed where extension='${EXTEN:3:5}' and allowed_exten='${CHANNEL:4:5}')}) same => n,Set(ODBC_WRITESQL()=update extensions set ext_forward_flag=\'1\' where ext_did=\'${CALLERID(num)}\' or ext_dialed=\'${CALLERID(num)}\') func_odbc.conf [READSQL] dsn=asterisk readsql=${ARG1} [WRITESQL] dsn=asterisk writesql=${VAL1} ``` ---
**Author:** ![satish4asterisk](https://avatars.discourse-cdn.com/v4/letter/s/5f9b8f/32.png) [@satish4asterisk](https://community.asterisk.org/u/satish4asterisk)\ **Post date:** [September 21, 2017, 3:02pm UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/11 "2017-09-21T15:02:43Z")
Try changing dsn name to general instead of asterisk in func\_odbc.conf, reload func odbc and see. Alternatively you can add below context in res\_odbc.conf, reload res\_odbc and try. [asterisk] dsn =\> asterisk username =\> asterisk password =\> mypass –Satish Barot ---
**Author:** ![ambiorixg12](https://sea1.discourse-cdn.com/asterisk/user_avatar/community.asterisk.org/ambiorixg12/32/1887_2.png) [@ambiorixg12](https://community.asterisk.org/u/ambiorixg12)\ **Post date:** [September 21, 2017, 5:38pm UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/12 "2017-09-21T17:38:14Z")
I 'm agreed with satish4asterisk , your problem it is your func\_odbc.conf confguration incorrect dsn setting and also add a context on the res\_odbc.conf file with the correct information, below you have a correct example of how your confinguration should be. ![image](https://global.discourse-cdn.com/asterisk/original/2X/5/549d069689736ba84f45f4d5688cffdf1bec9d49.png) ---
**Author:** ![vinsbg](https://avatars.discourse-cdn.com/v4/letter/v/f08c70/32.png) [@vinsbg](https://community.asterisk.org/u/vinsbg)\ **Post date:** [September 27, 2017, 2:24pm UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/13 "2017-09-27T14:24:10Z")
When I change the DSN in func\_odbc.conf to general instead of asterisk and reload the module in CLI I see unregistering and registering them successfully ```auto == Parsing '/etc/asterisk/func_odbc.conf': Found == Unregistered custom function ODBC_LANG == Unregistered custom function ODBC_EXIST == Registered custom function 'ODBC_EXIST' == Registered custom function 'ODBC_LANG' ``` Then when I try this command in CLI, the Asterisk is restarted and got this error ```auto *CLI> odbc read ODBC_LANG 1002 exec Segmentation fault (core dumped) ``` What segmentation fault mean? And sometimes while I changing settings I got this error ```auto *CLI> odbc read ODBC_LANG 1002 exec Failed to execute query. [SELECT language FROM sippeers WHERE name='1002'] ``` ---
**Author:** ![vinsbg](https://avatars.discourse-cdn.com/v4/letter/v/f08c70/32.png) [@vinsbg](https://community.asterisk.org/u/vinsbg)\ **Post date:** [September 28, 2017, 8:17am UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/14 "2017-09-28T08:17:46Z")
@mkozusnik, this doesn’t help because still need to use ODBC which is the problem part. It’s not that different from mine. Mine setup just have different prefix, no? ---
**Author:** ![satish4asterisk](https://avatars.discourse-cdn.com/v4/letter/s/5f9b8f/32.png) [@satish4asterisk](https://community.asterisk.org/u/satish4asterisk)\ **Post date:** [September 28, 2017, 2:21pm UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/15 "2017-09-28T14:21:01Z")
What version of Asterisk do you use? Do you get this on latest Asterisk 13? Could you try changing dns name from general to something else and see? ---
**Author:** ![vinsbg](https://avatars.discourse-cdn.com/v4/letter/v/f08c70/32.png) [@vinsbg](https://community.asterisk.org/u/vinsbg)\ **Post date:** [September 29, 2017, 4:55am UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/16 "2017-09-29T04:55:13Z")
The version is asterisk-14-current. I have managed to fix the issue with Segmentation fault. It turns out that the problem is in the mysql and odbc connector/driver. However, this issue still exists. > Failed to execute query. [SELECT language FROM sippeers WHERE name=‘1002’] This one now is working and return correct result > echo “select name from sippeers where `name` = 1001” | isql -v asterisk ---
**Author:** ![vinsbg](https://avatars.discourse-cdn.com/v4/letter/v/f08c70/32.png) [@vinsbg](https://community.asterisk.org/u/vinsbg)\ **Post date:** [September 29, 2017, 8:53am UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/17 "2017-09-29T08:53:04Z")
Okay, thanks to everybody for the help. The issue is resolved! It turns out that for an unknown reason to me, asterisk doesn’t want to use table **sippeers**. When I created table **sipfriends** and added the record there is started working right away. P.S. I don’t know how to mark the thread as resolved. ---
**Author:** ![ambiorixg12](https://sea1.discourse-cdn.com/asterisk/user_avatar/community.asterisk.org/ambiorixg12/32/1887_2.png) [@ambiorixg12](https://community.asterisk.org/u/ambiorixg12)\ **Post date:** [September 29, 2017, 2:42pm UTC](https://community.asterisk.org/t/solved-odbc-cant-execute-query/72067/18 "2017-09-29T14:42:55Z")
Closed on September 29, 2017, 2:42pm UTC