Hello Tony,

At 03:31 AM 25/07/2005 +0000, you wrote:

>As mentioned the 'problem' query is on a view that is a subset of a
>table. The code from the TIBO Query is:
>SQL:
>
>SELECT *
>FROM "View_MaintReq_Originator"
>
>(Where * is:
>REQUEST_ID (Primary Key),
>ORIGINATOR,
>DEPARTMENT
>PRIORITY,
>MAINTENANCE_TYPE,
>ASSET_NUMBER,
>ASSET_DESC,
>ORIGINATION_DATE,
>LAST_UPDATED,
>ORIGINATOR_COMPLETED_DATE
>REQUEST_SHORT_DESCRIPTION,
>ORIGINATOR_COMMENTS,
>ORIGINATOR_FINISHED)
>
>Edit SQL:
>
>UPDATE "View_MaintReq_Originator" SET
> REQUEST_ID = :REQUEST_ID,
> PRIORITY = :PRIORITY,
> MAINTENANCE_TYPE = :MAINTENANCE_TYPE,
> ASSET_NUMBER = :ASSET_NUMBER,
> ASSET_DESC = :ASSET_DESC,
> REQUEST_SHORT_DESCRIPTION = :REQUEST_SHORT_DESCRIPTION,
> ORIGINATOR_COMMENTS = :ORIGINATOR_COMMENTS,
> ORIGINATOR_FINISHED = :ORIGINATOR_FINISHED
>WHERE
>RDB$DB_KEY = :DB_KEY

This won't work.
1. The db_key is transient. The db_key of a view is not the same as the
db_key of the underlying table.
2. You should not be trying to update the primary key of the table.

>Insert SQL:
>
>INSERT INTO "View_MaintReq_Originator"(
> REQUEST_ID,
> PRIORITY,
> MAINTENANCE_TYPE,
> ASSET_NUMBER,
> ASSET_DESC,
> REQUEST_SHORT_DESCRIPTION,
> ORIGINATOR_COMMENTS,
> ORIGINATOR_FINISHED)
>VALUES (
> :REQUEST_ID,
> :PRIORITY,
> :MAINTENANCE_TYPE,
> :ASSET_NUMBER,
> :ASSET_DESC,
> :REQUEST_SHORT_DESCRIPTION,
> :ORIGINATOR_COMMENTS,
> :ORIGINATOR_FINISHED)
>
>Delete SQL:
>
>DELETE FROM "View_MaintReq_Originator"
>WHERE
>RDB$DB_KEY = :DB_KEY
>
>Autogenerated keylinks:
>
>"View_MaintReq_Originator".RDB$DB_KEY

As Lester explained, IBO will use the db_key as a last resort if

(a) the statement queries a table that has no primary key (which is always
the case with a view);
and
(b) you don't supply any keylinks.

You DO need to supply the keylinks. Assuming your view returns unique
Request_ID's, you should make that the KeyLink for your view.

>The Edit and Insert fields are the same as for the select query
>except for the 3 date fields.
>Request_ID is inserted via a generator on the table (before insert).
>Both ORIGINATION_DATE and LAST_UPDATED have defaults of 'Today' in
>the table and are omitted from the Insert and edit SQL (otherwise
>they are not set to their defaults if included in the view (see
>previous post)).
>The LAST_UPDATED date will be set programatically when the record is
>updated. The ORIGINATOR_COMPLETED_DATE will be set based on boolean
>field ORIGINATOR_FINISHED, when the maintenance request is finalised.
>The ORIGINATOR_FINISHED field is a domain of type BOOL (T or F).
>I mention these later aspects in case they have any bearing on the
>problem.
>There is a complemantary view for the maintenance personnel (rather
>than the maintenance requestor/originator) but have not finalised
>this query as yet because of the current problems with this view.
>
>After completeing the query, attempting to activate the query
>results in the "Invalid Keylinks Error". Deleting the autogenerated
>Keylinks, hitting OK and then activating the query appears to work
>(no error and the program seems OK) but if one returns to the query
>and tries to change anything (such as hitting prepare dataset whilst
>in the Column Attributes tab (the column attributes are blank at
>this point)) one again gets the keylinks error. Deleting the keylink
>and again setting active to true works but the column attributes
>dissappear (although I must say I haven't set any column attributes).
>
>Once again, any ideas? No problem with any further info if required.

There is plenty of info here now.

To summarise:

1) Set the KeyLinks yourself. KeyLinksAutoDefine is effective only for
single-table queries on a table that has a primary key. If your view
doesn't return unique rows on the table's primary key column on its own,
supply enough columns to be able to uniquely identify each row in the
set. (In this case, it appears that the primary key of the single
underlying table will be sufficient.)

2) Review your XxxxSQL statements. UpdateSQL should never try to update
the primary key. Make your statement specify updating only fields that are
going to be changed (and are allowed to be changed) by the user. In views,
if you change the primary key value (or try to) you will make the set invalid.

3) If it can be avoided, I would also recommend NOT defining database
objects with double-quoted identifiers. Apart from the fact that it's a
royal PITA to try to remember which ones you defined with quotes and which
not, in IBO you have the potential to bump into exceptions based on length
limitations, since IBO internally sets some names using the db identifiers
with various prefixes....

4) And - for maintainability - avoid SELECT *. Enumerate the column
identifiers and then you will always know what you are working
with. Tip: you can get a column list for anything, right inside the IDE,
by opening the editor for a TIB_Query or (if not using TIB_Query) via the
IB_SQL instance that is provided by double-clicking on your connection object.

Helen