Entity Framework migration script not working with firebirdsql

Question

I'm using EF Migrations with a Firebirdsql Database and it works fine just fine while I update databases using the PackageManagerConsole in VS.

If I use

Update-Database -Script

and then try to run the script I get an error.

After a bit of testing I think that it breaks with this sentences

EXECUTE BLOCK
AS
BEGIN
    if (not exists(select 1 from rdb$generators where rdb$generator_name = 'GEN_IDENTITY')) then
    begin
        execute statement 'create sequence GEN_IDENTITY';
    end
END
;

CREATE OR ALTER TRIGGER "ID_Users_Id" ACTIVE BEFORE INSERT ON "Users"
AS
BEGIN
    if (new."Id" is null) then
    begin
        new."Id" = next value for GEN_IDENTITY;
    end
END
;

If I try to run it returns

ISC ERROR MESSAGE: Dynamic SQL Error SQL error code = -104 Unexpected
end of command - line 6, column 21

Could be a bug with the provider. Are you using a current version? Not familiar with firebird syntax, but maybe play with the semicolon placement near the error message. Similar unanwered: http://stackoverflow.com/questions/30781748/ef6-mysql-update-database-script-generates-sql-without-semicolon — Steve Greene, Jun 21 '16 at 13:29
Yes. The script generation is not handling the `SET TERM` statements. Feel free to add some test-case to tracker. Not sure how I will plug it all in, because the execution shares similar path and there it's per statement. — cincura.net, Jun 21 '16 at 19:51

score 1 · Accepted Answer · answered Jun 21 '16 at 15:15

Firebird expects a single statement to be executed, so most tools (eg ISQL, flamerobin) will split on the semicolon and send the separate parts as statements. This fails in this case because then the following statement will be executed:

EXECUTE BLOCK
AS
BEGIN
    if (not exists(select 1 from rdb$generators where rdb$generator_name = 'GEN_IDENTITY')) then
    begin
        execute statement 'create sequence GEN_IDENTITY'

And this is clearly incomplete, and hence a Unexpected end of command error is raised. To address this problem, most Firebird tools expect a set term instruction to switch to a different statement terminator (eg # instead of ;). Set term itself is not understood by Firebird server.

The way entity framework (or the Firebird Entity Framework support) generates the statements (as shown) cannot be used by these tools.

For example your script should look something like:

set term #;
EXECUTE BLOCK
AS
BEGIN
    if (not exists(select 1 from rdb$generators where rdb$generator_name = 'GEN_IDENTITY')) then
    begin
        execute statement 'create sequence GEN_IDENTITY';
    end
END
#

CREATE OR ALTER TRIGGER "ID_Users_Id" ACTIVE BEFORE INSERT ON "Users"
AS
BEGIN
    if (new."Id" is null) then
    begin
        new."Id" = next value for GEN_IDENTITY;
    end
END
#
set term ;#

You didn't specify which version of Entity Framework and the Firebird Entity Framework driver you are using. I'd suggest you first try upgrading the driver. Otherwise I suggest you file a bug at http://tracker.firebirdsql.org/browse/DNET but I'm not sure if this is a problem that can be fixed there (it might also be how Entity Framework itself generates the script).

Sorry, been away all day from computer till now. I'm using EF 6.1.3 and last version of the FirebirdClient (updated today from 5.0.0 to see if it was related). I'll test this tomorrow and let you now. — MaLKaV_eS, Jun 21 '16 at 20:51

Entity Framework migration script not working with firebirdsql

1 Answers1