Func_odbc: strange behavior. (Asterisk 14.3.1) - SOLVED

Asterisk Asterisk Support
1

Hi, I’m stumped…
I have this func_odbc config:
[GSM]
dsn=MySQL-asterisk
readsql = INSERT INTO tt_chiamate_ricevute (Data_chiamata,Telefono) VALUES(’${SQL_ESC(${ARG1})}’, ‘${SQL_ESC(${ARG2})}’)

My extension:
exten => receiver,n,NoOp(${ODBC_GSM(${STRFTIME(${EPOCH},%Y/%m/%d %H:%M:%S)},${SIP_HEADER(From):1:13})})

My question is: if I got this config the record will be inserted but I have this (right warning) "Error -1 in FETCH [INSERT INTO tt_chiamate_ricevute (Data_chiamata,Telefono) VALUES(‘2017/04/04 19:12:13’, ‘+3xxxxxxxx’)]

if I change func_odbc from readsql to writesql I receive this error: “Function ODBC_GSM cannot be read”;
if I change func_odbc from readsql to insertsql I receive this error: “Section ‘GSM’ was found, but there was no SQL to execute. Ignoring.”

You have any idea?

Regards, Luca

2

Instead of doing a NoOP have you tried using a SET with your writesql function?

exten => receiver,n,Set(ODBC_GSM()=${STRFTIME(${EPOCH},%Y/%m/%d %H:%M:%S)},${SIP_HEADER(From):1:13})})

3

Hi johnkiniston, good idea; I have never felt this.
I tried but I receive this error: Function ODBC_GSM cannot be written to

4

I don’t know why but between readsql and writesql i often found difficulties…

Function can not be read if i remember correctly meant config problem on ODBC…naming of dsn or similar …

So often i choose the old and deprecated (but always functioning) app_mysql…so you can test the sql statement before diving into func_odbc

5

Hi simone686, with app_mysql work properly.

I don’t like the idea to use a deprecated function…

Luca

6

Can you paste your func_odbc.conf for your ODBC_GSM entry?

7

Hi johnkiniston, this is my func_odbc.conf

[GSM]
dsn=MySQL-asterisk
readsql = INSERT INTO tt_chiamate_ricevute (Data_chiamata,Telefono) VALUES(’${SQL_ESC(${ARG1})}’, ‘${SQL_ESC(${ARG2})}’)

8

Did you change it to writesql when you tried my Set() based code?

You can’t do a Set() to a Read.

9

uhm, maybe I didn’t change from readsql to writesql…
I tried now but I receive this error:
Set(“SIP/gsm2-000003db”, “ODBC_GSM()=2017/04/05 19:38:43,+3xxx”) in new stack
[Apr 5 19:38:43] WARNING[31007][C-00000175]: func_odbc.c:477 execute: SQL Execute returned an error -1: HY000: [MySQL][ODBC 5.1 Driver][mysqld-5.5.5-10.2.4-MariaDB-log]Incorrect datetime value: ‘’ for column ‘Data_chiamata’ at row 1 (121)
[Apr 5 19:38:43] WARNING[31007][C-00000175]: func_odbc.c:486 execute: SQL Exec Direct failed (-1)![INSERT INTO tt_chiamate_ricevute (Data_chiamata,Telefono) VALUES(’’, ‘’)]

But with app_mysql works:
exten => ins,n,MYSQL(Query resultID ${connID} INSERT INTO tt_chiamate_ricevute(Data_chiamata,Telefono) VALUES("${STRFTIME(${EPOCH},%Y/%m/%d %H:%M:%S)}","${SIP_HEADER(From):1:13}"))

?? a different way to passing variables?

I don’t understand
Luca

10

change your writesql to use ${VAL1} and ${VAL2} not ${ARG1} and ${ARG2}, Arguments and Values are not the same and my example used Values sorry.

1 Like
11

Great idea, You fixed the problem.
thank you

exten => receiver,n,Set(ODBC_GSM()=${STRFTIME(${EPOCH},%Y/%m/%d %H:%M:%S)},${SIP_HEADER(From):1:13})

func_odbc.conf
[GSM]
dsn=MySQL-asterisk
writesql = INSERT INTO tt_chiamate_ricevute (Data_chiamata,Telefono) VALUES(’${SQL_ESC(${VAL1})}’, ‘${SQL_ESC(${VAL2})}’)

2 Likes
12

same=>n,system(mysql --user=root --password=‘11200’ dialer -e “INSERT INTO call_status (id,status,channel) values (NULL,‘ringing’,’${CHANNEL}’)”)