Helen,
Thanks for the assistance, I will implement your suggestions tonight.
One question though, I am not sure I understand how I am getting the
record ID back without the suspend?
I could have swore I tried it without it before and I never got the
ID back.

Oh, and actually the db_query is a cursor component. I used that
because I thought it did not cache all the metadata and would be
faster for a middle tier server.

So using the cursor component is bad for this type of operation?

Thanks

--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:
> 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