Hi we have quite a mystifying problem. I'm not sure if it is with
firebird or the jdbc driver. We're using FB 1.5.5., java code and jdbc
2.1.6. Lengthy post coming up!

Our situation is this. We have a 'wordstore' table in which we store
words & phrases (for various uses) in a 'text' column (varchar(250) so
it can be indexed). We have an 'uptext' column which is an uppercased
version of the text, maintained by a trigger (on insert/update). (We
have this uppercased text because there are 100's of thousands of word
combos and a lot of searching, and we found this is one optimisation
that was necessary.) We also truncate this uptext to 81 char max. This
was all working fine. Here's the trigger:

as begin NEW.UPTEXT = UPPER(CAST(SUBSTRING(NEW.TEXT FROM 1 FOR 81) AS
VARCHAR(81) CHARACTER SET ISO8859_1) COLLATE EN_UK); end

Trouble began when we had to start doing some pretty big cleanups on the
uptext column because it may contain a lot of rubbish in it
(punctuation, etc) and as this is what we search against we need to
modify this but not the original text. This is done by java code... so
we needed to modify the trigger (so it only writes to uptext column if
it's null). We also needed to add a 'datemodified' column to this
wordstore.
So we run a 'drop trigger TRG_WORDSTORE_UPTEXT' then create the new
trigger, which is:

as begin NEW.UPTEXT = (CASE WHEN NEW.UPTEXT IS NULL THEN
UPPER(CAST(SUBSTRING(NEW.TEXT FROM 1 FOR 81) AS VARCHAR(81) CHARACTER
SET ISO8859_1) COLLATE EN_UK) ELSE NEW.UPTEXT END); end

The crazy thing is what happens next. I have a PreparedStatement that is
used to reprocess the non-null uptext values, i.e like this: "update
wordstore set uptext=?, datemodified=CURRENT_TIMESTAMP where pk = ?".
For every single update, the timestamp goes into the 'text' column (not
even part of the statement!) and the datemodified column remains null!
I found through trial & error that if - after creating the new trigger -
I disconnect from the database then reconnect THEN create the
PreparedStatement and start processing, everything works perfectly (so I
know the SQL is all right).
I've also found that if I create a new database (via a data pump) and
apply all constraints and the new trigger (without the old one first,
then dropping it), then the PreparedStatement also works correctly in
this case.

So can someone please tell me what is going wrong here.
- When dropping a trigger and creating a new one, do I need to
disconnect in between?
- Is this a bug in Firebird or the JDBC driver? Is the JDBC driver
getting confused after the trigger is dropped and a new one created?

It's really important for us to know what is going on here. We have a
large number of clients and we need to understand the implications of
this behaviour. Any ideas?

Thanks, Phil