Hi Paul,

At 06:25 AM 5/02/2003 +0000, you wrote:

>I have a small problem - we are currently using IB server version 6
>but I decided to test out Firebird recently and discovered that the
>word "BREAK" is a keyword on that engine and will cause problems.
>Unfortunately we have a table with a field named BREAK which works on
>Borland's engine, but not Firebird.
>
>To prepare a query with this word in the SELECT clause I need to
>double-quote it, eg
> SELECT FIELD1, FIELD2, ... "BREAK", FIELDN, ....
>
>and attempting to post the record will fail because of the internals
>of IBO will use the SQLName property to generate it's DSQL, which
>does not have the double quotes on it.

Actually, this isn't an IBO problem, it's a metadata problem that is very
slightly tricky to solve. You will actually need to do an alteration to
the structure of this table, by first adding the double-quoted column name
and then copying the data across from the column with the invalid
name. Ideally, you need to do this in IB 6, which doesn't have the keyword
BREAK, because any reference to a column named BREAK in Firebird is going
to choke.

i.e.
ALTER TABLE ATABLE
ADD "BREAK" [type stuff];
commit;
update ATABLE
SET "BREAK" = Break;
COMMIT;
ALTER TABLE ATABLE
DROP Break;

If you can't get the database back onto an IB 6 server to do this then
(working on a copy of the database) you could try changing the metadata
directly from a tool such as isql or IB_SQL.

UPDATE RDB$RELATION_FIELDS
SET RDB$FIELD_NAME='"' ||'BREAK'||'"'
WHERE RDB$FIELD_NAME='BREAK';

This won't work either, if there are any dependencies on this column, i.e.
it is involved in any index, trigger, SP, RI constraint, etc., so you will
be faced with disabling/unwinding the dependencies, or reloading the DB
back onto an IB 6 server to do it. (btw, you need the IB 6 client too).

Because quoted identifiers are case-sensitive, you will also need to go
through your application code and fix any case-insensitive usages of this
identifier, so that it is always (a) double-quoted and (b) correct for case.

When the metadata is right, IBO handles quoted identifiers correctly, i.e.
the SQLName will be '"BREAK"', NOT 'BREAK' and FieldByName(..) will
recognise '"BREAK"' but not 'BREAK'.

(Actually, you do get a little bit of leeway. 'BREAK' will work for the
quoted identifier *if* it is created as "BREAK", i.e. all uppercase, and
not something like "Break" or "break").

>So all I want to know is there a quick way to get this working. Maybe
>there is a string property in IB_Connection I can use
>(ColumnAttributes?) to override individual field names? Something
>like:
> MYTABLE.BREAK=MYTABLE."BREAK"

IBO works directly with metadata. If the references don't match the
metadata, you have to fix it.

>Some more keywords may start appearing in the future

yes, look out for several new keywords in Fb 1.5.

>so I'd rather
>have this kind of easy override mechanism rather than renaming
>database fields (and updating all the source code).

This is a migration task. IB 6 and Firebird are not the same database any
more. The recommended approach is to read the releasenotes before you
begin the migration, so that you can prepare the database and your app code
in advance. The new keywords are all detailed there.

Helen