Migration from MySQL I.

Source: http://www.dotnetfirebird.org/blog/2005/01/migration-from-mysql-i.html

Why you should do that:

• stored procedures support
• views support
• transactions (these are also supported in InnoDB tables in MySQL)
• friendly open source license that allows commercial use and embedding for free
• embeddable (with a small runtime)
• hot backup

How to:

1) Autoincrement fields

There are no autoincrement fields in Firebird. You need to use a generator. It is a server variable that stores the last number used. You need to call it when inserting a new row:

• in an inserting stored procedure
• in a trigger

Given that we have a table

CREATE TABLE mytable (

 id INTEGER,

 mytext VARCHAR(20)

);

the generator would look like this:

CREATE GENERATOR GEN_MYTABLE_ID;

the trigger would look like this:

CREATE TRIGGER MYTABLE_BI FOR MYTABLE

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

 IF (NEW.ID IS NULL) THEN

 NEW.ID = GEN_ID(GEN_MYTABLE_ID,1);

END

and the inserting stored procedure like this:

SET TERM ^ ;



CREATE PROCEDURE SP_MYTABLEINSERT (

 MYTEXT VARCHAR(20))

AS

DECLARE VARIABLE ID INTEGER;

BEGIN

 ID = GEN_ID(GEN_MYTABLE_ID,1);

 INSERT INTO MYTABLE (ID, MYTEXT)  VALUES (:ID, :MYTEXT);

END^



SET TERM ; ^

2) NOW()

MySQL:

SELECT * FROM mytable WHERE mydate = NOW();

Firebird:

SELECT * FROM mytable WHERE mydate = CURRENT_TIMESTAMP;

There are three special variables for current date and time:

• CURRENT_TIMESTAMP (date and time, TIMESTAMP type)
• CURRENT_DATE (date, DATE type)
• CURRENT_TIME (time, TIME type)

3) LIMIT x, y (return first y rows starting at offset x)

In Firebird it looks like this :

SELECT FIRST y SKIP x * FROM mytable;

LIMIT x (take first 10 rows) looks like this:

SELECT FIRST x * FROM mytable;

See also http://www.ibphoenix.com/main.nfs?a=ibphoenix&l=;IBPHOENIX.FAQS;NAME=.