kgdonn wrote:

> Disclaimer: Yes, I know this table design is terribly whacked but
> it's what I have to make work. IBO has so far been pretty amazing at
> accommodating all the bizarre twists I've thrown it, and I've almost
> got this bear ported. I can see the end...
>
> Now, I've got a table that does not have a primary key, but it does
> have a unique compound index, although some fields in the unique index
> may be NULL. If none of the fields are NULL, TIBOTable has no problem
> locating the record for update, but if any of the index fields are
> NULL, it throws an exception saying it can't locate the record. Is
> there a way to tell TIBOTable that these key fields may be NULL?

First check the FieldsAttibute BlankIsNull (or something to that
extent). If a field is a VARCHAR and a '' ("") is always NULL it *might*
help. But I don't think so.

*If* you are allowed to do metadata changes - add a pk to the table on a
new field and assign it a generator value (by trigger). IMHO this is the
best solution. The user and other apps does not have to see this new PK.
It can remain you "secret". Don't forget to tell IBO about your generator.

Another one (also req. metadata changes) if you are not allowed to add a
PK is to add another field that is a calculated compound of the fields
in you compound key (UNIQUE_COL) with another unique index if necessary.

In a BEFORE trigger

new.UNIQUE_COL = COALESCE(KEY_F1, '-') || '@' || COALESCE(KEY_F2, '-');

where _ and @ are chars guaranteed not to appear in KEY_Fn fields.

Use the new field above for your KeyLinks.

Since you have to assign new.* in AFTER triggers (the KEY_F* may change)
you will have problems inserting records and "getting back to" newly
inserted records. See suggestion 1 (better).

I don't think that you will be able to get a KeyLink compund with NULLs
working in the long run. This is vaguely from experience.

Kill me if I'm wrong

HTH = Hope this helps,

/Dany