At 05:59 AM 18/02/2004 +0000, you wrote:

>This may be a bug in IBO or FB 1.5 I am not sure,or it could be my
>code,

It's not so much your code (...hmmmm...) as your approach. From what I can
see, you are using a dataset component to perform a DML operation (do-able
but not recommended) AND you are kind of regurgitating it as some kind of
"second-level" dataset.

>but basicly using the code below (stored proc and IBO client
>code) the FB server process will rise and rise and rise until the
>servers memory is exhausted.

No steam coming out the back of the server? :-)

>I originally was using a blobstream, then changed it to use assign
>just to see if it made any difference and it did not.

No, it wouldn't. The basic flaw isn't fixed by this, though the VCL
TBlobstream isn't really supported by IBO.

>Anyone see anything wrong with my code? or any Ideas?

Yup. There is a property on TIBOStoredProc StoredProcHasDML - this has to
be set true to deal with the cleanup problems you're encountering in the
transaction. In fact, what I think is going on here is that your commit
isn't actually doing anything, but you're not trapping that possibility -
you're committing regardless but the transaction just stays in a prepared
state forever...something along those lines, anyway.

But the problem really is the way you are doing this. What you want to do
is insert a new record and get the generated ID back into the application -
right?

First, alter the SP (take out the suspend):

>reCREATE PROCEDURE ADD_NEW_OFFLINE_MSG (
> MSG_BLOB BLOB SUB_TYPE 0 SEGMENT SIZE 4096)
>RETURNS (
> MSG_ID INTEGER)
>AS
>begin
> MSG_ID = GEN_ID(GEN_OFFLINE_MSG_BLOBS_ID,1);
> INSERT INTO OFFLINE_MSG_BLOBS
> (MSG_ID,MSG_BLOB)
> VALUES
> (:MSG_ID,:MSG_BLOB) ;
> // suspend; take this out <---------------------------------
>end

Then, do this with a TIB_DSQL, SQL property being
EXECUTE PROCEDURE ADD_NEW_OFFLINE_MSG (:MSG_BLOB)


function Tclientthread.add_new_offline_msg(themsg: tmemorystream): integer;
begin
result:=0;
with db_query do // db_query is the tib_dsql
begin
if not ib_transaction.Started then
ib_transaction.StartTransaction;
try
if not Prepared then Prepare;
TIB_ColumnBlob(paramByName('MSG_BLOB')).Assign(themsg);
EXECUTE;
Result := FieldByName('MSG_ID').AsInteger;
IB_Transaction.Commit;
except
on e:exception do
begin
if IB_Transaction.Started then
IB_Transaction.Rollback;
writeerrorlog(e.message+'(add_new_offline_msg)');
raise;
end;
end;
end;
end;

Helen