A bit of a weird problem that has kept me busy several hours.

I am trying to write an application to upgrade an existing database
that currently contains a table with two similar varchar(80) fields
that have to be specified each time the user enter new data. I'd like
to create a master lookup table for these two fields, so I have
developed a script that replaces the varchar fields with two integer
fields. The numbers stored in these integer fields are of course
unique id's for the items in the master (lookup) table.

The script looks like this:

==== SCRIPT START ====

CREATE GENERATOR GEN_ARTISTS_ID;
SET GENERATOR gen_artists_id TO 0;

CREATE TABLE ARTISTS (
ID INTEGER NOT NULL,
NAME VARCHAR(120) CHARACTER SET ISO8859_1 DEFAULT '-',
NAME_UC VARCHAR(120) CHARACTER SET ISO8859_1 DEFAULT '-',
GROUP_ID INTEGER DEFAULT 0 NOT NULL,
GENDER INTEGER DEFAULT 0 NOT NULL,
INFO BLOB SUB_TYPE 1 SEGMENT SIZE 80
);

COMMIT;

/* insert a default record into artists lookup table */
insert into artists (id, name, name_uc) Values (0,'','');
COMMIT;

ALTER TABLE ARTISTS ADD CONSTRAINT FK_ARTISTS PRIMARY KEY (ID);

CREATE INDEX IDX_ARTISTS_NAMEUC ON ARTISTS (NAME_UC);

COMMIT;

/******************************************************************************/
/*** fill artists table with names from songs table ***/
/******************************************************************************/

create view all_artists (a_name) as
select distinct ARTIST1 from songs where (ARTIST1 > '')
union
select distinct ARTIST2 from songs where (ARTIST2 > '');

COMMIT;

insert into artists (name) select a_name from all_artists;

COMMIT;

/*note: at this point the new artists table contains all unique
artists name from the original songs table */

* 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;

/* set artist index for primary artist field by looking up name id's
in master artist table*/
update music_items set artist1_id =
(select artists.id from artists where (artists.name =
music_items.artist1))
where (music_items.artist1 > '');

==== SCRIPT END ====


It's this last statement that fails when I run it via a TIB_Script
component (either in an application or directly in delphi).
If I remove the last statement, the script runs fine and all data is
converted correctly. If I then run that last statement directly via
the editor in a TIB_Connection component it executes ok and updates >
5000 records as expected.


Additional info:

* IBExpert runs the entire script without problems
* I use a TIB_Connection, TIB_Transaction and a TIB_Script component
* Transaction isolation is comitted
* there are no strings called BLOB in any of the artists fields
* there are no BLOB fields involved in any update statements
* the application uses an updated version of IB_Session511 and loads
FBClient.dll
* Firebird server is 1.5.3.4854
* Using an IB_Monitor component tells me the stament itself if
prepared and started ok.
* If I split the script and run the last statement in a second script
run it works ok.


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

Any ideas are welcome..

Lucas