At 12:57 PM 6/03/2003 +0000, you wrote:

>Hi!
>
>I have a problem with statements of the following type:
>
>INSERT INTO T1(T1_PK, T1_Fld1, T1_Fld2, T2_FK)
>SELECT :pT1_PK, :pT1_Fld1, 'Const', T2_FK
>FROM T2 WHERE T2_Fld = :pT2_Fld
>
>The statements executes without error but there is no insert.
>RowsAffected is zero. I'm using the TIB_DSQL-Component (but with
>TIB_Cursor the effect is the same).
>
>The statements works fine, if primary key of T1 is not a parameter, i.e.
>
>INSERT INTO T1(T1_PK, T1_Fld1, T1_Fld2, T2_FK)
>SELECT Gen_ID(IDGen, 1), :pT1_Fld1, 'Const', T2_FK
>FROM T2 WHERE T2_Fld = :pT2_Fld
>
>I'm using Delphi 6.0 update pack 2 with Firebird 1.0 and IBObjects 4.2.I.
>
>Anyone has an idea what could be the problem?

You could possibly get the first one to "work" by assigning the
GeneratorLinks property of the statement.

However, the best advice I can give you is that this isn't a reasonable
usage of the INSERT...SELECT syntax. It's a server-side mechanism, for use
where you read the values for a new row in one set from *database* column
values in another set.

e.g.
INSERT INTO T1(T1_PK, T1_Fld1, T1_Fld2, T2_FK)
SELECT T2.aCol, T2.bCol, T2.cCol, T2.dCol from T2
WHERE T2.T2_Fld = :pT2Fld
...
ParamByName('pT2Fld').AsWhatever := SomeConstant;
...

It's true that you can make a SELECT statement consisting of all constants,
e.g.
Select 'alpha', 'beta', 'gamma', 'delta' from AnyTable
and so your second query would actually be accepted as long as you could
guarantee that it would pass compatible constants. However, this is just
another example of where, just because you *can* do something, it doesn't
mean that it's a reasonable way to do it.

If you need to, you could include a constant in one or more columns; but,
where you are using constants for all columns (as here), INSERT...SELECT is
not reasonable SQL, since it is not reading a server-side set.

If you are applying all constant values from the client side to an INSERT,
you should use the normal INSERT syntax, e.g.

INSERT INTO T1(T1_PK, T1_Fld1, T1_Fld2, T2_FK)
VALUES (:val1, :val2, :val3, :val4)

and apply the constants to the parameters to the prepared statement, prior
to the operation, e.g.

params[0].AsInteger := Gen_ID(IDGen, 1); // but a trigger or GeneratorLinks
// would be
better
params[1].AsString := T1.FieldByName('T1_Fld1').AsString;
... and so on

(Sorry if got some of your T1s and T2s muddled up here, but you get the
idea...)

Helen