ODBC function-Write to database

General
1

I am not sure its relevant to TangoPBX but I know we have knowledgeable freepbx and asterisk experts here so hopefully someone can help me here,:slightly_smiling_face: .

freepbx 17. I am using ODBC to write some data to a Database(US_CIS2QUE and table is US_Customer )

freepbx user has the grant to insert to this database.

GRANT SELECT, INSERT, UPDATE ON US_CIS2QUE.* TO freepbxuser@localhost

func_odbc.conf :
[US_ADDCUSTOMER]
dsn = US_CIS2QUE
mode = write
writehandle = US_CIS2QUE
writesql = INSERT INTOS US_Customer SET cust_no=‘${ARG1}’, cust_name=‘${ARG2}’, queue_no=‘${ARG3}’, queue_name=‘${ARG4}’, caller_id=‘${ARG5}’
readsql = SELECT ‘OK’

odbc.ini

[US_CISCustInfo]
Description=Connect to US CISCustInfo US_CIS2QUE
driver=MySQL
server=localhost
database=US_CIS2QUE
Port=3306
Socket=
option=3

res_odbc_custom.conf

[US_CIS2QUE]
enabled => yes
dsn => US_CISCustInfo
username=> freepbxuser
password=> xxxxxxxxxx
pooling => yes
limit => 1
pre-connect => yes
autocommit => yes

And I call it from custom dial plan :

same => n,Set(RESULT=${ODBC_US_ADDCUSTOMER(${Cust_no},${Cust_name},${Queue_no},${Queue_name},${CALLERID(num)})})

In asterisk log I see RESULT is OK but data is not added to the database.

If I add it manually I can see data and if I update func_odbc to :
writesql = INSERT INTO US_Customer SET cust_no=‘100401’, cust_name=‘TEST CUSTOMER’, queue_no=‘99900001’, queue_name=‘Test Queue’, caller_id='764764

Same result. I dont see it in DB. Any idea why?

And this is odbc show

ODBC DSN Settings

Name: US_CIS2QUE
DSN: US_CISCustInfo
Number of active connections: 1 (out of 1)
Logging: Disabled

Appreciate the help in advance :slightly_smiling_face:

2

The fix was:

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_US_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 US_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})}’);