[Solved] ODBC can't execute query

vinsbg · September 19, 2017, 12:30pm

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

[general]
enabled		    => yes
dsn		        => asterisk
username	    => asterisk
password	    => mypass
pre-connect  	=> yes
max_connections => 5

This is func_odbc.conf

[LANG]
dsn=asterisk
readsql=SELECT language FROM sippeers WHERE name='${SQL_ESC(${ARG1})}'

This is odbc.ini

[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

exten => 119,3,GotoIf($["${LANG}" = "en"]?Voicemail,6)

odbc show in CLI

*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

# echo "select 1" | isql -v asterisk
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1
Segmentation fault (core dumped)

mkozusnik · September 19, 2017, 4:44pm

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.

vinsbg · September 20, 2017, 4:46am

Thanks for the answer. I’ve tried what you suggested but no luck. Here is the output (still same)

# 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

mysql> select name from sippeers where `name` = 1001;
+------+
| name |
+------+
| 1001 |
+------+
1 row in set (0,00 sec)

mysql>

mkozusnik · September 21, 2017, 10:55am

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.

`back ticks`
'single quote'

vinsbg · September 21, 2017, 11:18am

Here is what happens when I put backticks

# 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)

navaismo · September 21, 2017, 11:54am

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.

vinsbg · September 21, 2017, 12:01pm

So this is the entire dialplan ( I mean not entire but the important part )

[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

 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

[LANG]
dsn=asterisk
readsql=SELECT language FROM sippeers WHERE name='${SQL_ESC(${ARG1})}'

navaismo · September 21, 2017, 12:19pm

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.

mkozusnik · September 21, 2017, 1:31pm

Also try escaping the back ticks and possibly the quotes…

\`name\`

mkozusnik · September 21, 2017, 1:34pm

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.

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}

satish4asterisk · September 21, 2017, 3:02pm

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

ambiorixg12 · September 21, 2017, 5:38pm

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.

vinsbg · September 27, 2017, 2:24pm

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

  == 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

*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

*CLI> odbc read ODBC_LANG 1002 exec
Failed to execute query. [SELECT language FROM sippeers WHERE name='1002']

vinsbg · September 28, 2017, 8:17am

@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?

satish4asterisk · September 28, 2017, 2:21pm

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?

vinsbg · September 29, 2017, 4:55am

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

vinsbg · September 29, 2017, 8:53am

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.