At 07:25 AM 19/03/2007, you wrote:

>Hi...
>
>I am with a problem.
>I have one tib_Query with the SQL:
>
>SELECT 'MAQUINA' TIPO, <-- 7 bytes
> COD_MAQUINA CODIGO,
> DESCRICAO,
> MARCA,
> COR
> FROM CTMAQUINAS
>
>UNION ALL
>
>SELECT 'IMPLEMENTO' TIPO, <-- 10 bytes
> COD_IMPLEMENTO CODIGO,
> DESCRICAO,
> MARCA,
> COR
> FROM CTIMPLEMENTO
>
>When it is executed occurs the following error:
>
>Problem in BindingCursor: Check KeyLinks property

I see two or three possible problems here - it depends on whether you
are using Firebird 2 or an earlier version.

1, as noted, the literal strings are different sizes. Prior to Fb 2,
this would cause a run-time exception from the server, because the
string field sizes previously were determined by the size of the
field in the first SELECT. Therefore, I think possibly you are using
Fb 2, which automatically adjusts the string to be the size of the
largest one.

2, it is not part of the legal syntax for a UNION query to assign
field names for expression fields in the second and subsequent
SELECTs. The first SELECT determines what the field names will
be. Although Fb and IB are usually tolerant of this mistake, it is
possible that IBO needs to be more strict about it.

Next, on the IBO side, the "binding cursor" is a cursor handle that
IBO opens on the underlying table sets for single-table, joined or
grouped sets so that it can find a specific row, or range of rows,
for positioned updates or deletes. For a TIB_Query, it actually
maintains two binding cursors, in order to make the set
bi-directional. For UNION sets (and selectable stored procedures)
there is no "underlying table" to which a cursor handle could be
obtained. IBO has no way to know which table was the source of any
record in your UNION set, since the API cannot provide that information.

3, So a likely source of the IBO error you are getting is that you
have set KeyLinksAutoDefine to true but there is no metadata
available from the server regarding the Primary Key - since there is
NO primary key. For most types of sets, IBO could use the RDB$DB_KEY
(IBO calls it DB_KEY) and map it to the binding cursor, but for union
sets the DB_KEY is not valid.

4, IBO knows about 3, so a set with no keys would still be OK
provided you keep RequestLive set to False. But another source of
trouble is that UNION ALL returns all rows from all SELECTs,
including duplicates. Your query will return duplicates if all of
the key fields from the underlying tables are not present in the
query. There will be NO way that IBO could set up the bindings in that case.

Can you make a UNION set updateable? Yes, for your set there is one
way, as long as the set contains all of the fields in the primary
keys of both tables. That is because, in your query, you know that
the TIPO field can provide the information about whether the record
came from CTMAQUINAS or CTIMPLEMENTO.

You can write one or more executable stored procedures, taking the
values of TIPO and *at least* the key fields as inputs, to perform
any DML operations you need. You must leave RequestLive False and
you must set the KeyLinks to include all of the fields that will be
mapped to the input parameters of the stored procedures. You will
need one parameterised EXECUTE PROCEDURE statement for each DML
operation you want to enable, placed in the appropriate XxxxSQL
property in each case.

For example, suppose you have a SP like the following:

CREATE PROCEDURE UPDATE_A_TABLE
(TIPO VARCHAR(10),
CODIGO BIGINT,
DESCRICAO VARCHAR(whatever),
MARCA Sometype,
COR Sometype)
....
AS
BEGIN
IF (TIPO = 'MAQUINA') THEN
BEGIN
UPDATE CTMAQUINAS
SET DESCRICAO = :DESCRICAO
WHERE CODIGO = :CODIGO
.... etc.
END
ELSE
BEGIN
...
END
END

then, in EditSQL, you would have

EXECUTE PROCEDURE UPDATE_A_TABLE (
:TIPO, :CODIGO, :DESCRICAO, :MARCA, :COR)

Helen