Vote for Firebird in Sourceforge Awards

Firebird is a finalist in four categories of the Sourceforge
Community Choice Awards for 2007.

In each category the Firebird project is up against 9 other
projects all selected for excellence from hundreds of thousands of SF
projects. Categories are Best Tool or Utility, Best Project for the
Enterprise, Best User Support and Best Project Overall.

Voting is open until July 20 so take this opportunity to credit
Firebird with your recognition. Full details and links here:

http://sourceforge.net/community/index.php/landing-pages/cca07/

Nice Function Added in Firebird 2.1 (beta)

Here is the list of built in function in Firebird 2.1 (now, it's still beta). My favourite function:

String function: LPAD, RPAD, LEFT, RIGHT, POSITION, REPLACE, REVERSE

Date function: DATEDIFF, DATEADD

Numeric function: ROUND, TRUNC, ABS, MIN_VALUE, MAX_VALUE, GEN_UUID, HASH

So with this new built in function, my previous article can be achieve with more short statement like this:

select current_date,
right(extract(year from current_date), 2) ||
right('0' || extract(month from current_date), 2) as Year_Month
from rdb$database

But in my opinion it would be nice if Firebird team extend the capability of SUBSTRING function like this: the "length" value can be negative so it can achieve the same result like RIGHT function with the capability of "start position". Example:

Substring('Hello World' from 9 to -3) will result "Wor"

FB Date Manipulation

Last week I have a question from Delphindo (Indonesian Delphi community) thorough YM conference. The question is how to make SQL query for Firebird to obtain the same result as we do this (to_char(sysdate, 'YYMM');) in Oracle. I must admit that Firebird doesn't have this "to_char" built in function, but we can create the same result with the trick like this:

select current_date,
substring(extract(year from current_date) from 3)||
case
when char_length(extract(month from current_date))=1
then '0'||extract(month from current_date)
else
extract(month from current_date)
end as Year_Month
from rdb$database

Thank's to the new feature of Firebird 2, that can make this happen without installing any UDF. I use the "char_length" and "substring" built in function that come in Firebird 2 (version 1.5xx still not support this built in function).

Firebird Date Literals

Today I go to firebirdnews and found that this article is good and it may guide you to know more about FB date time. Here is the link

Firebird 2.1 is in Alpha state

Here is the link of release note FB2.1Alpha. Wow it's time to try.
Some cool feature:

1. Use of domains for Trigger/SP variable definition (SQL Server or MySQL doesn't have it (CMIIW))
2. Global temporary tables
3. Common table expressions, recursive queries
4. Database Triggers
5. Database Monitoring via SQL
6. Admin information via select
7. Get ODS Version and Dialect via SQL

Backup in Firebird

There is nice article in here that explain a lot of things about backup database in Firebird (including command reference). This article is lack of explanation about Firebird new incremental backup (but you can find about this incremental backup on your Firebird 2 release notes). Hope you like it.

Firebird 2.0.1 and ZeosDBO 6.6.1 beta

Yeah I know it's late to let you know thorough this Blog that Firebird 2.01 and ZeosDBO 6.6.1 beta is released, but if you don't know then I hope this little blog can let you know more.

Firebird 2.0.1 is only bug fixed and there is nothing new feature.
ZeosDBO 6.6.1 beta is only bug fixed and there is nothing new feature.

Recomended Page Size in Firebird

If you using Firebird database then the best setting for id set the page size to 4096 and use SQL Dialect 3

FB2.0 New Interesting Feature

Today, I just know after reading my email from firebird-support that show the new feature of FB2.0 the complete article can be found here but that article is not in English :( (If someone here know what language in that site is used, please tell me because I want to translate it using Altavista Babel fish) I just read the example from that article and try it. The one that I like, is the new function on ODS 11 and it can be used like this:

select rdb$get_context('SYSTEM', 'NETWORK_PROTOCOL') as protocol
    , rdb$get_context('SYSTEM', 'CLIENT_ADDRESS') as client_address
from rdb$database;

In ODS 11 we have the built in UDF rdb$get_context and rdb$set_context you can read more information from the doc directory that come with FB2.0 the file is:

"README.context_variables2.txt"

Upgrading Firebird 1.5 to 2.0

Yeah, last week I try to install the new Firebird 2.0. And it's easy to migrate the old ODS 10.1 (FB 1.5) to ODS 11 (FB2.0) what you need to do is:

1. Uninstall Firebird 1.5.3 (if yu wish not to use it again)
2. Install the Firebird 2.0
3. Backup your old database with this new gbak from FB2.0
4. Restore them back with gbak from FB 2.0
5. Now you have the new database with ODS 11

Hope this little article can help you :)

Firebird 2.0 is Out, what about me?

On 13 November 2006 Firebird 2.0 is launch on the opening of Firebird Conference. I already download it, but I'm still using version 1.5.3 for my real application and just try version 2.0 in my research computer. Why? because I still wait for the version 2.1 =) As you know usually the early version still have a bug or the feature that is not yet complete. But if you are a new Firebird user then you must download this 2.0 and evaluate it. I still wait for the roadmap of the Firebird development that usually out after the Firebird conference to see what feature that I neeed most for my current project. It's happy to see that this community so active

ReInstalling Component That Using ZeosDBO 6.6 beta

Today I try again to ReInstall my components that using ZeosDBO. I want to try this new beta version (version 6.6 beta) so I must clean up my past ZeosDBO 6.1.5. The difficulty that I found when intalling version 6.6 beta is that the seacrh directory and the output directory (from the ZeosDBO.bpg) must be set first (so It's not easy installation for Delphi user that just learn how to install Delphi components), I think they will fix this later for stable version (as they did for 6.1.5 version).

The point is this new version has a simple bpl & dcp created. Before version 6.6 there is many bpl & dcp respectively for the "ZComponent", "ZCore", "ZPlain" and others. But in this 6.6 version the bpl & dcp is only "ZComponentDesign.dcp" and "ZComponentDesign70.bpl" so I must delete the requires dcp for my components and replace with this new dcp. That's all and everythings goes well (of course after I fix the property "RequestLive" with "ReadOnly")

Explicit Cursor in Firebird PSQL

Explicit cursor is a new this in Firebird because it's only available in version 2. We can found this syntax in the release notes on Stored Procedure Language (PSQL) section. The example below is taken from that release notes

DECLARE RNAME CHAR(31);
DECLARE C CURSOR FOR ( SELECT RDB$RELATION_NAME FROM RDB$RELATIONS );
BEGIN
 OPEN C;
 WHILE (1 = 1) DO
 BEGIN
   FETCH C INTO :RNAME;
   IF (ROW_COUNT = 0) THEN
     LEAVE;
   SUSPEND;
 END
 CLOSE C;
END 

Implicit Cursor in Firebird PSQL

Firebird has an implicit cursor, we can found the example in the example database employee.fdb, and we can find in the "DEPT_BUDGET" stored procedure, lets look into the source, the implicit cursor has the syntax:

FOR [select statement] into [local variable that hold the value from select]
DO
BEGIN
 [your statement]
END

The local variable is must defined for each of the field that selected and remember the datatype must be the same

Sweeping

Sweeping is NOT just organized garbage collection. What sweeping seeks to do is to move the Oldest Interesting Transaction up, and reduce the size of transaction masks. It does so by changing rolled back transactions to committed transactions.

"What!!!", you say. "The woman is nuts."

But that's what a sweep does. It removes all the changes made by a rolled back transaction then changes it state to committed. (Remember we agreed earlier that a read-only transaction that rolled back could be considered committed for all the harm it did. Remove the damage, and its safe to consider the transaction committed.)

At the same time, sweep garbage collects like any other transaction.

A new thread is started and sweeps the database while everybody else goes about life as normal. Well, more or less normal, where the less is the amount of CPU and I/O bandwidth used by the sweep.

Taken from IBPhoenix

Oldest Active Transaction

This one sounds easy - but it's not. The oldest active transaction is not the oldest transaction currently running. Nor is it the oldest transaction marked Active in the TIP. (Alas). It is the oldest transaction that was active when the oldest transaction currently active started. The bookkeeping on this is hairy and I frankly don't remember how it was done - now I do -, but that's the rule, and it does work.

Any record version behind a committed version created by a transaction older than the oldest transaction active when the oldest transaction currently active started is garbage and will never be needed ever again.

That's pretty dense. Lets ignore the commit/rollback question briefly.

Simple case: I'm transaction 20 and I'm the only transaction running. I find a record created and committed by transaction 15. I modify it and commit. You are transaction 25, and when you start, you are also the only transaction active. You read the same record, recognize that all active transactions can use the version of the record created by me, so you garbage collect the original version. In this case, your threshold for garbage collection (aka Oldest Active) is yourself.

Harder case: You continue puttering around, modifying this and that. Another transaction, say 27 starts. You are its oldest active. It too can modify this and that, as long as it doesn't modify anything you modified. It commits. I start a transaction 30. You are also my oldest active transaction, and I can't garbage collect any record version unless the newer version is older than you. I run into a record originally created by transaction 15, modified by transaction 20, then modified again by 27. All three of those transactions are committed, but I can garbage collect only the original version, created by transaction 15. Although the version created by transaction 27 is old enough for me, it is not old enough for you, and being cooperative, I have to consider your needs too.

Hardest case: I'm transaction 87, and when I started, all transactions before 75 had committed, and everybody from 75 on was active. Transaction 77 modifies a record, created originally by transaction 56. I continue to read the 56 version. All is well. Transaction 77 commits. You are transaction 95. When you start, I, number 87, am the oldest active. You read the record created by 56 and modified by 77. You can't garbage collect anything in that record because I can't read records created by any transaction newer than 74.

Maybe you know now why descriptions of the oldest active tend to be a little peculiar.

Taken from IBPhoenix

Oldest Interesting Transaction

To recognize which record versions can garbage collected, and which updates are rolled back and can be ignored, every transaction includes a "transaction mask" which records the states of all "interesting" transactions. A transaction is "interesting" to another transaction if it is concurrent - meaning that its updates are not committed, or if it rolled back - meaning that its updates should be discarded, or if it's in limbo.

The "transaction mask" is a snapshot of the states of all transactions from the oldest interesting, to the current. The snapshot is made when the transaction starts and is never updated. The snapshot depends on the number of transactions that have started since the oldest interesting transaction.

Taken from IBPhoenix

Garbage Collection

Garbage collection prevents an update-intensive database from filling up with unnecessary back versions of records. It also removes record versions created by transactions that rolled back. Every transaction participates in garbage collection - every transaction, including read-only transactions.

When a client applicaition reads a record from a Firebird database, it gets a record that looks like any record from any database. Two levels lower, somewhere in the server, InterBase/Firebird pulls a string of record versions off the disk. Each version is tagged with the transaction id of the transaction that created it. The first one is the most recently stored. At this point, the server has two goals: 1) produce an appropriate version of the record for the current transaction 2) remove any versions that are garbage - either because they were created by a transaction that rolled back or because they are so old that nobody will ever want to see them again.

Taken from IBPhoenix

Garbage

Firebird is a multi-generational database. When a record is updated, a copy of the new values is placed in the database, but the old values remain (usually as a bytewise difference from the new value). The old value is called a "Back Version". The back version is the rollback log - if the transaction that updated the record rolls back, the old version is right there, ready to resume its old place. The back version is also the shadow that provides repeatable reads for long running transactions. The version numbers define which record versions particular tranasctions can see.

When the transaction that updated the record commits and all concurrent transactions finish, the back version is unnecessary. In a database in which records are updated significantly and regularly, unnecessary back versions could eventually take up enough disk space that they would reduce the performance of the database. Thus they are GARBAGE, and should be cleaned out.

Taken from IBPhoenix

Transaction States

Transactions have four states: active, committed, limbo, and rolled back.

Taking these cases in order from the least complex to the most:

Limbo: A transaction that started a two-phase commit by calling the Prepare routine. The transaction may be alive or not. At any point, the transaction may re-appear and ask to commit or rollback. Changes it made can neither be trusted nor ignored, and certainly cannot be removed from the database.

Committed: A transaction is which completed its activity successfully. Either A) it called Commit and the commit completed successfully, or B) it called Rollback but made no changes to the database, or C) it called Rollback and its changes were subsequently undone and its state changed to Committed. This transaction is finished and will never be heard from again, and its remaining changes are now officially part of the database.

Rolled back: A transaction which either: A) called Rollback and requested that its changes be removed from the database, or B) never called Commit so was marked as Active, but discovered to be dead by another transaction which marked it as rolled back. In either case, changes made by this transaction must be ignored and should be removed from the database.

Active: A transaction which: A) hasn't started. B) has started and hasn't finished. C) started and ended without calling any termination routine. (e.g. crashed, lost communication, etc.)

Taken from IBPhoenix

The Firebird Conference 2006

The fourth Firebird Worldwide Conference will take place at the Andels Hotel in Prague, Czech Republic from the 12th November 2006 until 14th November 2006. The Andels Hotel is a new 4-star hotel, which is located very close to downtown, just across the river. Room tariff is from €99.00 (Euros) per night. More info here

Firebird Developer Day 3

Firebird Developer Day 3 (FDD 3) will have 3 international speakers Holger Klemt (IBExpert), Alexey Kovyazin (IBSurgeon/IBDeveloper magazine) and Ann Harrison (IBPhoenix). It will held on Piracicaba-SP Brazil, at 29 July (whole day). Online subscriptions (this site is using portuguese language)

I think in Indonesia we still don't have event like this hehehe

New Business Model for IBDeveloper Magazine

Yes, IB Developer magazine issue 4 is out yesterday. But now it's not "free" anymore. It means they have different business model for this magazine. The "free" here is mean we can read some article from that magazine for free but there is some bonus article that we must pay for it. Of course they do that because of the financial problem, until now they don't have big sponsor for this magazine. Perhaps some company in this world can help them to make the IB/FB community more bigger. You can find their site is in my links in this blog. Here is some words that I take from the IBDeveloper site

"We’ve come to realise that producing the magazine with only advertising and sponsors to support it is impossible. We’ve decided to change our business model slightly. We are going to run special proprietary editions of each issue with bonus articles. These special editions will be available in electronic (printable PDF) and paper versions. From this issue forward, the latest issue will be available only for purchase for a period—say one or two weeks from publication—and will be fully released afterwards for everyone, minus the bonus articles."

FB Selectable Stored Procedure

The stored procedure (SP) in Firebird can be used as "view/table", it means we can make not only executable SP but selectable SP. The selectable SP can return multiple record as you can do the same with view/table. The things that can make it happen is the keyword "SUSPEND". The simple example is:

CREATE PROCEDURE GET_EMP_PROJ(
 EMP_NO SMALLINT)
RETURNS (
 PROJ_ID CHAR(5))
AS
BEGIN
 FOR SELECT proj_id
     FROM employee_project
     WHERE emp_no = :emp_no
 INTO :proj_id
 DO
   SUSPEND;
END

The above example can be found on the employee example database when you install firebird. The "INTO" keyword must be followed by the return variable (declare in the header of the SP) then we must put the keyword "SUSPEND" after that. The advantage of this selectable SP is we can make it more complex that include some business rule before we return the value that can be selectable, and we can also has a parameters to output different result based on that parameters. As I know there is no much database that has this ability (I only know Oracle, Firebird/Interbase that can do that, perhaps PostgreeSQL).

We can make this selectable as the usual table, it means we can join it with the other table, view or selectable SP. Before Firebird version 2, we often used it as the solution to make derived table as we know before version 2 the derived table is not supported in FB.

Installing ZeosDBO

There is some people ask me about installing this component, although there is a file named install.html but maybe the instruction in that file not really clear. Here is the installation guide for ZeosDBO 6.1.5 in Delphi 7 (you can do the same way with the other version of ZeosDBO and Delphi)

1. Unzip the .zip file of ZeosDBO and copy into your Delphi directory like this:
[Delphi_Directory]\ZeosDBO6.1.5

2. Goto directory: [Delphi_Directory]\ZeosDBO6.1.5\package\delphi7
(if your Delphi version is 5 then goto "delphi5")

3. Double click the ZeosDbo.bpg and then it will opened in your Delphi program

4. Click View->Project Manager menu on Delphi

5. Right click your mouse on one of the .bpl that appear on Project Manager, and then click "Build All From Here"

6. Click the ZComponentDesignXX.bpl on Project Manager, right click mouse and choose "Install"

7. Goto "Tools->Environment Option" of your Delphi's menu and then choose the Library tab

8. On the "Library path" click the elipsis button (browse), type:
$(DELPHI)\zeosdbo-6.1.5\packages\delphi7\build
And then click the add button, then click OK button to finish it

9. Your Zeos component are ready to use

If you already have the previous version of ZeosDBO then before start the 9 steps above, you must delete all the ZXXX.bpl and Zxxxx.dcp files from the [Delphi folder]\Projects\Bpl

FB Partial Select

Firebird has capability to get only N data from database. The syntax is like this:
"SELECT [FIRST N] [SKIP M] * FROM [TABLE/ VIEW/ STORED PROCEDURE]"

For example, if we want to get the 5 top employee by their salary then we can do
"SELECT FIRST 5 * FROM EMPLOYEE ORDER BY SALARY DESC"

The "skip" keyword is functioned to skip M data from the top of data that we retrieve through query. For example if we want to get the runner up position for some contestant we can do
"SELECT FIRST 1 SKIP 1 FROM CONTESTANT ORDER BY SCORE DESC"

Indonesian Delphi Blog

The Indonesian Delphi community already has a blog for discussion about Delphi programming language, but not only Delphi, it also discuss about database, programming technique or Delphi component related to Delphi programming. The URL is http://delphindo.wordpress.com

You also can be a contributor in this Delphindo (the name for Indonesian Delphi community) blog by sending email to the moderator to let them know that you want to be a contributor, then they will give you a hint how to be a contributor (because I am not a Delphindo blog moderator so I don't know the detail, but if one of the moderator read this article, feel free to add a comment how to be a contributor to Delphindo blog)

Indonesian Delphi Community

I want to introduce one active community for Delphi (one of the programming language). There is delphindo@yahoogroups.com , this community is for Indonesian people so it use Indonesian language. Feel free to join and expand your knowledge.

World Class Firebird Support

I want to introduce you an official mailing list for Firebird support. That is firebird-support@yahoogroups.com , through this mailing list you can get support for many people arround the world (including the FB developer and Helen Borie (the author of the first Firebird Book)). Of course this mailing list using an english language. This is also very active mailing list that you need to.

ZeosDBO setting for Firebird & ADO

I want to share the setting that I always use in my program to connecting my Delphi with Firebird database through ZeosDBO 6.1.5. First the properti TransactIsolationLevel of TZConnection is set to tiReadCommitted, then choose firebird-1.5 from protocol property. And the rest is as usual (set the "hostname", "database", "user" and "password" property of TZConnection). Please be carefull for the "user" and "password" property of TZConnection because it doesn't encrypt the value so people may read in your .exe program (this is also the lack of this component). The "database" property can be filled by the name of the Firebird database alias or the directory where the Firebird database is exist. The "hostname" property is the computer name (or IP address) where the database is exist. After that you can try to connect to Firebird database.

The one that special for this component is that you can connect through ADO using "protocol" ADO and then you can select the database through the "database" property with the user interface that is the same when you use the TADOConnection.

Firebird locking

This problem is always asked by beginner. In Firebird there is one jargon that called "MGA (multi generated architecture)", this MGA can make reader not blocking writer. It means if we only select data from database to read then the others can do update the same data on that table without locked by the reader. What about if 2 user update the same of data? The last that commit data is the winner (if only update data except primary key), but if the update include updating the primary key then the second user will fail because the old primary key is invalid. There is many article that you can found in internet about the keyword MGA here. I hope this simple explanation can show you why locking in Firebird doesn't necessary. But if you want locking, then Firebird support record locking by adding the keyword "WITH LOCK" like this:
"select * from employee where id>30 with lock"

You can found a lot of explanation about that explicit locking by reading the release notes of Firebird 1.5.3 page 33

ZeosDBO

I want to introduce one component that I usually use in most of my program. That's it ZeosDBO component (http://zeosforum.net.ms/). It is a component to make connection to database directly (through database API) and it support many of database like MS SQL Server, Oracle, Interbase, Firebird, Postgress, MySQL, Sybase and also support for ADOConnection. This component offer single interface to various connection to database (like ADO or DBExpress) so we can change the database source without changing our code.

One that I like from this component is, it's free, open source and can run on Kylix (the Linux version of Delphi) and also ported for Lazarus. But maybe in this time the development of this component is going slowly, it's because this development need more serious people to develop, and from this blog I want to invite you to join to develop this good component. I feel pitty if this good component become dead component.

Dummy table

In the Firebird or Oracle world there is a dummy table that we often used it to get result from a function or expression, and the result is only 1 record. For example we use this dummy table for get a value from generator/sequence (see in my previous blog that talk about generator). In Firebird the dummy table is called "rdb$database" where is in Oracle, it is called "dual". Maybe the MS Access or MS SQL Server users will see this is rather odd (like FB/Oracle users that see "select multiply_result=2*3" in MS SQL Server)

Generator (FB) vs Sequence(Oracle)

In this comment I will compare a featured called "generator" in Firebird (FB) with "sequence" in Oracle. Both have the same function that for generate an automatic number. FB give us an easy way to reset or change the value of generator through the "gen_id(generator_name, increment_value)" function.
Example: "select gen_id(gen_tb_barang, 1) from rdb$database"

This "gen_id" function is also very flexible that we can reset the generator value to zero.
Example: "select gen_id(gen_tb_barang, -gen_id(gen_tb_barang,0)) from rdb$database"
And we can also get last generator value with this SQL:
Example: "select gen_id(gen_tb_barang, 0) from rdb$database"

We can get the value of sequence in Oracle with "select sq_tb_barang.nextval from dual". The "nextval" method is increment by 1 by default, we can change the increment step by this SQL command "alter sequence increment by ".

To reset sequence to become zero in Oracle is more complex, we can drop that sequence and then create it again for simple but other say this (although I never success with this procedure to make my sequence become zero):

The logic is:
1) get the curval from the sequence
2) alter the sequence to have increment as the curval * -1
3) get the nextval from the sequence
4) alter the sequnce to have increment as 1

The code is:

declare Handle Integer;
SqlText varchar2(2000);
Temp Integer;
Begin
select sq_barang.nextval into temp from dual;
temp := temp * -1;
SqlText := 'Alter Sequence sq_barang increment by ' || to_char(temp);
Handle := dbms_sql.open_cursor;
dbms_sql.parse(handle, SqlText, DBMS_SQL.NATIVE);
select sq_barang.nextval into temp from dual;
SqlText := 'Alter Sequence sq_barang increment by 1' ;
dbms_sql.parse(handle, SqlText, DBMS_SQL.NATIVE);
End;

Why using Firebird?

This statement is base on my experience, so maybe it's not true for you. Firebird version that I know for the first time is 1.0 version, since then I fall in love with Firebird (FB). Thanks for my friend that introduce this database for me (he is my senior in STTS and I was work from him as part time programmer when I was still in university). The first point that I like is the installation is quite simple and easy to understand. Second, it already has view, trigger & store procedure that using PSQL (the procedural language that similar with Oracle PL/SQL) , so I not spend to much time to understand FB. Third, FB is free and there is so much free tools that you can use, like IBExpert Personal Edition (one of the best tool for administrating FB) for free. Fourth, it has an active community that will help your probem at the FB Support mailing list (firebird-support@yahoogroups.com) . The 1.5.x version has more speed than 1.0.x version. Many people ask about the documentation of FB, yes it is still in development, the one that I use is the Interbase documentation and FB release notes , but now there is a book by Helen Borie that explain almost all thing that you want to know about Firebird (I already add this URL to this Blog's links). FB is perfect for small to midsize company (I suggest you to use 1.5.3 version for better performace), but I think it'll perform better after the 3.0 version that can use multiple processor (SMP). The lack of the 1.5.x version is that it doesn't support derived table and the poor index tree but both will be fixed in 2.0 version. If you have a comment please add in this blog

Firebird 1.5.3 is launched

Hello I want to let you know that version 1.5.3 is already launch and you can download it. Not a major version, just a lot of bug fixed. I recommened using this version, because this version is very stable (I already try it) compare with 1.5.0, but doesn’t improve to much if you already using 1.5.2 version.

This 1.5.3 version also include Release Notes in PDF that I think more complete version (including release notes from version 1.5.0 until 1.5.3)
Now version 2.0 is still in Beta 2 version. Hope this will be released in March.

For small to midsize company, I think this database will rock you. (Also available for Linux version)