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
Interbase’s 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 we’ll go a
little further developing a simple application to construct that file, using
Interbase’s System Tables.
As for the
first part, we’ll put all the code in a separate unit and provides access to it
through global functions. The three global functions are as in Figure 1.
|
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. |
Figure 1:
functions in uErrorMessages.pas
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. We’ll 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
(we’ll 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 unit’s 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
Interbase’s Api Guide document.
|
Cluster ID (first element) |
Elements in cluster |
Meaning |
|
0 |
|
End of Status Vector. |
|
1 |
2 |
The next element is
Interbase’s Error Code. |
|
2 |
2 |
Next element is a
string parameter for replace in standard message. |
|
3 |
3 |
Next element is the
size in bytes of a variable-length string whose address is in cluster’s 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, with the parameters already
replaced, ready for display. |
Figure 2:
excerpt from Interbase’s Status Vector
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 don’t
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
don’t 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 can’t 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 TApplication’s 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 form’s uses
clause and we have to call
- LoadDict
before all start; tipically, main form’s 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.
It’s 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
- 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
- Add the following procedure to
main form’s 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
form’s uses clause.
That’s all.
Run the application, and break the constraint –it is a check constraint on
Salary field- by changing Leslie Johnson’s 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 constraint’s 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.
Figure 3
shows this new application in action.
Figure 3:
Dictionary Creator’ main form
In brief,
this application
- Extract 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
Database’s 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 table in Figure 4 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 RDB$REF_CONSTRAINTS |
Foreign Key index
fields. Name of referenced PRIMARY
KEY o UNIQUE constraint. |
Figure 4:
types of constraints and where to find related information
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
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 ListView’s 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 Interbase’s 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; complete source code is available here