I keep coming up against the problem of interpreting Delphi
enumerated types into database field values and back again. Soon I
will have to translate my app into different languages, and the
problem will increase as this happens. So I am asking whether a new
datatype might be possible - called say "Enumerated".

Here is a sample scenario.

In Delphi I define

TOrderStatus = (osQuotation,osOrder,osDelivered,osInvoiced);
TOrderStatuses = array[TOrderStatus] of string;

In my language dependent Constants unit I define a constant as

Const
OrderStatuses:TOrderStatuses =
('Quotation','Order','Delivered','Invoiced');

So whenever I refer to it I can use

OrderStatuses[osQuotation]

rather than a literal string.

This removes language-dependancy from my Delphi code. But when I
come to save the order status to the database table I get problems.

If I save it as a string "Quotation", then whenever my SPs and
Triggers want to select on this field, they have to use a literal
string, and the metadata is then not language-independent.
If I save it as an integer, every time I print it or display it I
have to write code to convert from the integer to the string, and
every time I save it, I have to convert back from string to integer.

So I would like to be able to define the field in the table as

ORDER_STATUS ENUMERATED

There would be a system table ENUMERATED_TYPES containing enumerated
values, with three fields

SERIES_NAME VARCHAR(31)
SERIES_INDEX INTEGER,
SERIES_VALUE VARCHAR(20)

When saving, Firebird would take the saved string, look up the
ENUMERATED_TYPES table, and save the SERIES_INDEX value to the table.
When reading the table, same thing, vice versa.

Part of my system setup would be to check that the ENUMERATED_TYPES
table was populated with all necessary types and values. I might
even let the user change the standard names of some types to suit his
in-house systems.

I suppose this could be implemented by holding the field as an
integer and having an additional computed field (for reading) and
converting the computed field back to an integer using triggers (for
writing). But this means lots of extra Delphi code to make the
computed field writable etc.

The client would have to see the field as a VARCHAR or CHAR, so that
existing tools could use the new type.

Does this make sense as a new Firebird feature?

I once did a course on Navision, the accounting package with a
proprietary database, and there was something similar in that. It
was very useful.



Roger Vellacott
Passfield Data Systems Ltd