Subject Re: [firebird-support] Baffling stored procedure error message. Any debugging tips?
Author Svein Erling Tysvaer
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.


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
> validation error for column PROJ_ID, value "*** null ***"
> At procedure 'ADD_A_FREIGHT_CHARGE'
> 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