Firebird - using merge statement in ZUpdateSQL.ModifySQL

[Zoran]

Windows 7, Firebird 2.5, Lazarus 1.6, FPC 3.0

I have a ZQuery1 with ZUpdateSQL1.
I want to use Firebird merge statement in ZUpdateSQL.ModifySQL.

Take a look at these statements:
1:

Code: Select all

update table1
set field2 = :field2
where id = :old_id

and
2:

Code: Select all

merge into table1 x
using (select :old_id as id, :field2 as f2 from rdb$database) y
on x.id = y.id
when matched then update set field2 = y.f2

These two statements are equivalent in Firebrid. They do exactly the same. However, if I put the second (merge) variant in ZUpdateSQL1.ModifySQL, the exception gets raised. This is what I get:

SQL Error: Dynamic SQL Error SQL error code = -804 Data type unknown. Error Code: -804. can't format message 13:196 -- message file C:\Windows\firebird.msg not found The SQL: merge into table1 x using (select ? as id, ? as f2 from rdb$database) y on x.id = y.id when matched then update set field2 = y.f2; .

It seems to me that ZUpdateSQL doesn't know how to substitute parameters with values.
Why? Shouldn't ZUpdateSQL just replace parameters with values and send the statement to database? With substituted values this merge statement works well (I checked in FlameRobin).

Of course, for the simple example I have shown here, in real application I would use the "update" variant, not "merge". But I have complex sql query and using merge is what I need there. This is just simple example which describes the problem.

[marsupilami]

Hello Zoran,

it would be good to have the full firebird error message. This part of the Exception message lets me thing that either your firebird server or you fbclient.dll can't access the firebird.msg:

can't format message 13:196 -- message file C:\Windows\firebird.msg not found

If you can't get that message - could you providfe a simple table structure and SQL statement that we can use to reproduce the case?

With best regards,

Jan

[EgonHugeist]

Hi Zoran,

there is no issue using a "merge" statement.
Your problem is the subselect:

Code: Select all

(select :old_id as id, :field2 as f2 from rdb$database) y

FireBird isn't able to prepare the statement because there is no type determination possible for "old_id" and "field2" which you also use in the match-condition. This is because you just want to make a "virtual" select for the using clause.

two solutions if you use such syntax:

First:
Concatate the SQL and do not use the two Parameters. This would work..

Second:
Zeos would need a upgrade to understand macro-inlined parameters. Look @ http://zeoslib.sourceforge.net/viewtopi ... cro#p36009 user alex75 already made such a approach. If i find the time i'll try to merge his Idea to 7.3-alpha.

What do you think?

[Zoran]

Thank you, Marsupilami and EgonHugeist.

@Marsupilami:

I'm afraid EgonHugeist is right - it is not Zeos bug, but some Firebird limitation - Firebird cannot prepare this statement.

Anyway, I'm attaching a simple application which shows the problem to this post (please just adjust database path in Database property of ZConnection1). The application uses this table:

Code: Select all

create table table1
(
   id int primary key,
   field2 int
);

commit;

insert into table1 (id, field2) values (1, 5);
insert into table1 (id, field2) values (2, 2);
insert into table1 (id, field2) values (3, 8);
insert into table1 (id, field2) values (4, 1);
insert into table1 (id, field2) values (5, 0);
insert into table1 (id, field2) values (6, 0);
insert into table1 (id, field2) values (7, 4);
insert into table1 (id, field2) values (8, 6);
insert into table1 (id, field2) values (9, 5);
insert into table1 (id, field2) values (10, 2);

commit;

Try to update field2 in table, the exception is raised.
However, if ZUpdateSQL1.ModifySQL is changed to

Code: Select all

update table1
set field2 = :field2
where id = :old_id

then you can update field2 without error.

@EgonHugeist:
For the first solution you give (concatate the SQL and do not use the two Parameters) I don't think that I can do that with ZUpdateSQL component. If I am wrong, would you please take a look at my simple application and try to make it work this way.

For the second solution, I agree with you that db engine should be used for preparing the statement. And now when I understand that this is not Zeos bug, but Firebird limitation and that db engine for some reason cannot prepare this statement, I will not ask you to apply the patch from your link, but I should try to find another way for what I need.

[EgonHugeist]

Hi Zoran,

might be true you'll fail using the TZUpdateSQL component. But you can use the TZQuery in this case.

Note that's not a FB issue only. For Postgre/MySQL i emulate such scenarios (because of a different arichtecture in DBC). All (except SQLite) to me known RDBM's do run into same issue.

I've been downloading your template. If i've time next days i'll check if i can give a better advice.