At 12:13 AM 24/10/2003 -0300, you wrote:

>I've tried to call a stored procedure, but I have problems when I try to
>return the table identifier, created under the stored procedure's context. I
>need it at client side, because I have a grid that needs to refresh the
>record with this new identifier.
>
>I have the following code attached to TIBoQuery.EditSQL:
>
>EXECUTE PROCEDURE ITEM_ORDER_UPDATE(
> :ItemId, :ProductId, :Value, :OrderId
> , :ItemId /* return a value */
>)

You're getting the error actually because you have included the output
parameter in the input parameter list. You don't do that. Execute
Procedure takes only only input arguments. IBO sets *itself *up for the
output parameters when it calls and processes Prepare().

>And the stored procedure looks like this:
>
>PROCEDURE ITEM_ORDER_UPDATE (
> ItemId INTEGER,
> ProductId INTEGER,
> Value NUMERIC(10,2)
> OrderID INTEGER
>) RETURNS (
> NewItemId INTEGER
>) AS
> IF (ItemId IS NULL) THEN
> BEGIN
> ItemId = GEN_ID(<sequence>, 1);
> <INSERT CODE>;
> END
> ELSE
> <UPDATE CODE>;
> NewItemId = ItemId; /* RETURN PK */
> SUSPEND;
>END;
>
>After the update, ibo returns the following error "parameter mismatch for
>procedure ITEM_ORDER_UPDATE". ?.
>
>Is there a better way to return the new created identifier?

Yes, certainly .

Since you are *passing* the itemID in the stored procedure, use the client
function Gen_ID (available on both the ib_connection and ibodataset
objects) to get the identifier over to the client *before* you post the
update. It has the same effect, plus one more benefit: you are not
dependent on the completion of the post to know what the new itemID
is. This means that, after the update, IBO already knows which record it
has to refresh.

So we would have something like the following in BeforePost:

with YourIBOQuery do
begin
if FieldByName('ItemID').IsNull then
FieldByName('ItemID') := Gen_ID('TheGeneratorName', 1);
inherited;
end;

Now, your update proc just needs to be:
RECREATE PROCEDURE ITEM_ORDER_UPDATE (
ItemId INTEGER, <--- or is this actually a DECIMAL(18,0) ?
ProductId INTEGER,
Value NUMERIC(10,2),
OrderID INTEGER)

AS
IF (IEXISTS(SELECT * FROM TheTable where ItemID = :ItemID) THEN
BEGIN
<Your update statement..WHERE ITEMid = :ItemID>
END
ELSE
BEGIN
<Your insert statement>
END
END ^^

All completely atomic on the server, while on the client, IBO will take
care of ensuring that the new row gets into the dataset, since it already
has the new PK.

Helen