At 02:55 AM 31/08/2005 +0000, you wrote:

[snip]

I'll answer the things that are relevant to IBO. This list is actually the
IBO list, not a general list for SQL puzzles. As a newbie to client/server
databases, you'll need to join the firebird-support list...

>I have a lot of this functionality built in, but can't for the life of
>me get joins to be updateable. For example:
>
>select l.pro_no
> , l.tripid
> , l.cust_id
> , l.car_id
> , l.gross_pay
> , l.created_date
> , l.created_time
> , l.status
> , l.memo
> , c.cust_id
> , c.customer
> , c.watts
> , c.phone
>from t_load l
>left join customer c
>on l.cust_id = c.cust_id
>order by pro_no
>
>gets me the customer assigned to the load, the query works

One thing to understand from the start is that joined queries are not
"updatable". IBO can make it so that it behaves as though it were.

A single-table select is "naturally updatable". That does not mean that
you ever touch the stored data directly from an application, as you did
with DBase. What it does mean is that the cursor from which the dataset is
fetching rows contains a unique key by which an application can request an
operation targetting a specific row in the underlying table for an update
or delete request. If you set the RequestLive property true and correctly
identify the KeyLinks of the dataset, the IBODataset behaviour is to
generate the required Insert, Update and Delete requests automatically.

When the dataset does this, it is actually composes the required
parameterised SQL statements and loads them into its XxxxxSQL properties
(EditSQL for updates, InsertSQL for inserts, DeleteSQL for guess what...)

You can alternatively compose your own parameterised statements. In the
case of naturally updatable sets (in SQL we talk of sets, not
tables). That is, you can supply your own data manipulation language (DML)
statements to each of these XxxxSQL properties. In this case, RequestLive
is simply ignored, since your custom SQL has the effect of making the
dataset behave as though it were "live".

This is optional for naturally updatable sets and mandatory if you want to
be able to update the database from a joined set. There is one more
alternative with a joined set, which I'll get back to later.

>and using a
>small SP I was able to get a lookupcombo to write /update the customer
>on the load.

You DO need a SP to make your joined set updatable. It should not be
surfaced to the GUI, however. What you should do is write the SP as an
executable procedure, with input arguments covering *at least* the primary
keys of any tables that it is to read from or write to. Then, in your IBO
application, you would enter the parameterised procedure call in the
EditSQL property as 'EXECUTE PROCEDURE MyProc(:arg1, :arg2, ....etc).

>I can't get the above query inserting editsql that looks
>the same with the field = :field where pk_field = :old_pk_field (tried
>without the old)

You don't refer to the Old_ values. These are mostly used internally and
can get things very confused in your code unless you are very clear about
what is actually happening. However, as I showed your earlier, you won't
get an insert to work on a joined set "out of the box".

What's important is to set your KeyLinks accurately and to have good calls
to executable SPs waiting there in your XxxxxSQL for these sets. You don't
refer to the Old_ values but make the input arguments of your executable
procedure match up to the KeyLinks of your dataset.

>The best I can do is put the same update sql in the
>insert sql to make the set where I can edit, but I can't
>append/insert. Do I need to use TIBOQueries to display the joins and
>strictly SP's to write back?

As explained above, life just is not that complicated!! Freely use joined
sets, but make it a mission to understand the logic behind them and, in
IBO, do the things you need to do to make them act as though they were
updatable.

>Is there a more effecient way to do this?

OK, now we come to the other technique that I alluded to earlier. Your
dataset has a property called KeyRelation. If you actually want to perform
DML only one ONE table in the joined set, you can place the name of that
table in KeyRelation. You must be meticulous in setting your KeyLinks and
very clear in your head about logic of the relationships between the
KeyLinks fields. Then, IBO will do its best to compose the XxxxSQL for you
automatically.

Some complex joined sets might defeat this technique. There are some sets
where it just will not work; but it is at least worth trying if the joins
are all INNER, or if the KeyRelation is the leftmost (first) table in the
join *and* is not right-joined or full-joined to anything.

Do please make use of the tech info sheets at
www.ibobjects.com/TechInfo.html.

I'd like to wish you well with this project. Coming to IBO from the cosy
but limited world of the BDE is quite a challenge. Doing the shift from
the file-based desktop database to the networked, transaction-driven
client/server environment and the abstraction of the SQL logic is not
trivial. Doing both in one hit is bold and brave. :-) Don't try to do
too many things at once; and follow the rule that, if something seems
difficult, then there is something important that you haven't discovered yet.

Helen