At 10:53 AM 1/02/2006, you wrote:

>I just cannot get it to work running everyting from a single script
>within a TIB_script based application.

OK, let's just get right to the bottom of the problem. This is the
block that causes the problem:

* create main artist id field */
ALTER TABLE SONGS
ADD ARTIST1_ID INTEGER
DEFAULT 0
NOT NULL ;
UPDATE SONGS SET ARTIST1_ID = '0'; <--------------

/* create second artist id field */
ALTER TABLE SONGS
ADD ARTIST2_ID INTEGER
DEFAULT 0
NOT NULL;
UPDATE SONGS SET ARTIST2_ID = '0';

COMMIT;

The problem comes because you perform an update on a column that only
partially exists in the table; then, you make matters worse by
repeating the misdemeanour. :-)

Place a commit after the DDL and before any DML or any further DDL
involving the same table. Replace this whole block with

ALTER TABLE SONGS
ADD ARTIST1_ID INTEGER
DEFAULT 0
NOT NULL,
ADD ARTIST2_ID INTEGER
DEFAULT 0
NOT NULL;

COMMIT;

UPDATE SONGS SET
ARTIST1_ID = '0',
ARTIST2_ID = '0';

COMMIT;

DDL is a different kind of processing on the database side than
DML. Tools such as IBExpert and isql specifically isolate DDL
statements into their own transaction and autocommit them. (You can
optionally turn this AUTODDL off in isql and IBExpert probably has
something to do that, too. Then you need to keep your wits about you
with respect to timely commits of the DDL.)

In an IB_Script, you have a process running, that is executing a
number of statements and, possibly, involving more than one
transaction: each time IBO's process encounters a COMMIT statement,
it commits all work executed so far and starts a new transaction. If
you are mixing DDL and DML in a script (not a perfect idea, anyway)
then you must take care of committing the DDL statements before you
ever start applying DML to them. And, if you have multiple DDL on
the same table that can be in a single transaction, try to do them
all in a single statement, since COMMIT for a DDL statement does a
heck of a lot more stuff than you see on the surface.

Oh, and btw, that error message was the "telling" one, since for some
reason it always appears in that scrambled DDL scenario. It's
possible that it really is a blob conversion error, but related to
one of the internal blob structures that the engine reads or writes
in the process of creating, altering and analysing metadata.

Helen