At 10:21 AM 20/10/2004 +0200, you wrote:

>hello all,
>
>sorry for the long post, but it seems that I still have to learn a lot
>about IBO
>:-)
>
>Let's start with the definition of these two tables in my database:
>
>CREATE TABLE Anagraphics(
> ID_Anagraphic INT_ID, /* PK */
> Prefix INT_PREFIX,
> Unit INT_UNIT,
> Call_Sign CH_CALL_SIGN,
> Operator_Name CH_OP_NAME,
> City CH_CITY,
> TX_Province INT_ID, /* INT_ID = INTEGER, NOT NULL, > 0 */
> TX_Region INT_ID,
> Own_Province INT_ID,
> Own_Region INT_ID,
> Class CH_CLASS,
> Op_2_Prefix INT_PREFIX_CAN_BE_NULL,
> Op_2_Unit INT_UNIT_CAN_BE_NULL,
> Op_2_Call_Sign CH_CALL_SIGN_CAN_BE_NULL,
> Op_2_Name CH_OP_NAME_CAN_BE_NULL,
> Op_3_Prefix INT_PREFIX_CAN_BE_NULL,
> Op_3_Unit INT_UNIT_CAN_BE_NULL,
> Op_3_Call_Sign CH_CALL_SIGN_CAN_BE_NULL,
> Op_3_Name CH_OP_NAME_CAN_BE_NULL,
> Last_Progressive INT_ANAGRAPHIC_PROGRESSIVE,
> Can_Delete_Last LOGICAL,
> Certificates INT_CERTIFICATES,
> Amount NUM_AMOUNT);
>
>CREATE TABLE Provinces(
> ID_Province INT_SPECIAL_ID, /* PK, INT_SPECIAL_ID = INTEGER, NOT
> NULL, >= 0 */
> Short_Name CH_PROVINCE_SHORT_NAME,
> Long_Name CH_PROVINCE_LONG_NAME,
> ID_Region INT_SPECIAL_ID,
> Award LOGICAL);
>
>Then, I have three TIB_Query (and associated TIB_DataSource) in the
>data-module,
>the first query has this SQL:
>
>SELECT A.ID_ANAGRAPHIC, A.PREFIX, A.UNIT, A.CALL_SIGN,
>A.OPERATOR_NAME, A.CITY, A.TX_PROVINCE,
>(SELECT P1.SHORT_NAME FROM PROVINCES P1 WHERE P1.ID_PROVINCE = A.TX_PROVINCE)
>AS TX_PROV,
>A.TX_REGION, A.OWN_PROVINCE,
>(SELECT P2.SHORT_NAME FROM PROVINCES P2 WHERE P2.ID_PROVINCE = A.OWN_PROVINCE)
>AS OWN_PROV,
>A.OWN_REGION, A.CLASS,
>A.OP_2_PREFIX, A.OP_2_UNIT, A.OP_2_CALL_SIGN, A.OP_2_NAME,
>A.OP_3_PREFIX, A.OP_3_UNIT, A.OP_3_CALL_SIGN, A.OP_3_NAME,
>A.CERTIFICATES, A.AMOUNT
>FROM ANAGRAPHICS A
>ORDER BY A.PREFIX, A.UNIT
>
>The second and third query are used to retrieve province ID's for Anagraphics
>and also to show TIB_LookupCombo's in the grid:
>
>Second query:
>SQL: SELECT * FROM PROVINCES ORDER BY SHORT_NAME
>KeyLinks: ID_PROVINCE=ANAGRAPHICS.TX_PROVINCE
>KeyLinksAutoDefine: False
>KeyDescLinks: SHORT_NAME=TX_PROV
>
>Third query:
>SQL: SELECT * FROM PROVINCES ORDER BY SHORT_NAME
>KeyLinks: ID_PROVINCE=ANAGRAPHICS.OWN_PROVINCE
>KeyLinksAutoDefine: False
>KeyDescLinks: SHORT_NAME=OWN_PROV
>
>Well, using that SQL in the first query the dataset is perfectly editable (of
>course the query's RequestLive is True), but as you can see, I had to use a
>non-SQL92 syntax.
>
>Now I modify the SQL of the first query to be SQL92 compliant:
>
>SELECT A.ID_ANAGRAPHIC, A.PREFIX, A.UNIT, A.CALL_SIGN,
>A.OPERATOR_NAME, A.CITY, A.TX_PROVINCE, P1.SHORT_NAME TX_PROV,
>A.TX_REGION, A.OWN_PROVINCE, P2.SHORT_NAME OWN_PROV,
>A.OWN_REGION, A.CLASS, A.OP_2_PREFIX, A.OP_2_UNIT, A.OP_2_CALL_SIGN,
>A.OP_2_NAME, A.OP_3_PREFIX, A.OP_3_UNIT, A.OP_3_CALL_SIGN,
>A.OP_3_NAME, A.CERTIFICATES, A.AMOUNT
>FROM ANAGRAPHICS A
>JOIN PROVINCES P1 ON P1.ID_PROVINCE = A.TX_PROVINCE
>JOIN PROVINCES P2 ON P2.ID_PROVINCE = A.OWN_PROVINCE
>ORDER BY A.PREFIX, A.UNIT
>
>Well, now I cannot edit or insert in the Anagraphics table any more,
>receiving
>an error of:
>
>"Cannot insert a row" or
>"Cannot edit row"
>
>I have tried several combinations of KeyLinks for the first query (that
>with the
>first SQL was blank) but without luck. I have also given a look at the
>KeyLinks
>project in the Demos folder, but it wasn't of any help.
>
>Can you give me a guide line on how the KeyLinks of the first query could
>be to
>make the query editable again, stated that leaving KeyLinks blank doesn't
>work?

OK. First, let's settle the problem of "What are KeyLinks"?

Right, for *any* dataset, they are a list of database columns, that are all
present in the query's output set, that form a unique key for the set. For
single table queries, that should be the primary key; although if your
table is not very well normalised, it could be another unique column or
column set from the table.

Then, for lookup datasets, to implement the tib_lookupcombo, Keylinks does
double duty. It must be the unique key of the lookup set but it is ALSO
used to link that unique key to the column in the parent set (or, more
correctly, the "Keysource dataset") from which the lookup is being polled.

As far as I can see, those linkages are correct for the first version of
the query, which is NOT the same as the second version. Also, the first
version is not "non-standard for SQL-92" - it is perfectly standard. And
it is naturally updatable because it is a single-table query and the
KeyLinks is the primary key of that single table. A subquery is *not* a join.

Your problem with the second query is that you have a join there. This
"flattens" the output set and potentially gives you multiple rows with the
same ID_ANAGRAPHICS. You have to find at least one key column for each of
the join streams. Try ID_ANAGRAPHIC + TX_PROVINCE + OWN_PROVINCE. This
might or might not be enough to get a unique key for the set - I can't
guess from what is here. If not, then proceed to try more columns.

These keylinks, once established, will enable IBO to refresh the set, but
it still doesn't make the set updatable. You can only make this set "live"
in one of two ways, either:

1. Set the KeyRelation property to be the name of one single table in the
query that is to be updatable. (This is what I think you actually want here).

or

2. Write executable stored procedures to perform updates, inserts and
deletes to multiple tables, as required. The input parameters must be
fields that can be got from the joined dataset and they must include the
KeyLinks fields.

Gosh, I seem to have been writing variations to this stuff for a week or
more. Why don't you guys read other people's posts?

Helen