Subject Re: [IBO] Parameter mismatch preparing TIBOStoredProc?? Bug??
Author Helen Borrie
At 08:56 PM 06-11-01 -0800, you wrote:
>OK so I have this StoredProc on the server:
>CREATE PROCEDURE SP_DeletePayment (aKeyCol NUMERIC(10, 0)) AS
> DELETE FROM Payments WHERE PKey = :aKeyCol;
> END ^
>This all loads OK. No problem yet.

When you say "this loads OK", what do you mean? perhaps it compiled OK but did you commit the transaction? Did you test it interactively from the DSQL page of IB_SQL (or with some other interactive tool)?

>I add a TIBOStoredProc object to my data module, set the DatabaseName, and
>then have to type in the SP name (it doesn't list in the drop-down). I try
>to set prepared to TRUE and I get an ISC error "Parameter mismatch error".

OK, you will need to have the connection (TIBODatabase, presumably) active at the time IBO is searching for objects.
Where is the aKeyCol parameter value coming from? Is it of an appropriate data type? Numeric(10,0) seems a bit odd... And how are you passing the value to the component?

>What is up with that?

Unfortunately, ISC error messages are not always appropriate to the error they are reporting. Do try dropping a TIB_MonitorDialog onto your form and watching what goes on during the Prepare...

>AFAIK, calling Prepare should get the parameter info from the server.

It should, if (a) the client is connected to the server and (b) the stored procedure exists.

You could test an alternative route, which is to use a TIB_DSQL to call the SP. Just connect its IB_Connection property to your IBODatabase. Your SQL will be:

EXECUTE PROCEDURE SP_DeletePayment(:aKeyCol)

and you will still need to pass a value to the parameter.

>Question 2: If I am passing an Int64 variable to this parameter, how should
>I do it? There is no .AsInt64 or .AsLargeInt on the Param object. The Param
>is a NUMERIC(10) to match the Keycol which is set by a generator, hence the
>Int64 datatype.

Int64 is valid ONLY in a Dialect 3 database. It is NUMERIC(18,0). You can pass it either AsInteger or simply use the .Value property and let IBO cast it.

If you are passing 32-bit integers, they are also AsInteger; and your Stored Procedure variable should be of type INTEGER, not a scaled numeric.