Subject Re: [IBO] How to pass parameter for the SP in the InsertSQL statement of TIBOQuery
Author Helen Borrie
At 10:46 PM 28/10/2004 +0000, you wrote:

>I would like to know how do I pass parameter for the SP I am calling
>in the InsertSQL statement a TIBOQuery.

Just make sure that the input parameters for the EXECUTE PROCEDURE
statement of the InsertSQL match the name and type of those in the fields
you want to pass. The same rule applies to the other parameters: make sure
the names of the parameters all match. IBO will do the rest.

More comments in-line:

>Here is my TIBOQuery object,
>object HolQry: TIBOQuery
> Params = <
> item
> DataType = ftUnknown

Make sure you that, if you are going to instantiate any TParam objects,
that you set the properties of these params properly. Open the Params
editor and assign the proper ft* value to the DataType property.

> Name = 'HOLGRPID'
> ParamType = ptInput
> end>
> AutoCalcFields = False
> AutoFetchAll = True
> BufferSynchroFlags = [bsBeforeEdit, bsAfterEdit, bsAfterInsert]
> DatabaseName = 'NDSERVER_1'
> DeleteSQL.Strings = (
> 'IN_USE = 0'
> EditSQL.Strings = (
> IB_Connection = HolConn
> InsertSQL.Strings = (

This does not look like a procedure that could insert a new record into the
table HOLIDAYS based on data inserted into the dataset buffer in dsInsert
mode. As a minimum, the SP would need input parameters for each of the
database columns that you have in the dataset's SELECT statement. I can't
guess what those fields would be, since your SELECT statement doesn't
provide a field list. Guessing from your EditSQL statement, you would need
a SP that started something like this:

create procedure NEW_HOL_SP (
HOLDID sometype,
HOLGRPID sometype,
HOLDATE sometype,
HOLNAME sometype,
AUTOEXPIRE sometype)

Your InsertSQL would then be:


and IBO would pick up the values from the Insert buffer.

> KeyLinks.Strings = (
> 'HOLID')
> KeyLinksAutoDefine = False
> PreparedInserts = True
> RecordCountAccurate = True
> OnError = HolQryError
> AfterInsert = HolQryAfterInsert
> BeforePost = HolQryBeforePost
> AfterPost = HolQryAfterPost
> OnNewRecord = HolQryNewRecord
> SQL.Strings = (
> 'select * from HOLIDAYS where HOLGRPID = :HOLGRPID and IN_USE =
> FieldOptions = []
> Left = 93
> Top = 272
>So I want to call NEW_HOL_SP in the InsertSQL. This Stored Procedure
>NEW_HOL_SP takes one parameter HOLGRPID. I've tried to use
>HolQry->ParamByName("HOLGRPID")->AsInteger = 1;
>in the BeforePost() event. It doesn't seem to work.

No. You would do something like this if you were using a separate
statement object that is not bound to the dataset..