Subject Re: [IBO] Syntax for passing variable to insert query
Author mitch_landor
--- In, Helen Borrie <helebor@...> wrote:
> At 09:00 PM 11/09/2006, you wrote:
> >Right Click on TIBOQuery Component & "Edit Query".
> >Select "UpdateSQL" tab.
> >There are 3 boxes - "EditSQL", "InsertSQL", "DeleteSQL".
> >What I described as the "insert window" is the "InsertSQL" box.
> OK, now we are getting to the nitty-gritty. You haven't hitherto
> mentioned that you have a dataset (a SELECT statement) and that you
> were playing about with the InsertSQL property *of a dataset*.
> Right. Now, one of the things a dataset object (such as TIBOQuery)
> "knows" is how to create Insert, Update and Delete statements based
> on the specification of its SELECT statement. Provided you give it
> the unique key of the set (via KeyLinks) and set its RequestLive
> property true, it generates these statements automatically.
> All this is hunky-dory if your SELECT statement outputs fields from a
> single table. With joined sets, grouped sets and selectable stored
> procedures, you have to create a custom SQL statement to perform the
> respective DML operations according to data entered into field
> "slots" in your interface. As a broad rule, such custom SQL
> statements will be calls to executable stored procedures, that take
> values from the "field slots" as input parameters.
> >Can I modify the code in the "InsertSQL" box that was created when I
> >pressed the button "Generate for Table" so that I can make one of the
> >columns receive a delphi global variable, and have the other columns
> >take their values as normal from an dbgrid?
> No. In fact, the other columns do NOT take their values from the
> grid. Lying between the grid (or any other data-aware control) and
> the dataset is a datasource. A datasource is an object too, one that
> knows what to do when the user changes something in the control. It
> does quite a lot of stuff and, all going well, the datasource will
> update the dataset, effectively passing user's changes (or
> instructions) to the dataset. The datasource "knows" what's
> happening in both the control and the dataset and its job is keep
> them both in sync.
> >If not, is there an easy way to do this using the TIBOQuery component?
> For simplicity's sake, let's suppose the SELECT statement that forms
> the SQL property of the dataset is a "simple" one - consisting of the
> primary key fields plus some others, all from a single table.
> In the ordinary case, when you call the dataset's Insert method, the
> "empty" record that appears is the "visual end" of an array of
> parameters that the dataset component has automatically constructed
> in the InsertSQL property. You don't want to change *that*. What
> you do want to do here is to intervene and change the value that the
> component would automatically assign to that particular parameter.
> Now, absolutely nothing happens in the database until you call
> Post. So the place where you want to intervene is just before the
> Post occurs, i.e. at BeforePost. Here, you want to make what's
> called a "late binding" for this parameter. It's dead simple:
> procedure TForm1.IBOQuery1BeforePost(DataSet: TDataSet);
> begin
> Dataset.ParamByName'('PVERSION_ID').AsInteger := VAR_PVERSION_ID;
> end;
> One other point you could do it, provided you ensure that the user
> can't modify it, is in AfterInsert - like what happens when the
> dataset invokes its Gen_ID() method and picks up the
> GeneratorLinks. That way, the user will be able to see the actual
> value that will be posted.
> Helen

Helen, thanks for your very helpful reply.

I am using a "simple" SELECT statement that forms the SQL property of
the dataset consisting of the primary key field "ID" plus some others,
all from a single table. However I have a "where pversion_id =

The variable that I pass to the parameter "Var_PVERSION_ID" is called
"curr_proj_version". This works OK and returns just the rows that I need.

When inserting a new row, I comment out the "ID" field in the insert
statement because I have an insert trigger that automatically provides
a value for this primary key column. I have tested this by manually
filling in the other not null column "PVersion_ID" and it works OK.

Now I want to automatically provide a value to the "PVersion_ID"
column when I do the insert.

I examined your instructions, but shouldn't the procedure use FieldBy
Name rather than ParameterByName to set the field value?

I tried both of the following statements in the procedure and now I
get the error "Field "ID" must have a value" as if the trigger has
stopped working.

If I manually enter the next "ID" value I get the message "Field
"PVERSION_ID" must have a value."

procedure TDataModule5.IBOQuery1BeforePost(DataSet: TDataSet);
iboquery1.FieldByName('PVERSION_ID').AsInteger := curr_proj_version;
//alternative statement tested
//iboquery1.ParamByName('var_PVERSION_ID').AsInteger := curr_proj_version;

Here is the select statement for your reference:

pversion_id = :Var_PVERSION_ID

Here is the insert statement:

/*ID, PK*/

Thanks for your help,