Would you please turn on SQL trace and identify which statement it is having
the problem with?
Would you please send a DDL dump for the entire database (sufficient at
least so the script can be tested) so that I can have a duplication of this
problem to work with?

Absent these two conditions being present I don't know if I can help you.
You can send them to me privately.

Regards,
Jason Wharton

----- Original Message -----
From: "Adrian Wreyford" <wreymed@...>
To: <IBObjects@yahoogroups.com>
Sent: Friday, June 19, 2009 1:28 PM
Subject: [IBO] Problem with IB_script .. please any help


The following script, when run against my DB, in IB/FB development studio,
runs without any errors, and the matadata is successfully updated.
I cut and past the script into IB_Script, and then execute the script at
runtime, to update my clients databases to the newer structure.
D2007, IBO 4.8.7

It fails and retuens the 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)

There are no Null values in the ANIMALID field as it was incorectly set to
smallint, notnull, and we ran out of ID's!
So I just want to set to INTEGER .. NOT NULL.

Why this runs every time in IB/FB Development studio, but not in my code I
cannot understand.

For years now I have been creating and testing the scripts in IB/FBds, and
cutting and pasting into IB_Scripts. Now all of a sudden I get this error.

I've been unable to resolve for 1 week now.

Any help will be appreciated

pasted below the script text (PS I use a standard connection setup that is
inserted before the begining of the scripts, not shown here).



SET TERM ^ ;

ALTER PROCEDURE PROC_GETNEWID (TABLENAME Varchar(31))

returns (NEW_ID Integer)

AS

BEGIN EXIT; END^

SET TERM ; ^

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;

CREATE GENERATOR GEN_ANIMALPURCHASEID;

CREATE GENERATOR GEN_ANIMALSELLID;

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;

CREATE TABLE ANIMALPURCHASE (

ANIMALPURCHASEID Integer NOT NULL,

ANIMALPURCHASEANIMALID Integer NOT NULL,

ANIMALPURCHASEDATE Date NOT NULL,

ANIMALPURCHASEAMOUNT Decimal(18,2) NOT NULL,

ANIMALPURCHASEFROMID Integer NOT NULL,

ANIMALPURCHASECOMMENT Varchar(100),

ANIMALPURCHASEWEIGHT Decimal(18,2),

CONSTRAINT PK_ANIMALPURCHASE PRIMARY KEY (ANIMALPURCHASEID)

);

　

　

CREATE TABLE ANIMALSELL (

ANIMALSELLID Integer NOT NULL,

ANIMALSELLANIMALID Integer NOT NULL,

ANIMALSELLDATE Date NOT NULL,

ANIMALSELLTOID Integer NOT NULL,

ANIMALSELLAMOUNT Decimal(18,2) NOT NULL,

ANIMALSELLTRANSFER Char(1) DEFAULT 'F' NOT NULL, /*If true, then Transfered,
if false then Cancelled*/

ANIMALSELLWEIGHT Decimal(18,2),

ANIMALSELLSLAUGHTERED Char(1),

ANIMALSELLCARCASSWEIGHT Decimal(18,2),

ANIMALSELLCOMMENT Varchar(100),

ANIMALSELLCARCASSFAT Integer,

ANIMALSELLCARCASSCONF Integer, /*Carcass Conformation*/

ANIMALSELLCARCASSDAMAGE Integer,

ANIMALSELLCARCASSGRADE Varchar(2),

CONSTRAINT PK_ANIMALSELL PRIMARY KEY (ANIMALSELLID)

);

　

　

CREATE TABLE CANCELREASONS (

CANCELREASONID Integer NOT NULL,

CANCELREASONE Varchar(50) NOT NULL,

CANCELREASONA Varchar(50) NOT NULL,

CANCELREASONHEADINGE Varchar(30),

CANCELREASONHEADINGA Varchar(30),

CONSTRAINT PK_CANCELREASONS PRIMARY KEY (CANCELREASONID)

);

　

　

CREATE TABLE TESTFUNCTIONAL (

TESTFUNCTANIMALID Integer NOT NULL,

TESTFUNCTBEEFTESTCODE Varchar(22) NOT NULL,

TESTFUNCTHAIRSTRAIGHTNESS Integer,

TESTFUNCTTEMPERAMENT Integer,

TESTFUNCTSKINPIGMENTATION Integer,

TESTFUNCTFACESTRAIGHTNESS Integer,

TESTFUNCTLOWERJAWLENGTH Integer,

TESTFUNCTGIRTHFULLNESS Integer,

TESTFUNCTTOPLINEBACK Integer,

TESTFUNCTRUMPANGLE Integer,

TESTFUNCTMUSCULARITY Integer,

CONSTRAINT PK_TESTFUNCTIONAL PRIMARY KEY
(TESTFUNCTANIMALID,TESTFUNCTBEEFTESTCODE)

);

　

　

　

　

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 ANIMALPURCHASE ADD CONSTRAINT FK_ANIMALPURCHASE_1 FOREIGN KEY
(ANIMALPURCHASEANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

ALTER TABLE ANIMALPURCHASE ADD CONSTRAINT FK_ANIMALPURCHASE_2 FOREIGN KEY
(ANIMALPURCHASEFROMID) REFERENCES CONTACTS(CONTACTID) ON DELETE CASCADE;

ALTER TABLE ANIMALSELL ADD CONSTRAINT FK_ANIMALSELL_1 FOREIGN KEY
(ANIMALSELLANIMALID) REFERENCES ANIMAL(ANIMALID) ON DELETE CASCADE;

ALTER TABLE ANIMALSELL ADD CONSTRAINT FK_ANIMALSELL_2 FOREIGN KEY
(ANIMALSELLTOID) REFERENCES CONTACTS(CONTACTID) 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;

SET TERM ^ ;

ALTER PROCEDURE PROC_GETNEWID (TABLENAME Varchar(31))

returns (NEW_ID Integer)

AS

declare variable GEN_NAME Varchar(33);

begin

GEN_NAME = 'GEN_' || (TABLENAME);

if (GEN_NAME = 'GEN_ANIMAL') then

BEGIN

NEW_ID=GEN_ID(GEN_ANIMALID,1);

EXIT;

END


if (GEN_NAME = 'GEN_PROGRESS') then

BEGIN

NEW_ID=GEN_ID(GEN_PROGRESSID,1);

EXIT;

END


if (GEN_NAME = 'GEN_PERFORMANCE') then

BEGIN

NEW_ID=GEN_ID(GEN_PERFORMANCEID,1);

EXIT;

END


if (GEN_NAME = 'GEN_DOSING') then

BEGIN

NEW_ID=GEN_ID(GEN_DOSINGID,1);

EXIT;

END


if (GEN_NAME = 'GEN_RAINFALL') then

BEGIN

NEW_ID=GEN_ID(GEN_RAINFALLID,1);

EXIT;

END

if (GEN_NAME = 'GEN_SSCOUNTER') then

BEGIN

NEW_ID=GEN_ID(GEN_SSCOUNTER,1);

EXIT;

END


if (GEN_NAME = 'GEN_ANIMALEVENTS') then

BEGIN

NEW_ID=GEN_ID(GEN_ANIMALEVENTID,1);

EXIT;

END

if (GEN_NAME = 'GEN_LOCATION') then

BEGIN

NEW_ID=GEN_ID(GEN_LOCATIONID,1);

EXIT;

END

if (GEN_NAME = 'GEN_RAINFALLZONES') then

BEGIN

NEW_ID=GEN_ID(GEN_RAINFALLZONEID,1);

EXIT;

END


if (GEN_NAME = 'GEN_AGEOFDAMADJ') then

BEGIN

NEW_ID=GEN_ID(GEN_AODID,1);

EXIT;

END


if (GEN_NAME = 'GEN_AI') then

BEGIN

NEW_ID=GEN_ID(GEN_AIID,1);

EXIT;

END


if (GEN_NAME = 'GEN_ET') then

BEGIN

NEW_ID=GEN_ID(GEN_ETID,1);

EXIT;

END


if (GEN_NAME = 'GEN_NAT') then

BEGIN

NEW_ID=GEN_ID(GEN_NATID,1);

EXIT;

END


if (GEN_NAME = 'GEN_EMBRYO') then

BEGIN

NEW_ID=GEN_ID(GEN_EMBRYOID,1);

EXIT;

END


if (GEN_NAME = 'GEN_SEMEN') then

BEGIN

NEW_ID=GEN_ID(GEN_SEMENID,1);

EXIT;

END


if (GEN_NAME = 'GEN_PALPATION') then

BEGIN

NEW_ID=GEN_ID(GEN_PALPATIONID,1);

EXIT;

END


if (GEN_NAME = 'GEN_PERFSSCOUNTER') then

BEGIN

NEW_ID=GEN_ID(GEN_PERFSSCOUNTER,1);

EXIT;

END


if (GEN_NAME = 'GEN_CONTACTS') then

BEGIN

NEW_ID=GEN_ID(GEN_CONTACTID,1);

EXIT;

END


if (GEN_NAME = 'GEN_DRUGS') then

BEGIN

NEW_ID=GEN_ID(GEN_DRUGID,1);

EXIT;

END


if (GEN_NAME = 'GEN_ANIMALPHOTO') then

BEGIN

NEW_ID=GEN_ID(GEN_ANIMALPHOTOID,1);

EXIT;

END

if (GEN_NAME = 'GEN_PLANNER') then

BEGIN

NEW_ID=GEN_ID(GEN_PLANNERID,1);

EXIT;

END


if (GEN_NAME = 'GEN_EXPSSCOUNTER') then

BEGIN

NEW_ID=GEN_ID(GEN_EXPSSCOUNTER,1);

EXIT;

END


if (GEN_NAME = 'GEN_MANAGEMENTGROUP') then

BEGIN

NEW_ID=GEN_ID(GEN_MANAGEMENTGROUPID,1);

EXIT;

END


if (GEN_NAME = 'GEN_CATALOGUE') then

BEGIN

NEW_ID=GEN_ID(GEN_CATALOGUEID,1);

EXIT;

END


if (GEN_NAME = 'GEN_CATALOGUEENTRIES') then

BEGIN

NEW_ID=GEN_ID(GEN_CATENTID,1);

EXIT;

END


if (GEN_NAME = 'GEN_ANIMALDISEASEINJURY') then

BEGIN

NEW_ID=GEN_ID(GEN_ANIMALDISEASEINJURYID,1);

EXIT;

END


if (GEN_NAME = 'GEN_DISEASEINJURY') then

BEGIN

NEW_ID=GEN_ID(GEN_DISEASEINJURYID,1);

EXIT;

END


if (GEN_NAME = 'GEN_ANIMALPURCHASE') then

BEGIN

NEW_ID=GEN_ID(GEN_ANIMALPURCHASEID,1);

EXIT;

END


if (GEN_NAME = 'GEN_ANIMALSELL') then

BEGIN

NEW_ID=GEN_ID(GEN_ANIMALSELLID,1);

EXIT;

END


SUSPEND;

end^

SET TERM ; ^

commit work;

COMMENT ON COLUMN ANIMAL.ANIMALID IS '';

COMMENT ON COLUMN ANIMALSELL.ANIMALSELLTRANSFER IS 'If true, then
Transfered, if false then Cancelled';

COMMENT ON COLUMN ANIMALSELL.ANIMALSELLCARCASSCONF IS 'Carcass
Conformation';

SET GENERATOR GEN_DISEASEINJURYID TO 1000;