SQL error handling

Dexterity allows you to control how SQL error messages will appear to the user. When a SQL error occurs, Dexterity’s default behavior is to display a dialog like the one shown in the following illustration.

The user can click More Info to display specific information about the SQL error that occurred. For some SQL errors, you may want to suppress this dialog, display your own message, or log the message in a table. You can use the SQLError procedure to do this.

SQLError procedure

If you have added the SQLError procedure to your application, it will run each time a SQL error occurs. The SQLError procedure is automatically passed parameters containing information about the error that occurred. Based on this information, you specify a return value from the SQLError procedure indicating how the error message will be handled.

The SQLError procedure uses several parameters; six in parameters that are automatically passed into the script, and a single out parameter that specifies how the error message will be handled.

• internal_table_ID – A long integer containing the internal ID number of the table for which the SQL error occurred.
• dict_ID – An integer containing the ID of the dictionary that was active when the SQL error occurred.
• table_series – An integer containing the series of the table for which the SQL error occurred.
• table_ID – An integer containing the resource ID of the table for which the SQL error occurred.
• operation – An integer specifying the table operation that caused the SQL error. This value is provided for error logging purposes only. It is subject to change in future versions of Dexterity. The SQLError procedure should not perform conditional operations based on this value.
• resource_name – A string containing the name of the script or report that was being run when the SQL error occurred.
• error_response – An integer out parameter specifying how the SQL error message will be handled. The response corresponds to one of the following values:

Reading error information

Within the SQLError procedure, you will want to be able to read the error text that would normally be displayed to the user. To do this, use the Table_GetSQLErrorText() function. The information retrieved can be displayed to the user or logged in a file. Refer to the description of the Table_GetSQLErrorText() function later in this document for more information.

Sample SQLError procedure

The following SQLError procedure logs every SQL error that occurs in the application. The error information is stored in the c-tree table named SQL_Error_Log. The value 0 is returned to the error_response parameter so the default SQL error message is displayed to the user.

in long internal_table_ID;
in integer dict_ID;
in integer table_series;
in integer table_ID;
in integer operation;
in string resource_name;
inout integer error_response;

local integer status;
local string error_text;
local long native_error_number;

{Log the SQL error that has occurred.}
get last table SQL_Error_Log;
if err() = EOF then
	{This is the first error to log.}
	'Sequence Number' of table SQL_Error_Log = 0;
else
	{Log the next SQL error.}
	increment 'Sequence Number' of table SQL_Error_Log;
end if;

{Read the SQL error information.}
status = Table_GetSQLErrorText(internal_table_ID, error_text, native_error_number);
while status = STATUS_SUCCESS do
	{Successfully read an entry in the error text, so log the error.}
	'Log Date' of table SQL_Error_Log = sysdate();
	'Log Time' of table SQL_Error_Log = systime();
	'Resource' of table SQL_Error_Log = resource_name;
	'Native Error' of table SQL_Error_Log = native_error_number;
	'Error Text' of table SQL_Error_Log = error_text;
	save table SQL_Error_Log;

	{Read the next portion of the SQL error text.}
	status = Table_GetSQLErrorText(internal_table_ID, error_text, native_error_number);
end while;

{Use the default behavior that displays the SQL error to the user.}
error_response = 0;

Integrating applications

If your Dexterity application integrates with a product that uses SQL Server, you can create a procedure trigger for the SQLError procedure. Be sure to register the trigger to activate before the SQLError procedure runs. In the trigger processing procedure, check the dict_ID parameter from the SQLError procedure to find out whether it was your product that encountered the SQL error.

Documentation Feedback

---