Maik,

Were you the author of this code?

At 11:57 AM 9/10/2004 +0000, you wrote:

>-------- Original Message --------
>Subject: [IBO] SQL Error code 0 or 501 (09-Okt-2004 13:30)
>From: mw@...
>To: mw@...
>
> >
> > Hi,
> >
> > currently I'm facing some trouble with stored procedures.
> > On a heavy load system. I'm getting some errors wich are
> > I cannot explain.
> >
> > My System:
> >
> > Firebird 1.5.1
> > Windows XP P/2003 Server
> > Delphi 7E
> > IBO 4.3.Aa
> >
> > Tools:
> >
> > MemProof 0.9.4.8
> >
> > 1) sometimes I get an error without any
> > ISC or SQL Errornumber like:
> >
> > 2004 Oct 07 6:52:56 PM:WPS::ERROR:db error: SQL:0 ISC:0
> > 2004 Oct 07 6:52:56 PM:WPS::ISC--> 0
> > 2004 Oct 07 6:52:56 PM:WPS::ISC--> 0
> > 2004 Oct 07 6:52:56 PM:WPS::SQL--> delete from ACTIVE_QUEUE_TAB
> > where ACTIVE_QUEUE_ID = ? /* id */
> >
> > 2004 Oct 07 6:52:56 PM:WPS:::<DeleteActiveQueueEntry> failed
> >
> > What's this ???

WPS == Wobe P?? S??

You seem to have a subsystem there of some sort that's calling a stored
procedure - is it perhaps a replication system? This looks like some kind
of a log.

> >
> > 2) MemProof shows a lot of errors (see attachment).
> > The calling code for the procedure in the Screenshot is:
> >
> > function TAppCtrl.GetRootObject: integer;
> > var
> > sproc: TIB_StoredProc;
> > {$IFDEF DEBUG_VER}
> > c, n1, n2: TLargeInteger;
> > {$ENDIF}
> > begin
> > {$IFDEF DEBUG_VER}
> > QueryPerformanceFrequency(c);
> > QueryPerformanceCounter(n1);
> > {$ENDIF}
> > sproc := TIB_StoredProc.Create(nil);
> > sproc.IB_Connection := _ProdBase;
> > sproc.IB_Transaction := _ScriptTransaction;
> > sproc.OnError := _OnIBError;
> > sproc.Name := 'GET_OBJECT_ID';
> > sproc.StoredProcName := 'GET_OBJECT_ID';
> > try
> > with sproc do
> > begin
> > ParamByName('PARENTID').AsInteger := 0;
> > ParamByName('OBJ').AsString := '__rootlevel__';
> >
> > ExecProc;
> >

Here, to read the return values, you need to read Fields[], not Params[]

> > Result := ParamByName('OBJECTID').AsInteger;
> > if ParamByName('INSERTED').AsInteger <> 0 then
> > FNewObjectsCreated := True;
> > end;
> > except
> > sproc.Free;
> > raise;
> > end;
> >
> > sproc.Free;
> > {$IFDEF DEBUG_VER}
> > QueryPerformanceCounter(n2);
> > WriteLog(Format('ƒƒ-> GetRootObject T = %g <-ƒƒ', [(n2 - n1) / c]));
> > {$ENDIF}
> >
> > end;
> > //#---------------------------------------------------------------------
> >
> > The DoTheFetch Method in IB_Components has some comments about a 'Bug'
> I do
> > not
> > really understand this. Has this something to do with my Problems ?

It's hard to say. There are two possible gdscodes that throw the SQLCODE
-501 error. One of them is 335544577 "Attempt to reclose a closed
cursor". But your proc isn't (or shouldn't be) processing a cursor, since
it is (or ought to be) an executable procedure.

SQLCODE 0 isn't an error - it's the success code.

The error I highlighted: it's an error to refer to the return parameters of
an executable stored procedure using the Params[] array. TIB_Statement
uses Params[] for input parameters and Fields[] for return parameters. So
an "unknown column" error ought to be bubbling up there somewhere. We
don't see what your error handler is doing...but it's possible that your
exception is occurring before execution ever reaches this error.

I also notice you totally ignore the transaction during this code, and you
don't test the StoredProc to see whether it is prepared. This might
alternatively account for the SQLCode -501 (gdscode 335544327, "Invalid
request handle")

Or, "invalid request handle" would apply if you were trying to execute a
selectable SP. Could it be that the stored procedure that you are calling
with ExecProc is actually a selectable SP? And what is the setting of the
StoredProcForSelect property? It has to be False for an executable SP.

Helen