Whats wrong in this ODBC setup?

Asterisk Asterisk Support
1

I get the result of readsql, but I writesql does not work.

func_odbc

[SLS_ADDCUSTOMER]
prefix=ODBC
dsn=MYSQL-SLS_CIS2QUE
mode=write
writehandle=SLS_CIS2QUE
readsql=SELECT cust_name,queue_no,queue_name FROM SLS_Customer WHERE cust_no=‘${SQL_ESC(${ARG1})}’
writesql=INSERT INTO SLS_Customer (cust_no,cust_name,queue_no,queue_name,caller_id) VALUES (‘1234’,‘testcustomer’,‘123456’,‘test’,‘323424’);
readsql = SELECT ‘OK’

odbc.ini

[MYSQL-SLS_CIS2QUE]
Description=MYSQL Connect to ‘SLS_CIS2QUE’ database
driver=MySQL
server=localhost
database=SLS_CIS2QUE
Port=3306
Socket=
option=3

res_odbc_custom

[SLS_CIS2QUE]
enabled => yes
dsn => MYSQL-SLS_CIS2QUE
username=> freepbxuser
password=> xxxxxxxxxx
;pooling => no
;limit => 1
pre-connect => yes
database=>SLS_CIS2QUE

freepbxuser has access to the database

I can write this enter using

isql -v MYSQL-SLS_CIS2QUE freepbxuser xxxxxxxxxx

And this is my dialplan:

[Add-CallerID-Call-Queue]
exten => s,1,NoOp(Add new caller ID for existing customer)
same => n,NoOp(This is existing customer with new caller ID. Add him to the DB:${Cust_no})
same => n,Set(RESULT=${ODBC_SLS_ADDCUSTOMER(${Cust_no})})

2

You’re using this incorrectly. This invokes “readsql” because you’re reading a dialplan function. If you want to use “writesql” you’d use Set on the dialplan function to write to it:

same => n,Set(ODBC_SLS_ADDCUSTOMER(${Cust_no})=1)

Though I haven’t tested it.

You’ve also configured “readsql” twice in SLS_ADDCUSTOMER.

3

Its working Thanks you!

4

How was it resolved ? And might you please link back to the Asterisk Forums where the solution was found (as you posted this elsewhere e.g. FreePBX Community Forums) ?

5

The variable I am passing to the ODBC function here comes from another ODBC read function I used before calling this one

Changing the calling ODBC to

[Add-CallerID-Call-Queue]
exten => s,1,NoOp(Add new caller ID for existing customer)
same => n,NoOp(This is existing customer with new caller ID. Add him to the DB:${Cust_no})
same => n,Set(ODBC_SLS_ADDCUSTOMER(${Cust_no},${Cust_name},${Queue_no},${Queue_name},${CALLERID(num)})=${Cust_no},${Cust_name},${Queue_no},${Queue_name},${CALLERID(num)})
same => n,NoOp(Newcustomer added: RESULT=${RESULT})

And writesql command to

writesql=INSERT INTO SLS_Customer (cust_no,cust_name,queue_no,queue_name,caller_id) VALUES (‘${SQL_ESC(${VAL1})}’,‘${SQL_ESC(${VAL2})}’,‘${SQL_ESC(${VAL3})}’,‘${SQL_ESC(${VAL4})}’,‘${SQL_ESC(${VAL5})}’);

I

6

Yes it is and I pasted the fix here.