Title: Changing Interbase Error Messages in runtime
Question: This article shows a technique used to tackle with Interbase errors on the client side, using Interbase Express (IBX). When you receive such an error, you get an ugly message (from user's perspective) such as "Violation of primary or unique key constraint 'INTEG_2' on table 'COUNTRY'". Using this technique, you can turn those messages into more human ones, or translate them into your own language.
Answer:
Changing Interbase Error Messages
=================================
This article shows a technique used to tackle with Interbase errors on the client side, using Interbase Express (IBX). When you receive such an error, you get an ugly message (from user's perspective) such as "Violation of primary or unique key constraint 'INTEG_2' on table 'COUNTRY'". Using this technique, you can turn those messages into more human ones, or translate them into your own language.
The objective is to change all non handled exceptions, so the TApplication's OnMessage event is used to peek the name of the broken constraint directly from Interbases Status Vector and replace the default text with our own, taken from a file. This error processing can be plugged into any project with a couple of lines; the bother part is to construct the equivalences file, so well go a little further developing a simple application to construct that file, using Interbases System Tables.
As for the first part, well put all the code in a separate unit and provides access to it through global functions. The three global functions are as in next table:
Function/Procedure | Called from | Parameters | Comments
==========================================================================
LoadDict | Main program | Name: string | Dictionary file name
Must be called before error processing start
--------------------------------------------------------------------------
DisplayMessageIB | Main program | E: Exception | Current Exception object
Returns True if succesfully processed the error, False if the exception was not EIBError
--------------------------------------------------------------------------
GetConstraintName | DisplayMessageIB | - |
Returns the name of last constraint broken, extracted from Status Vector.
Kept as global just as a bonus.
==========================================================================
The technique is not difficult to grasp. First of all, we need a place to store the messages; it could be anything capable of keeping pairs of related strings: the name of the constraint and the associated message. Well stick here to simplicity, using an INI file which we will call the dictionary file.
Dictionary replacement
======================
Once we get to the name of the broken constraint (well talk about that later), we have to search for the message associated with it, if exists. We will use a simple .INI file to keep pairs of the form
Constraint_Name=Message
For example,
INTEG_2=The country already exists
This file will be called Dictionary from now on. All the equivalence pairs will be put in a single section entitled Constraints, so this file could be really the same one that your application uses to keep general settings.
So, we need to have access to an entire section of an INI file; the code reads the entire section in a TStringList object so we can easily search for a name afterwards. You can see the relevant code in Figure 5: in the initialization section the string list is created (by the way, the string list is private to this unit); it is filled in function LoadDict, which should be called from the application before any processing takes place; and it is deallocated in units finalization section.
Once we have the messages in a string list, we can begin processing Interbase errors.
Error processing
================
When a constraint is broken in a database operation, the server assembles a special vector called the Status Vector with information such as the internal error code, the SQL error code is any, the name of the constraint broken, and a standard message ready for display. The Status Vector is divided (logically, not physically) into clusters of one or more elements; each element is a 32-bit number. The first element of each cluster identifies the type of cluster. The useful types of clusters of the Status Vector are shown in the table in Figure 2; the complete reference is in Interbases Api Guide document.
Cluster ID | |
(first element) | Elements in cluster | Meaning
==============================================================================
0 | | End of Status Vector.
1 | 2 | The next element is Interbases Error
Code.
2 | 2 | Next element is a string parameter for
replacing in standard message.
3 | 3 | Next element is the size in bytes of a
variable-length string whose address is
in clusters third element.
4 | 2 | Next element is a number parameter for
replace in standard message.
5 | 2 | Next element is the address of a string
with the standard message, the
parameters already replaced, ready for
display.
==============================================================================
This status Vector is made available to us by Interbase Express (IBX) as an array of isc_status elements (32 bits signed integers: longint). The definitions are found in IBExternals.pas. There is a series of global functions for working with the Status Vector (StatusVector, StatusVectorArray, etc) in unit IB.pas. The one we will use is StatusVectorArray, which returns a pointer to the first element of the status vector array:
type
ISC_STATUS = Long;
TStatusVector = array[0..19] of ISC_STATUS;
PStatusVector = ^TStatusVector;
function StatusVectorArray: PStatusVector;
When there is an error, IBX calls IBDatabaseError, which in turn raises an EIBInterBaseError or an EIBInterBaseRoleError exception (two classes that inherit from EIBError without modification). This exception object is what we get in TApplication.OnException event handler as parameter E. In fact, we dont need that exception object to get the Status Vector since it is global and corresponds to last executed command; but we will use the error codes inside the main processing function to display a somewhat detailed message in case we dont have our own, of course.
The first step in processing an error is to look for the name of the broken constraint in the staus vector. The code is in function GetConstraintName, which returns the name if found, or an empty string if not.
Next, we search the StringList for that name. If we found a match, we display the associated message. If we cant find the constraint name in the list, we build a standard message showing the error codes and the server message.
All this process is the task of function DisplayMessageIB, which takes the exception object as a parameter to get the error codes. This is the only function we need to call from TApplications OnException event handler.
Implementation
==============
We have three functions and a String List; it all could be encapsulated in a class, but for the sake of simplicity they are in a separate unit (uErrorMessages.pas, in Figure 5).
To use the technique in an application, this unit has to be listed in main forms uses clause and we have to call
LoadDict before all start; tipically, main forms OnCreate event handler.
DisplayMessageIB from Application.OnException event handler. The DisplayMessageIB function returns a boolean value which will be True if the exception was processed, or False if not (because it could not be an IBError). If it was not processed, then the exception is not one of Interbase, and should follow the standard processing.
Its easier to show the implementation using an example.
A little example
================
We will build a simple application which uses table Employee from database Employee.gdb in Delphi demos, generate the dictionary file with custom messages, and raise an exception to see the new message.
Create a file named employee.ini with the following text in it (you can use Windows notepad):
[Constraints]
INTEG_30=Salary out of bounds
Now start a new application in Delphi, and add a form with a DBGrid and the necessary components to display table Employee from database Employee.gdb.
Add the following procedure to main forms class:
procedure TForm1.ProcessExcept(Sender: TObject; E: Exception);
begin
if not DisplayMessageIB(E) then
ShowMessage(E.Message);
end;
Now add these lines in main form OnCreate event handler (if your INI file is not in Windows directory, modify the LoadDict parameter):
LoadDict(employee.ini);
Application.OnException:= ProcessExcept;
Add uErrorMessages.pas to main forms uses clause.
Thats all. Run the application, and break the constraint it is a check constraint on Salary field- by changing Leslie Johnsons salary to 150000. When you try to post the change you should see the new Salary out of bounds message. Any other Interbase exception display the standard error message, including the error codes.
Dictionary file creation
========================
All of the above is fine, once we have the INI dictionary file created; but this could be a daunting task, for it involves extracting constraints names and associate messages to every one of interest. To help in this task we will write a program which will extract all constraint names, display their definition and let the user write the associated message it could even create some standard messages automatically.
In brief, this application extracts all constraints names and some other information from Interbase System Tables, and puts that info in a Listview for display and temporary storage.
When any one constraint is selected in the ListView, its definition is displayed in a read-only memo component.
In another memo component, the user can write its own message for this constraint.
When finished, all messages can be saved to an INI file of choice.
It all starts when we select a Database and open it. The information about this Databases constraints is in its System Tables, special tables created and mantained by Interbase to keep track of all metadata information.
The application extract all constraint names and other data using the following query:
SELECT RC.RDB$CONSTRAINT_NAME as Nombre,
RC.RDB$CONSTRAINT_TYPE as Tipo,
RC.RDB$RELATION_NAME as Tabla,
CC.RDB$TRIGGER_NAME as Campo,
RC.RDB$INDEX_NAME as Indice,
T.RDB$TRIGGER_SOURCE as Definicion
FROM RDB$CHECK_CONSTRAINTS CC
RIGHT JOIN RDB$RELATION_CONSTRAINTS RC
ON CC.RDB$CONSTRAINT_NAME = RC.RDB$CONSTRAINT_NAME
LEFT JOIN RDB$TRIGGERS T
ON CC.RDB$TRIGGER_NAME = T.RDB$TRIGGER_NAME
ORDER BY RC.RDB$CONSTRAINT_TYPE, RC.RDB$CONSTRAINT_NAME
We have no room here to make a deep description of Interbase system tables, but you can find them in Interbase Language Reference document. The following table shows the different kinds of constraints that exist, and where to find the necessary information.
Constraint type | Auxiliary table | Information available
===============================================================================
NOT NULL | RDB$CHECK_CONSTRAINTS | Affected field name
CHECK | RDB$TRIGGERS | Check expression
PRIMARY KEY | RDB$INDEX_SEGMENTS | Index fields
UNIQUE | RDB$INDEX_SEGMENTS | Index fields
FOREIGN KEY | RDB$INDEX_SEGMENTS | Foreign Key index fields.
| RDB$REF_CONSTRAINTS | Name of referenced PRIMARY KEY o
| UNIQUE constraint.
The result of this query is used to populate a ListView with
* Constraint name
* Constraint type (CHECK, PRIMARY KEY, etc.)
* Table where constraint is defined
* Constraint field, for NOT NULL type
* Expression, for CHECK constraint
* Index name for PRIMARY KEY, UNIQUE and FOREIGN KEY
* Message to display (taken from dictionary file)
The code is in procedure ReadConstraints. This ListView serves both display and data storage purposes.
The next step is taken when the user selects an item from the ListView: complete information about the selected constraint is extracted and displayed in a read-only memo. For example, for a CHECK constraint it will show the complete text of the CHECK expression.
Some of this information is already in the ListView; the remaining data is gathered using two extra querys. The first one extracts the list of fields that make up an index, whose name is passed as a parameter:
SELECT RC.RDB$RELATION_NAME as Tabla,
S.RDB$FIELD_NAME as Campo
FROM RDB$RELATION_CONSTRAINTS RC
INNER JOIN RDB$INDEX_SEGMENTS S
ON RC.RDB$INDEX_NAME = S.RDB$INDEX_NAME
WHERE RC.RDB$INDEX_NAME = :Index
ORDER BY S.RDB$FIELD_POSITION
The other query gets information about a referenced master table in a Foreign Key constraint: index, table and fields.
SELECT RC.RDB$CONSTRAINT_NAME as NombreFK,
RC.RDB$CONST_NAME_UQ as ConstrRef,
RELC.RDB$RELATION_NAME as TablaRef,
RELC.RDB$INDEX_NAME as IndiceRef,
IND.RDB$FIELD_NAME as CampoRef
FROM RDB$REF_CONSTRAINTS RC
LEFT JOIN RDB$RELATION_CONSTRAINTS RELC
ON RC.RDB$CONST_NAME_UQ = RELC.RDB$CONSTRAINT_NAME
LEFT JOIN RDB$INDEX_SEGMENTS IND
ON RELC.RDB$INDEX_NAME = IND.RDB$INDEX_NAME
WHERE RC.RDB$CONSTRAINT_NAME = :FK
ORDER BY IND.RDB$FIELD_POSITION
The parameter FK holds the name of the Foreign Key constraint.
All this information is used in ListViews OnSelectItem event handler, assembling a descriptive text which is displayed in a memo.
The user can write a suitable message for the selected constraint in the memo provided; when another constraint is selected, that message will be stored in a column of the ListView.
Automatic message generation
============================
Another feature of this little application is the auto-generation of messages: certainly not too diferent than Interbases messages, but at least they could be written in your language!
The standard messages are defined as constants; the correct one is selected based on restriction type.
The code is pretty straightforward: procedure GenerateMessage writes a new message (if there was any, it is not overwritten) directly to one item of the ListView, whose index is passed as a parameter. This procedure is called from inside a loop when we want to generate messages for all constraints.
The rest of the code is just interface housekeeping.
Enjoy!