Subject Re: [IBO] Syntax for passing variable to insert query
Author mitch_landor
--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 03:45 PM 12/09/2006, you wrote:
>
> >Here is the select statement for your reference:
> >
> >SELECT *
> >FROM LOC
> >where
> >pversion_id = :Var_PVERSION_ID
> >ORDER BY ID
>
> This is fine. You will apply the value to the parameter for the
> SELECT in the BeforeOpen event of the dataset, viz.
>
> Dataset.ParamByName('Var_PVERSION_ID').AsInteger := VAR_PVERSION_ID;
>
> I don't like SELECT * at all. Do yourself and your antecedents a
> favour and write a proper output specification.
>
>
> >Here is the insert statement:
> >
> >INSERT INTO LOC(
> > /*ID, PK*/
> > PVERSION_ID,
> > NAME,
> > FLOORAREA,
> > LOCKD,
> > CREATED,
> > DESLOCREF,
> > LOCQTY)
> >VALUES (
> > /*:ID,*/
> > :PVERSION_ID,
> > :NAME,
> > :FLOORAREA,
> > :LOCKD,
> > :CREATED,
> > :DESLOCREF,
> > :LOCQTY)
>
> Another favour you can do for yourself, rather than tinkering with
> the auto-generated InsertSQL and getting unpleasant surprises, is to
> make sure that your BI trigger is a "safe" one, viz., tests for null...
>
> if (new.ID is null) then
> new.ID = gen_id(SomeGenerator, 1);
>
> And then, in IBO, make use of GeneratorLinks. You can do this in one
> of two places, either on the TIBODatabase, with an entry in
GeneratorLinks as
>
> Loc.ID=SomeGenerator
>
> or, in the GeneratorLinks property of the dataset:
>
> ID=SomeGenerator
>
> Then you will never have to pay any further attention to that
> mandatory PK value.
>
> NB, make sure you don't put any whitespace in the GeneratorLinks
strings.
>
> Oh, and, yes, sorry, you *will* need to refer to Fields for an
> InsertSQL, not Params, because of what the component does to pick up
> values for the bound insert.
>
> But make sure you are clear in your mind that DML statements that are
> NOT bound to a dataset take *params* directly.
>
> Helen
>


Got it. Many thanks.

How would I go about this if I want to do an insert using a dataset
consisting of 2 or more joined tables? Where can I find the
documentation on how to do this? (I have the IBO Getting Started
Guide) and also "the bible" ...your book.