Subject Re: [IBO] Multiple SQL Statements in Edit-/Insert-/DeleteSQL
Author Helen Borrie
At 04:04 PM 12/07/2004 +0200, you wrote:

> >
> > A DML statement can operate on one and only one table.
>But do these SQL-Statements need to be treated as a
>single DML Statement?

Yes, because IBO implements the API, it doesn't re-invent the database engine.

> > >and I don't want to add a stored procedure for this more
> > >or less trivial task.
> >
> > Updating two tables is not a trivial task. If you have to update both
> > tables, you have no option other than a stored procedure (which, itself,
> > can be quite trivial).
>Wouldn't it be nice to just have 2 (or n, for that matter)
>update-statements which are executed by ibobjects when necessary?

Now, how would IBO do that?

You can, of course, execute batches of statements using TIB_Script; but,
of course, scripts can't take parameters...

> > >I can't use the BeforeInsert event, because bar depends
> > >on foo (foreign key set).
> > >And I can't use AfterInsert because I'd have to Refresh the
> > >data after I've done the insert.
> >
> > You simply can't do it, period. It's against database rules.
>What do you mean? I can't do what?

You can't operate on multiple tables in a single DML statement.

>And having two tables linked to each other definitely is
>not against database rules...

Not against database rules for *selecting* - you can select from multiple
tables in lots of ways. You just can't perform multiple-table operations
in insert, delete and update statements.

> > >OnCustomInsert would work,
> >
> > Not. OnCustomInsert refers to a custom InsertSQL operation: inserting a
> > row of data into a single table using your own parameter assignments; or
> > calling a parameterised stored procedure.
>I can do whatever is necessary to insert the
>dataset in OnCustomInsert, can't I?

-- "insert the dataset" ---????

> > >but the InsertSQL is less to type and maintain, IMHO.
> >
> > It certainly is; and, if you are careful with the names you give to the
> > input parameters, you won't have to code anything.
>Except for the stored procedure...

Of course.
What you could do (though it's ugly) is use KeyRelation to make one side of
the join updatable (a unique feature of IBO) and set up a parameterised
TIB_DSQL to insert a row for the other side of the join immediately after
the Insert method completes (this is not in AfterInsert event, btw...), but
before the transaction is committed. You would need to pass the parameters
to it yourself, in code. It seems like a lot of work compared to a
one-stop SP call, though it's feasible.