Subject Re: [IBO] Hannes :: Stored procedures
Author Jason Wharton
Perhaps you could try investigating the use of SUSPEND and EXIT in different
combinations in the SP.

I suggest this be taken up in an InterBase/Firebird forum as it appears to
be a situation outside of IBO's control.

Jason Wharton
CPS - Mesa AZ

----- Original Message -----
From: "hannes hernler" <ibo.hernler@...>
To: <>
Sent: Wednesday, October 24, 2001 4:00 AM
Subject: Re: [IBO] Hannes :: Stored procedures

> "Helen Borrie" (>) answered to
> hannes hernler(>>)
> but hannes asks again:
> > >...I have a stored procedure in my database and tested
> > >various IBO components by calling this SP....
> > >when I use a TIB_Cursor, the procedure is called twice.
> > What calls the procedure twice? What's the problem you
> > think you have here? Are you expecting two results from
> > one call to the stored procedure?
> no, I am expecting 1 value (the generated primary key) as
> result.
> the SP should insert a new line in a table, generate a key and return
> this generated value.
> when I call the SP with TIB_Cursor, the line is inserted 2 times!!
> and the value is returned once.
> > >when I use TIB_DSQL it works one time in the context above
> > > and from then on reurns the same RID every time (and no
> > > row is inserted)
> > You can't return a dataset to an IB_DSQL (which is OK here,
> > because your stored proc doesn't return a dataset). Your SQL
> > should invoke the procedure (not select from it) and you should
> > call Execute on it. It is correct to read the Fields[] array to get the
> > return value - AFTER it has been committed.
> yes, I tried this also but again: the line is inserted 2 times!
> > >when I use TIB_StoredProcedure everything works fine.
> > ..meaning that you can use the encapsulated stored proc component,
> > or you can use a dsql component (= Dynamic SQL call) to pass an
> > EXECUTE PROCEDURE statement. That will be just fine.
> with Property StoredProcForSelect the TIB_StoredProcedure
> can also query the database for the result of the SP.
> I use this and it works fine, the dataset is inserted just 1 time
> and the ID is returned.
> but my problem is that I want to use the components from a script language
> ( in an isapi module.
> and I cannot tell all script coders that they should not use a SELECT
> statement.
> we have the problem that we have a lot of double inputs because of this.
> > Use SELECT...FROM...ASTOREDPROC when you have a stored
> > proc that is designed to return a dataset - in that case, use an
> > ib_cursor or an ib_query for it. You call First on the ib_cursor,
> > or Open on the ib_query (not Execute). ....
> unfortunatly this doesnt work with a SP that has also DML commands
> like insert. "insert" is executed twice when the SP is invoked via
> !
> hannes
> IB Objects - direct, complete, custom connectivity to Firebird or
> without the need for BDE, ODBC or any other layer.
> - your IBO community resource for Tech Info
> keyword-searchable FAQ, community code contributions and more !
> Your use of Yahoo! Groups is subject to