Google
 

Thursday, January 27, 2005

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=.

3 comments:

dhd said...

welcome to the wow gold, cheap WoW Power Leveling, service site,wotlk gold buy cheap wow gold,wow gold,world of warcraft power leveling buy wow gold

Adi said...

Oes Tsetnoc one of the ways in which we can learn seo besides Mengembalikan Jati Diri Bangsa. By participating in the Oes Tsetnoc or Mengembalikan Jati Diri Bangsa we can improve our seo skills. To find more information about Oest Tsetnoc please visit my Oes Tsetnoc pages. And to find more information about Mengembalikan Jati Diri Bangsa please visit my Mengembalikan Jati Diri Bangsa pages. Thank you So much.

MapleStory Mesos said...

Not long came across ones clause and have absolutely been meter reading on. I wish to expressage the appreciation of your writing attainment along with power to brand audience learn from the beginning on the goal. I'd like you just read modern blogposts and also to parcel our views with you.

Cheap Runescape Gold
Runescape Gold