At 12:36 AM 5/06/2004 +0000, you wrote:

>Hi,
> should I be able to edit a joined tables' field via a SP with a
>TIBOQuery, TDataSource, and a TDBMemo or TDBRichEdit?
>
>My FB1.5 d/b has...
>create table REPORTS
>(
> PK SMALLINT NOT NULL,
> NAME D_VCHAR50,
>~snip~
> CONSTRAINT PK_REPORTS PRIMARY KEY (PK)
>)^
>
>create table REPORT_DETAILS
>(
> FK SMALLINT,
> DEPT D_DEPT,
>~snip~
> DATE2 DATE,
> DESCR D_BLOB1,
> CONSTRAINT FK_REPORT_DETAILS FOREIGN KEY (FK) REFERENCES REPORTS
>(PK) ON UPDATE CASCADE ON DELETE CASCADE
>)^
>
>create or alter procedure UPDATE_REPORTS (OLD_PK SMALLINT, NAME
>VARCHAR(50), ~snip~, DATE2 DATE, DESCR BLOB)
>as
>begin
> UPDATE REPORTS SET NAME = :NAME, ~snip~
> WHERE PK = :OLD_PK;
>
> update REPORT_DETAILS set ~snip~, DATE2 = :DATE2, DESCR = :DESCR
> where FK = :OLD_PK;
>end
>^
>with accesses granted.
>
>My TIBOQuery SQL is
>SELECT R.PK
> , NAME
>~snip~
> , D.DATE2
> , D.DESCR
>FROM REPORTS R
>LEFT OUTER JOIN REPORT_DETAILS D on D.FK = R.PK
>and D.DEPT = USER
>
>and the Update SQL is
>EXECUTE PROCEDURE UPDATE_REPORTS(
> :REPORTS.PK,
> :REPORTS.NAME,
>~snip~
> :REPORT_DETAILS.DATE2,
> :REPORT_DETAILS.DESCR)
>
>I can see the joined fields' data in the TDBMemo, but cannot edit it!
>The TIBOQuery's RequestLive=true & ReadOnly=false.
>The TDataset's AutoEdit=true.
>The TDBMemo's ReadOnly=false.
>
>Have I missed something, or is this a limitation from using the
>TDataset components (so I can try FreeReport)?
>
>Apologies if this is not an IBO issue.

Partly, it's an IBO issue, insofar as IBO can make non-editable sets "live"
by providing the means to target the underlying table rows
precisely. These sets are otherwise not editable at all.

To make a set editable by any means, you need to set your KeyLinks so that
each output row in the joined set is uniquely defined. If the KeyLinks are
wrong, IBO can't make the dataset updatable, no matter what you throw at
it. Part of your design problem is that your Report_Details table has no
primary key, so it isn't possible for this set to have valid KeyLinks. The
DB_KEY *of the output set* is no use to you here because it can't target a
unique Report_Details row.

However, even with good KeyLinks, your EditSQL won't work, because of the
outer join. Your application potentially would be trying to "update"
non-existent Report_Details rows.

It's a design problem, with several structural causes and even some
possible solutions. To begin with, you need a PK on Report_Details and to
include it in the output set. Then, if you want to be able to perform
updates on Report_Details data, that data has to exist somehow.

One way that you *could* enable your requirement is to use the KeyRelation
facility, alone, or in combination with suitable EditSQL, InsertSQL and
DeleteSQL. As long as you don't have a requirement to edit any columns in
Report, or to insert or delete Report rows, you can set the KeyRelation to
Report_Detail. That will make the dataset updatable from IBO's p.o.v.
(provided you add a PK to Report_Detail and use 100% correct KeyLinks).
Report_Detail columns will be updatable -- you can insert, edit and delete
the underlying R_D table using the normal, automatic IBO methods. The
Report columns would be read-only. Custom xxxxSQL properties would be
quite optional in this setup.

Using a conventional IBO master-detail structure is the more sensible way
to maintain this structure since, some time, you are going to want to
maintain the Report table!! There's no reason why you can't run and
refresh an outer-joined dataset in the same transaction, for your
report. Aside from that, doing maintenance and reporting from the same
transaction isn't really very reliable. Generally, you want to do
maintenance operations in a Read Committed transaction and report from a
snapshot (Concurrency) transaction. You usually want a stable view of
database state for stuff you commit to paper. A Read Committed view is a
transient view, at best, ideal for interactive updating and unreliable for
reporting.

Helen