Developers

Omnis Technical Note TNSQ0017 December 2007

SQL Error Handler

For Omnis Studio 4
By Andreas Pfeiffer

When accessing a server database using Omnis table classes certain errors may be generated, usually based on an incorrect table or column name, or some incorrect SQL syntax. You can very easily create a handler to catch these errors and report them to the end-user. 

All methods built into the table class use the $sqlerror() method automatically when a SQL error occurs. Whenever one of the standard SQL methods, such as $insert(), $update(), or $select(), encounters an error, for example, because the assigned Where clause included an invalid column name, the $sqlerror() method is activated. 

You can base all your table classes on a table superclass and override the $sqlerror() method in the table super class, defining "pErrorType", "pErrorCode" and "pErrorText". These parameters will be filled automatically and contain information about any SQL errors. 

Furthermore, all built-in methods use the statement object of the table class instance, and you can retrieve further information about any errors via this object. In the $sqlerrror() method of the table class you can therefore use $cinst.$statementobject.$nativeerrortext to read the error text which is returned from your SQL server. During development, you could use a local variable "statRef" of the type Item Reference in the $sqlerror() method and insert the two commands: 

Set reference statRef to $cinst.$statementobject 
Breakpoint

This way you can check the parameter pErrorText and the variable statRef when a SQL error occurs. Then you can open this variable during debugging and press F6 to open the Property Manager. You can see the properties of the statement object, that is, $sqltext, $nativeerrorcode, and so on. 

But it would be much more elegant to use an additional window which you can open from within $sqlerror(): 

Do $windows.wSQLError.$open('*',kWindowCenter,
  $cinst.$statementobject,pErrorCode,pErrorText)

This technique will work at runtime, therefore any SQL errors will be displayed for your customers in the SQL error window. The entire statement object is passed to the SQL error window so its properties can be accessed in the $construct() method of the window. 

The wSQLError window

You need to declare the following parameters: pStatementobjRef (Item Reference), pErrorCode (Character), and pErrorText (Character) in the $construct() method of the SQL error window. 

To avoid errors in the $construct(), you need to check the validity of pStatementobjRef since the SQL connection may have been cancelled and the session is no longer valid. After this, you can copy the required properties into instance variables in order to display them in the window: 

If len(pStatementObjRef.$name)>0
Calculate ivSQLText as pStatementObjRef.$sqltext
Calculate ivNativeErrorCode as pStatementObjRef.$nativeerrorcode
Calculate ivNativeErrorText as pStatementObjRef.$nativeerrortext
End If
Do $cinst.$title.$assign(con('SQL Error:',pErrorCode,' - ',pErrorText))

The method stack

The sys(192) function in Omnis returns a list of methods in the method stack, which you can use to display the methods where the error has occurred. You need to create a list and delete the first two lines as they return info about the error window itself and of the $sqlerror() method: 

Calculate ivStackList as sys(192)
Do ivStackList.$remove(1)
Do ivStackList.$remove(1)

The list contains the following columns: Class, Object, Method, Line, Line Text, Parameter; note that column 6 is a two column list containing information about the method parameters and their contents. You can use a complex grid on your window containing fields that map to the columns in the method list. 

You can add a button to print the contents of the window, with the following code in its $event() method: 

On evClick
Send to printer
Print top window

Your window will look something like this: