At 07:07 PM 17-10-01 +0200, hannes hernler wrote:

>I´ve got a funny phenomenon:
>
>I have a stored procedure in my database and tested
>various IBO components by calling this SP.
>------------
> if not dsqlNewItem.Prepared then
> dsqlNewItem.Prepare;
> dsqlNewItem.ParamByName('POMAGID').asinteger := Info['OMag'];
> dsqlNewItem.ParamByName('PIPARENT').asinteger := Info['Parent'];
> dsqlNewItem.Execute;
> result:= dsqlNewItem.fieldbyname('RID').asinteger;
>-------------
>the DSQL query is always the same:
>
>SELECT RID FROM SP_OMITEM_NEW( ?POMAGID, ?PIPARENT )
>------------
>in the SP a generator is called and an row inserted in a table.
>the generated value is returned.

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

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

The SQL should be
EXECUTE PROCEDURE SP_OMITEM_NEW( ?POMAGID, ?PIPARENT )

>when I use TIB_StoredProcedure everything works fine.
>
>ok, I go and use TIB_StoredProcedure .
>but in IBO help is written that it should make no difference...

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

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). That kind of procedure is typically of the FOR SELECT....INTO...<variables>...DO
BEGIN
....
SUSPEND;
END

(not what you are dealing with here)

cheers,
Helen




All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________