Search code examples
sqlsql-serverluaasteriskunixodbc

how to handle SQL failer from asterisk dial plan

Im using

  • Asterisk certified/16.8-cert2
  • MSSQL Express 2014
  • unixODBC 2.3.1
  • freetds v1.1.20
  • Lua 5.1.4
  • CentOS7

I doing update statement in SQL with func_odbc.so. The update statement is working fine with no issue. but I wanna away to be able to catch an error if it happens, like a disconnect or network interruption.

Here is my update statement

  UPDATE Customers SET AccountBalance = AccountBalance - ${VAL1} WHERE cif = ${ARG1}

My dialPlan function call ( in extention.lua)

local statues = channel.ODBC_ErrorTest(1499):set(10)

One of the thing that I though I can make it work, is by adding TRY and CATCH blook in the SQL statement itself like below, so if there is an error it will return the error number 

BEGIN TRY 
  UPDATE Customers SET AccountBalance = AccountBalance - a WHERE cif = 17399 
END TRY 
BEGIN CATCH 
 SELECT   ERROR_NUMBER() AS ErrorNumber 
END CATCH

But when I try to execute this it does not return me an error number when there is a failer, just return an empty string.

So my question is simple, how to handle SQL failer from the dial plan?

Solution

  • For update it should return -1 in case of writesql error.

    However you should understand, asterisk is PBX, not something suitable for complex sql check.

    Possible workarounds

    1) create other table with command, create external script which check it for new "actions" and send back results/error. Check in asterisk after 0.5sec, 1sec etc.

    2) create simple rest API and use func_CURL.