SQL Error handling

Hi

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 dial Plan function call

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, see screenshot below

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

The best way is to use AGI with php (console version). My setup is Gentoo, PHP 7.4, sqlsrv driver for php from Microsoft. Everything works as expected.

I have tried built-in Asterisk ODBC, but managed to execute single SQL statements. You have multiple statements. The execution of multiple statements did not work in my case. For me, the best way is to execute stored procedures from PHP. Both freetds and sqlsrv supports select, insert, delete, update statements and RPC statements (stored procedures).

In your case, write a stored procedure, call it with php, then in the dialplan call the php script file with the AGI application. same => n, AGI(path to stript(parameters))

Another thing you can do with PHP (console version) AND external SQL SERVER is an autodialer.

Thank you very much for the reply, but its a totally different approach than the one I’m using now. except if there is a way to call PHP console from inside Lua (extension.lua)

As I understand it, LUA is just a structured front end to the normal dialplan language. As such, I would expect to be able to call AGI or System applications the same way as the vast majority would call them.

Umm , I have not play with asterisk AGI yet. However regarding asterisk application. I can call them, my question is not about calling them is about how to know if there was a failure during the process of calling them ( I’m talking func_odbc.so functions )

The suggestion to use AGI was based on AGI allowing a sufficiently low level access to the database access library that you can see when it returns a failure indication.

However, in general, it is not a good idea to set up telephony database integration when the database and telephony aspects are sufficiently far apart that this is an issue.

This is just my personal opinion, but…

Database access should not be done in dialplan. Database access belongs in a ‘real’ programming language like one of the ‘P’ languages or C – where you have access to full functionality, error handling, and programming constructs without having to jump through a bunch of hoops.

I prefer writing ‘functional blocks’ as AGIs. For example, I would cobble this up as an AGI named ‘update-customer-balance’ with the ‘cif’ and ‘current-charge’ as parameters. In the AGI, I could handle invalid input, retry database connections, syslog()ging errors, etc. and return the status of the functional block as a channel variable, say ${AGI-STATUS}. Maybe I’d return status values like ‘SUCCESS’ or ‘FAILURE’, or ‘INVALID-INPUT’ so my next dialplan step could be goto(${AGI-STATUS}).

In the AGI, I’d use getopt_long() (because I’m that kind of guy) for command line processing so my AGI call would look like:

agi(update-customer-balance, --cif=${CIF}, --current-charge=${CURRENT-CHARGE})

Quick side…

Any chance you could update the topic title to ‘SQL Error handling’? It would make the topic easier for some bloke to search for in the future.

With mssql freetds php driver you may encounter difficulties in catching errors using try and catch block in SQL, because the driver only has the mssql_get_last_message() statement. Try and catch blocks may have multiple line response messages, the last one actually being a warning, not an error. In your example, you may execute a plain statement and check, if query succeeded: if (!mssql_query($query,$linkid)) {$error_code=-1; $error_desc=mssql_get_last_message(); } In this case you will get only one error message. Full error handling is implemented in sqlsrv driver from Microsoft.

With odbc, you are free to use both drivers, but I recommend sqlsrv.

I thank you all guys for the replies and the idea about how to implement database integration with Asterisk that allows me to have probable error handling. However my attention of this post was to get just a simple error handler, that at least can tell me if the execution went through or not, and I found it (Praise be to God)

it turns out that there is 2 channel variable that gets an update after every call of func_odb function has. those two variables are ODBCSTATUS and ODBCROWS . a simple dumpChan() will show you this two-channel variable

However, these two will act differently with writsql and readsql.

  • Writesql

The result when dealing with writesql , in the successful scenario you will have

ODB STATUS = SUCCESS
ODBCROW= 1 ( number of row the been effected )

In the case of writesql failed scenario, you will have

ODBSTATUS = FAILURE
ODBCROW= 0 ( number of row the been effected )

  • readsql

However when dealing with readsql successful scenario

ODB STATUS = SUCCESS
ODBCROW= 1 ( number of rows the been effected )

But when dealing with readsql failure scenario

ODBCSTATUS = will not exist
ODBCROWS = -1

From that, I can at least hand up the call, or try some recovery mechanism.

As you see, this is very simple why of handler if I wanna go more complex we will have to do something way that you guys mention which is summarized as follows

  1. Write AGI script that returns a channel variable to dial plan, we can use PHP or any other Language that AGI support.

1.1. Take note that PHP and ODBC you can use freetds driver sqlsrv driver, but freetds return only the last error message so you may want to which can be problematic in some cases

  1. create another table is the database which I update it after every transaction then after few 0.x seconds check it to see if everything is successful
  2. create simple rest API and use func_CURL.

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.