Subject Re: [IBO] Hannes :: Stored procedures
Author hannes hernler
"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
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
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 ib_cursor