Asterisk func_odbc mysql readsql

1

Hi all…
i found very tricky to write to mysql tables via func_odbc…
read values and select them no problem…but no way to write a single value…
I had to use the old and deprecated MYSQL function inside dialplan…with no problem…but…

Is there a place to find informations or samples?

2

What trouble have you run into?

Here is an example from my dialplan:

[SETDIDFWD]
dsn=odbc_TN
writesql=UPDATE TN SET `ForwardedTo`=${SQL_ESC(${VAL2})} WHERE (`TN`='${SQL_ESC(${VAL1})}');

This function updates my numbers database to insert a forward.

I call it in the dialplan with:

exten => s,n,Set(ODBC_SETDIDFWD()=${ARG1},${ARG2})

3

Post your query to see if you have any mistakes.

4

this my func_odbc

[IVRTICKETLOOKUP]
dsn=bluenergy
writesql= INSERT INTO ticket (data,ora,numero_chiamante,codice_cliente,numero_contratto,kg_bombola) VALUES (’${SQL_ESC(${ARG1})}’,’${SQL_ESC(${ARG2})}’,’${SQL_ESC(${ARG3})}’,’${SQL_ESC(${ARG4})}’,’${SQL_ESC(${ARG5})}’,’${SQL_ESC(${ARG6})}’)

res_odbc it’s ok because i can see connection on cli with ODBC SHOW command

app_mysql works with these statements inside Dialplan

same => n,MYSQL(Connect connid localhost root pwd bluenergy)
same => n,MYSQL(QUERY resultid ${connid} INSERT INTO ticket (data,ora,numero_chiamante,codice_cliente,numero_contratto,kg_bombola) values (${ticket01},${ticket02},${ticket03},${ticket04},${ticket05},${ticket06}))

This one via ODBC It’s not functioning

same => n,Set(wrticket=${ODBC_IVRTICKETLOOKUP(‘INSERT INTO ticket (data,ora,numero_chiamante,codice_cliente,numero_contratto,kg_bombola) VALUES (${ticket01},${ticket02},${ticket03},${ticket04},${ticket05},${ticket05})’)})

$ticket variable it’s correctly evalueated

same => n,Set(ticket01=${SHELL(/var/tmp/adesso.sh):0:-1})
same => n,Set(ticket02=${SHELL(/var/tmp/hours.sh):0:-1})
same => n,Set(ticket03=${CALLERID(num)})
same => n,Set(ticket04=${codicecliente})
same => n,Set(ticket05=${num_contck})
same => n,Set(ticket06=${ideticck})

I always get Function IVRTICKETLOOKUP can not be read

this is my mysql table

CREATE TABLE ticket (numero_ticket INT NOT NULL AUTO_INCREMENT PRIMARY KEY,data DATE,ora CHAR(5),numero_chiamante CHAR(15),codice_cliente CHAR(5),numero_contratto INT,kg_bombola CHAR(10)) ;

Thanks…

5

I believe you are calling your function wrong.

Instead of:

same => n,Set(wrticket=${ODBC_IVRTICKETLOOKUP(\'INSERT INTO ticket (data,ora,numero_chiamante,codice_cliente,numero_contratto,kg_bombola) VALUES (${ticket01},${ticket02},${ticket03},${ticket04},${ticket05},${ticket05})\')})

Try

same => n,Set(ODBC_IVRTICKETLOOKUP() = ${ticket01},${ticket02},${ticket03},${ticket04},${ticket05},${ticket05})

6

Hi…and thanks for helping me…i tried…but nothing happened on DB…
the cli shows this…

– Executing [s@inbound:9] Set(“SIP/200-00000000”, "ideticck=01 ") in new stack
– Executing [s@inbound:10] GotoIf(“SIP/200-00000000”, “0?inbound,i,1:”) in new stack
– Executing [s@inbound:11] Set(“SIP/200-00000000”, “ticket01=220616”) in new stack
– Executing [s@inbound:12] Set(“SIP/200-00000000”, “ticket02=1248”) in new stack
– Executing [s@inbound:13] Set(“SIP/200-00000000”, “ticket03=200”) in new stack
– Executing [s@inbound:14] Set(“SIP/200-00000000”, “ticket04=14691”) in new stack
– Executing [s@inbound:15] Set(“SIP/200-00000000”, “ticket05=2279”) in new stack
– Executing [s@inbound:16] Set(“SIP/200-00000000”, "ticket06=01 ") in new stack
– Executing [s@inbound:17] Set(“SIP/200-00000000”, "ODBC_IVRTICKETLOOKUP()=220616,1248,200,14691,2279,01 ") in new stack
– Executing [s@inbound:18] BackGround(“SIP/200-00000000”, “menu”) in new stack

7

Increase your verbosity and debug level and post the output, You are not giving enough info here to tell what is going on.

8

Can i ask a different question?
How do i manage primary key on writesql statement?
I have auto increment primary key…