At 01:03 PM 13/09/2006, you wrote:

>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?

OK, this is something you can do with IBO - to make a dataset "live"
when its cursor is not linked directly to a table, as is the case
with a joined set.

>(I have the IBO Getting Started Guide)

OK, this document explains KeyLinks. Make sure you understand what
KeyLinks do.

>and also "the bible" ...your book.

Well, somewhere it'll tell you that you can't do a positioned update
on a cursor to a joined set...which is true throughout SQL. But IBO
does magical things and makes it possible...and this brings us to the
*intended* use of InsertSQL.

However, first, IBO has a built-in trick that, with some joined sets,
makes setting RequestLive on a joined set valid. It is the
KeyRelation property.

If you want to be able to update *one* table in your joined set, you
can set that property to the name of that particular table and,
provided your KeyLinks are correct (contain all of the fields
required to identify one and only one row in the KeyRelation table),
IBO will automatically construct correct XxxxSql for you.

Now, going back to making non-updatable sets updatable. In this
case, it doesn't matter whether you set RequestLive true or not. If
you provide a valid DML statement for each XxxxSQL property, with
parameters that refer to fields in the set, you can make the set updatable.

In SQL, of course, it is impossible to update multiple tables in a
single DSQL statement. However, the trick when you want e.g. the
Insert method of the dataset to update (add records to) more than one
table, or perhaps to insert to one table and update others, etc., is
to write an executable stored procedure that takes the *output*
values of your dataset as input parameters. Inside your SP, your
sort out which inputs are for which table and perform the DML in the
procedure.

Your InsertSQL in that case would be along the lines of:

execute procedure myproc(:fieldA, :fieldB, :fieldC, etc...).

If the names of the parameters that you use in the statement are the
same as the respective field names in the bound dataset, IBO will
assign the field values automatically for you. (Parameter names
never have to match the declared input names in the procedure's DDL,
only the data types must match.) You can alternatively use any names
you like for the parameters but, in that case, you will have to
assign the Params values explicitly in the BeforePost.

Make sure the KeyLinks are perfect. "Perfect" means there is no
possibility that the same set of KeyLinks could locate more than one
record in the table -- otherwise your DML operation will except with
a "multiple rows in singleton select" error.

As to finding documentation for working out what the KeyLinks will
be, you'll find several FAQs about it in the online FAQ. Some people
"get it" without further ado; others keep stabbing in the dark for
years, depending on luck rather than light for getting it right. I
can't help such people; but it's a good tip to be always mindful
that a dataset is *output* - a representation of the stored data -
and joined sets are always a denormalised view of related data from
multiple tables.

Don't treat parameters as though they were variables. They are
not. So, if you want to use the same *value* in more than one place
in your parameterised statement, you will have to assign separate
*parameters* for each usage of the value.

Helen