Helen, you're a star!

Everything you say makes perfect sense now looking back at
my earlier findings. Should have been able to crack this one
myself, but then again I'm still an SQL rookie... ;-)

Thanks,

Lucas

--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:
>
> 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
>