I apologise, but have allready asked this question on the IBO usergroup, but no response, so I'm hoping somebody can help me here.

I have a table with a ANIMALID Smallint Not Null
I erroneously assigned it to smallint, and it should be integer.

I run the following script against the populated db, in IB/FB development studio, and the database is updated without problems, no errors, and the data is intact.

If I add the same script to IB_Script, and run from my application against the same populated database, then I get the following error:

First chance exception at $7729FBAE. Exception class EIB_ISCError with message 'ISC ERROR CODE:335544347

ISC ERROR MESSAGE:
validation error for column ANIMALID, value "*** null ***"
'. Process SupaStud.exe (6780)

The script follows below:

ALTER TABLE ANIMALDISEASEINJURY DROP CONSTRAINT FK_ANIMALDISEASEINJURY_1;

ALTER TABLE ANIMALDOSING DROP CONSTRAINT FK_ANIMALDOSING_1;

ALTER TABLE ANIMALPERFORMANCE DROP CONSTRAINT FK_ANIMALPERFORMANCE_1;

ALTER TABLE ANIMALPHOTO DROP CONSTRAINT FK_ANIMALPHOTO_1;

ALTER TABLE ANIMALPROGRESS DROP CONSTRAINT FK_ANIMALPROGRESS_1;

ALTER TABLE ANIMALSIRE DROP CONSTRAINT FK_ANIMALSIRE_1;

ALTER TABLE CANCEL DROP CONSTRAINT FK_CANCEL_1;

ALTER TABLE ANIMAL DROP CONSTRAINT UNQ_ANIMAL_1;

ALTER TABLE ANIMAL DROP CONSTRAINT ANIMAL_PK;



ALTER TABLE ANIMAL ADD TEMP$$ANIMALID Integer NOT NULL;

UPDATE ANIMAL SET TEMP$$ANIMALID = ANIMALID;

ALTER TABLE ANIMAL DROP ANIMALID;

ALTER TABLE ANIMAL ALTER COLUMN TEMP$$ANIMALID TO ANIMALID;





ALTER TABLE ANIMAL ADD CONSTRAINT UNQ_ANIMAL_1 UNIQUE (ANIMALID);

ALTER TABLE ANIMAL ADD CONSTRAINT ANIMAL_PK PRIMARY KEY (ANIMALIDENTIFICATION,ANIMALID);

ALTER TABLE ANIMALDISEASEINJURY ADD CONSTRAINT FK_ANIMALDISEASEINJURY_1 FOREIGN KEY (DISEASEINJURYANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

ALTER TABLE ANIMALDOSING ADD CONSTRAINT FK_ANIMALDOSING_1 FOREIGN KEY (DOSINGANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

ALTER TABLE ANIMALPERFORMANCE ADD CONSTRAINT FK_ANIMALPERFORMANCE_1 FOREIGN KEY (PERFORMANCEANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

ALTER TABLE ANIMALPHOTO ADD CONSTRAINT FK_ANIMALPHOTO_1 FOREIGN KEY (ANIMALPHOTOANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

ALTER TABLE ANIMALPROGRESS ADD CONSTRAINT FK_ANIMALPROGRESS_1 FOREIGN KEY (PROGRESSANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

ALTER TABLE ANIMALSIRE ADD CONSTRAINT FK_ANIMALSIRE_1 FOREIGN KEY (SIREANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

ALTER TABLE CANCEL ADD CONSTRAINT FK_CANCEL_1 FOREIGN KEY (CANCELANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;


I modified it as follows, also with no success:
ALTER TABLE ANIMALDISEASEINJURY DROP CONSTRAINT FK_ANIMALDISEASEINJURY_1;

ALTER TABLE ANIMALDOSING DROP CONSTRAINT FK_ANIMALDOSING_1;

ALTER TABLE ANIMALPERFORMANCE DROP CONSTRAINT FK_ANIMALPERFORMANCE_1;

ALTER TABLE ANIMALPHOTO DROP CONSTRAINT FK_ANIMALPHOTO_1;

ALTER TABLE ANIMALPROGRESS DROP CONSTRAINT FK_ANIMALPROGRESS_1;

ALTER TABLE ANIMALSIRE DROP CONSTRAINT FK_ANIMALSIRE_1;

ALTER TABLE CANCEL DROP CONSTRAINT FK_CANCEL_1;

ALTER TABLE ANIMAL DROP CONSTRAINT UNQ_ANIMAL_1;

ALTER TABLE ANIMAL DROP CONSTRAINT ANIMAL_PK;

　

ALTER TABLE ANIMAL ADD TEMP$$ANIMALID INTEGER NOT NULL;

UPDATE ANIMAL SET TEMP$$ANIMALID = CAST(ANIMALID AS INTEGER) WHERE ANIMALID IS NOT NULL;

ALTER TABLE ANIMAL DROP ANIMALID;

ALTER TABLE ANIMAL ADD ANIMALID INTEGER NOT NULL;

UPDATE ANIMAL SET ANIMALID = TEMP$$ANIMALID WHERE TEMP$$ANIMALID IS NOT NULL;

ALTER TABLE ANIMAL ALTER ANIMALID POSITION 3;

ALTER TABLE ANIMAL DROP TEMP$$ANIMALID;

　

ALTER TABLE ANIMAL ADD CONSTRAINT UNQ_ANIMAL_1 UNIQUE (ANIMALID);

ALTER TABLE ANIMAL ADD CONSTRAINT ANIMAL_PK PRIMARY KEY (ANIMALIDENTIFICATION,ANIMALID);

ALTER TABLE ANIMALDISEASEINJURY ADD CONSTRAINT FK_ANIMALDISEASEINJURY_1 FOREIGN KEY (DISEASEINJURYANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

ALTER TABLE ANIMALDOSING ADD CONSTRAINT FK_ANIMALDOSING_1 FOREIGN KEY (DOSINGANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

ALTER TABLE ANIMALPERFORMANCE ADD CONSTRAINT FK_ANIMALPERFORMANCE_1 FOREIGN KEY (PERFORMANCEANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

ALTER TABLE ANIMALPHOTO ADD CONSTRAINT FK_ANIMALPHOTO_1 FOREIGN KEY (ANIMALPHOTOANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

ALTER TABLE ANIMALPROGRESS ADD CONSTRAINT FK_ANIMALPROGRESS_1 FOREIGN KEY (PROGRESSANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

ALTER TABLE ANIMALSIRE ADD CONSTRAINT FK_ANIMALSIRE_1 FOREIGN KEY (SIREANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

ALTER TABLE CANCEL ADD CONSTRAINT FK_CANCEL_1 FOREIGN KEY (CANCELANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

commit work;

I connect with the following code:
IB_Script17A.SQL.Insert(0,'SET AUTODDL on;');
IB_Script17A.SQL.Insert(1,'SET SQL DIALECT 3;');
IB_Script17A.SQL.Insert(2,'CONNECT ''' + FormSupaStud.IB_Connection1.Path + ''' USER ''SYSDBA'' PASSWORD ''' + FormSupaStud.IB_Connection1.Password + ''';');

IB_Script17A.Execute; {update table structures}
IB_Script17A.Free;

Why it forks in Interbase/Firebird development studio, but allways fails against the same DB in delphi IBO, I cannot tell.
And yes, there are no records with null values in the ANIMAL table!

Regards

Adrian Wreyford

[Non-text portions of this message have been removed]