Helen,

thanks for this lot of hints where to search for the problem.

-------- Original Message --------
Subject: Re: [IBO] SQL Error code 0 or 501 (09-Okt-2004 16:12)
From: helebor@...
To: mw@...

>
> Maik,
>
> Were you the author of this code?

Yes. Why ?

>
> 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.

Yes, it's the part of the server doing the work in the system.
The log is written by the OnError method of of the IB_Objects:

procedure TIBThread.IB_Error(Sender: TObject; const ERRCODE: integer;
ErrorMessage, ErrorCodes: TStringList; const SQLCODE: integer;
SQLMessage, SQL: TStringList; var RaiseException: boolean);
begin
if ERRCODE = 335544336 then
begin
TriggerLogEntry(WLOG_ERROR, TID, 'deadlock to resolve', nil);
end
else if NeedsReconnect(ERRCODE) then
begin
TriggerLogEntry(WLOG_ERROR, TID, ' database connection fault -> reconnect', nil);
SignalForceDBReconnect;
end
else
begin
TriggerLogEntry(WLOG_ERROR, TID, 'SQL Error: ' + IntToStr(SQLCODE),
ErrorMessage);
TriggerLogEntry(WLOG_ERROR, TID, 'Error Codes : ' + IntToStr(ERRCODE), ErrorCodes);
TriggerLogEntry(WLOG_ERROR, TID, 'SQL Message :', SQLMessage);
TriggerLogEntry(WLOG_ERROR, TID, 'SQL :', SQL);
end;

//abort;
end;

>
> > >
> > > 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[]

Ok, I understand - but why did this work anyway ?

>
> > > 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.

Why does IBObjects call the OnError Event with this 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")

What do you mean with ignore the transaction. The commit and Rollback is
done anywhere else. I'm executing some more operations before I commit. Can
this cause problems ?

>
> 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.

Sorry, but I'm not really aware how to divide the one from the other because
I can execute this procedure and can use it in a select statement. I guess
it might be a selectable.

I tested to use a TIB_Cursor to execute the procedure in a select statement
this solved the problem with the 501 error.

maik