Re: [Firebird-devel] Simplify MySQL migration: LAST_INSERT_ID()

[Roman R. <ro...@ro...>]

Tony,

> I had to convert a MySQL database to Firebird last year and created the
> awk script attached below to convert a MySQL SQL schema to the Firebird
> dialect. You may find this useful, as it is intended to take most of the
> grunt work out of the conversion.

Thanks! I will give it a try!

> It is invoked with "gawk -f convert.awk schema.sql" assuming that the
> script is saved in "convert.awk" and you are working on "schema.sql".
>
> I found two main problems with the conversion - "auto-increment" and
> "enumerated types" of which the latter is the more difficult to resolve,
> with several alternative strategies.

So far I did not see the enumerated types. Hopefully, they don't use them :)

> In the script, I dealt with auto-increment by creating a generator and
> an "insert before trigger". I did not have to deal with retrieving the
> last insert id. However, in the approach below, this should be easy
> enough to deal with using the Firebird USER_SESSION namespace. If you
> amend the Create Trigger part of the awk script to
>
>      print "CREATE TRIGGER AI_" tablename " FOR " tablename
>      print "ACTIVE BEFORE INSERT"
>      print "AS"
>      print "BEGIN"
>      print "  IF (new." auto_col " is NULL) THEN"
>      print "  BEGIN"
>      print "     Select GEN_ID(" generator ",1) FROM RDB$Database Into
> new." auto_col ";"
>      print "     rdb$set_context('USER_SESSION', 'lastinsertid', new."
> auto_col ");"
>      print "  END"
>      print "END^"
>
> then the last generated value will the saved in the user session. This
> can then be retrieved by a stored procedure such as
>
> Create Procedure GET_LAST_INSERT_ID
> RETURNS
>    (lastInsertID Integer)
> As
> Begin
>    lastInsertID = rdb$get_context('USER_SESSION','lastinsertid');
>    SUSPEND;
> END
>
> This has the correct semantic as MySQL as, quoting the MySQL Manual
> ,"The value of mysql_insert_id() is affected only by statements issued
> within the current client connection. It is not affected by statements
> issued by other clients. "

Hey, that's very nice solution to the issue! Thanks!

Roman