Subject Re: [IBO] Syntax for passing variable to insert query
Author Helen Borrie
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);
Dataset.ParamByName'('PVERSION_ID').AsInteger := VAR_PVERSION_ID;

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.