--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> Hi Mitch!
>
> Since you haven't provided much information beside the error message,
> I'll start with a wild guess:
>
> Your stored procedure contains a SELECT ... INTO ... and one of the
> returned fields you somehow try to put in the PROJ_ID column? If so, my
> guess is that the select doesn't return any row at all and PROJ_ID
> becomes null. Modify your procedure to use a fixed number (as

opposed to

> a variable) and see if the problem goes away. If it doesn't, I'm
> definitely wrong.
>
> What do you mean by 'execute the procedure in IbExpert'? Are you

talking

> about executing the procedure in the 'Stored procedure debugger'? If

so,

> I doubt it is running in Firebird at all, I think IBExpert tries to
> emulate Firebird (I doubt it is possible to really debug a procedure
> within Firebird - well, at least for those of us who don't understand
> BLR), and that is not the same as running the procedure. Try running

the

> procedure and not this debugger when the database is in the exact same
> state as it is before running through your program (it should be
> possible within IBExpert, and if not try isql or similar) and see if it
> gets an error or not.
>
> Also, I don't understand what you mean by 'the first time' - is it the
> first time after you start the program, the first time it starts a new
> project or the first time you put some data into some table?
>
> If it isn't an empty value in the PROJ_ID column, tell us at least

which

> version of IBO you are using, show us parts of the stored procedure
> (simplified as much as possible while retaining the error), tell us if
> there are any triggers involved and keys/constraints on the tables

involved.

>
> Set
>
> mlq97 wrote:
> > I'm using Delphi 2006, TIBO data access components, Firebird 2.0.
> >
> > I have a button on a Delphi form which executes a stored procedure
> > called "Add_A_Freight_Charge" which receives an integer parameter
> > "V_DOC_ID". (I have determined that in all cases the parameter is
> > correctly passed to the SP).
> >
> > The first time I execute the SP by pressing the button, I get an error
> > message:
> >
> > ---------------------------
> > Debugger Exception Notification
> > ---------------------------
> > Project Inigo.exe raised exception class EIBO_ISCError with message
> > 'ISC ERROR CODE:335544347
> >
> > ISC ERROR MESSAGE:
> > validation error for column PROJ_ID, value "*** null ***"
> > At procedure 'ADD_A_FREIGHT_CHARGE'
> >
> > STATEMENT:
> > TIBOInternalDataset:
> >

"<TApplication>.frmDocument.sprocAddFreightCharge.IBOqrsprocAddFreightCharge'.

> > ---------------------------
> > Break Continue Help
> > ---------------------------
> >
> >
> > The second and subsequent times that I execute the SP by pressing the
> > button, it works OK.
> >
> > When I manually provide the parameter value and execute the procedure
> > in IbExpert it works every time. This is even the case when running it
> > while my app form is open in the same state as when an error occurs.
> >
> > I can't understand why the same SP should behave differently like
> > this. Does the error message imply that the error is within the SP or
> > caused by some external condition? Does anyone have any clues as to
> > what might be going on?
> >
> > Many thanks,
> > Mitch
>

Thanks for your assistance Svein,

I am using TIBO 4.6.B

I am test executing the procedure in the IBExpert stored procedure
editor, not in the debugger.

Here is my stored procedure which I have simplified. The first select
is supposed to find the value for Proj_ID. The DOC row definitely
contains the correct Proj_ID. But when I try to use it in the
subsequent Insert the error occurs. If I substitute a fixed number
instead of :PROJ_ID in the Insert, it works OK.

So to recap:

"The first time" means after restarting the program and opening the form:

1. Press button on form the first time executes SP with error.
2. Press button on form a second time executes SP without error.
3. Executing SP manually with IB_SQL tool or IBExpert, never an error.
4. Press button on form first time executes SP (having hard coded
value in SP for Proj_ID) without error.

I have a couple of insert triggers on the ITEM table, but none require
Proj_ID or obviously would cause a problem.


CREATE PROCEDURE ADD_A_FREIGHT_CHARGE_TEST (V_DOC_ID INTEGER)
RETURNS (NEW_ITEM_ID INTEGER)
AS
DECLARE VARIABLE PROJ_ID INTEGER;
DECLARE VARIABLE PARTY_ID INTEGER;
BEGIN

-- gets values for Proj_ID & Party_ID
SELECT DOC.PROJ_ID, DOC.PARTY_ID FROM DOC WHERE DOC.DOC_ID = :V_DOC_ID
/* substituting # 1186 for :V_DOC_ID does not solve the problem*/
INTO :PROJ_ID, :PARTY_ID;

INSERT INTO ITEM (PROJ_ID, ITEM_ORDER, ITEM_DESCR_CLIENT, PARTY_ID,
ITEM_SPECORACCESSORY)
VALUES
(
:PROJ_ID, -- ***ERROR IS HERE, works ok when using numeric value
0,
'Freight charge on supplier invoice - Doc ID #' || :V_DOC_ID,
--:ITEM_DESCR_CLIENT,
:PARTY_ID,
'F'
)

END


Here are the constraints on Item:



/* Check constraints definition */

ALTER TABLE ITEM ADD CONSTRAINT ITEM_NOT_USED_IN_SELF check
(ITEM_USED_IN_ITEM_ID <> ITEM_ID);


/******************************************************************************/
/**** Primary Keys
****/
/******************************************************************************/

ALTER TABLE ITEM ADD CONSTRAINT PK_ITEM PRIMARY KEY (ITEM_ID);


/******************************************************************************/
/**** Foreign Keys
****/
/******************************************************************************/

ALTER TABLE ITEM ADD CONSTRAINT FK_ITEM_1 FOREIGN KEY (PARTY_ID)
REFERENCES PARTY (PARTY_ID);
ALTER TABLE ITEM ADD CONSTRAINT FK_ITEM_2 FOREIGN KEY (PROJ_ID)
REFERENCES PROJ (PROJ_ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE ITEM ADD CONSTRAINT FK_ITEM_3 FOREIGN KEY (ITEMTYPE_ID)
REFERENCES ITEMTYPE (ITEMTYPE_ID);
ALTER TABLE ITEM ADD CONSTRAINT FK_ITEM_5 FOREIGN KEY (LOC_ID)
REFERENCES LOC (LOC_ID);
ALTER TABLE ITEM ADD CONSTRAINT FK_ITEM_6 FOREIGN KEY
(ITEM_USED_IN_ITEM_ID) REFERENCES ITEM (ITEM_ID);
ALTER TABLE ITEM ADD CONSTRAINT FK_ITEM_7 FOREIGN KEY (PDMUPS_ID)
REFERENCES PROJDEFMARKUPS (PDMUPS_ID) ON DELETE CASCADE ON UPDATE CASCADE;