Firebird with Delphi power

ZeosDBO 6.6.6 is Out

2010-01-05T05:24:00.000-08:00

The Zeos Development Team announce the reelase of ZeosDBO 6.6.6 -stable and the alpha version of ZeosDBO 7.0. The maintenance version of ZeosDBO 6.6.6 already include the bug fix of the Firebird CTE (Common Table of Expression) bug that I already describe earlier in this blog. So hope this new year update from Zeos Development Team can be a new year gift for us. Happy New Year 2010 everyone!!!

Fixed CTE bug with ZeosDBO

2009-12-21T23:47:00.000-08:00

Today I check my email and I receive an email from Mark Daems (MVP of Zeos Component) and he inform me that the bug related with CTE (Common Table of Expression, the new Firebird 2.1 feature) already fixed, and I'm so happy that I can now use CTE feature with Zeos component without any problem. If you also use Zeos component then here is the fix:

Open ZAbstractRODataset.pas and make this patch and then recompile ZeosDBO.bpg:
{ Initializes field and index defs. }
- InternalInitFieldDefs;
+ if not FRefreshInProgress then
+ InternalInitFieldDefs;

if DefaultFields and not FRefreshInProgress then
CreateFields;

The (-) sign is the row of code that deleted and the (+) sign is the new one that added. The bug that I already found is in here.

Firebird Configuration Tool

2009-05-21T04:44:00.000-07:00

Do you always make a change in Firebird database configuration setting? This nice & handy tool will safe you if you're a beginner or perhaps want to have more organize tool to edit the Firebird database configuration. That tool for me is more convenient than edit it manually with my favourite editor Notepad++.

What is Firebird database configuration setting? it's a "firebird.conf" file in your Firebird installation directory, it's a ordinary text file with some setting that effect the behaviour of your Firebird database. So thank's to Jhonny Suárez for his great contribution tool for Firebird communities.

Time to switch to ZeosDBO

2009-03-10T02:18:00.000-07:00

Long time I not write in this blog. After I read in FirebirdNews.com that said IBObject (a component to direct access to Firebird/Interbase) is no longer free, it is due to the author problem that said in this IBObject support list. So why not switch to ZeosDBO that for long time ago always open source product and free. Now Zeos still in development in version 7 that will support BDS 2009 and the snapshot already can be used for experimentally. Here is the link to the Zeos forum

count(*) VS count(somefield)

2008-11-19T02:37:00.000-08:00

What is the difference between "select count(*) from sometable" and "select count(somefield) from sometable"? The answer is simple, the "count(*)" will count all the record on that table. The "count(somefield)" will count all the record which is that field is not null. So the result of that 2 query may different. Example:
I have a table foo which has the record like this:

id name
1  XXXXX
2  YYYYY
3
4

select count(*) as cntall, count(name) as cntname from foo
The result of the above query is:
cntall cntname
4      2

The Future of Zeos

2008-09-14T18:45:00.000-07:00

Here is the link about the future development of Zeos. Thank's Mark for the news :) also I hear from Mark (ZeosLib Most Valuable Person) that he receive invitation to attend in Firebird Conference!! it's look that Zeos get famous among Firebird user that use Delphi or Lazarus. It's a great news, you must attend Mark :) we support you.

08.08.08 Version Phenomena

2008-08-10T18:03:00.000-07:00

08.08.08 is a lucky number in Chineese tradition as in the opening of the Beijing Olympic 2008. But also many software that release it stable version on that day :) for example here is my favourite software that has 08.08.08 release version:
1. ZeosDBO (look at my previous posting)
2. CnPack IDE Wizards (open source Delphi add-on like GExpert)
3. IBExpert Personal Edition (free version of IBExpert, the famous Firebird GUI management)

If you also use that software then probably you want to check out it's new version :) Happy download and wish Beijing Olympic 2008 held with success.

ZeosDBO 6.6.3-stable is Released

2008-08-08T01:00:00.000-07:00

Today (the same day with the opening of the Beijing Olympic Games 2008) ZeosLib version 6.6.3 stable is officialy release. The list of the download files (the component itself and the documentation) can be found here

Thank's for this great components. Bravo ZeosLib Development Team!!!

ZeosDBO 6.6.3-stable Snapshot is Out

2008-07-31T02:17:00.000-07:00

Hi, there is a good news :) finally zeos version 6.6.3-stable is out (the snapshot/draft version)!! You can download it here The official release maybe will released on 8-8-2008 (Opening of the Beijing Olympic :D). For now there is some documentation work on it.
Now Zeos can work with Firebird 2.1, just use the 2.0 protocol to connect :)

Zeos is Now Ready for FB 2.1.1

2008-07-17T18:51:00.000-07:00

Yippi, yesterday I try to convert my old FB 2.0 database to Fb 2.1.1 version. Then use Delphi with Zeos version 6.6.2-rc to connect it (with the old Firebird-2.0 protocol) and... magic.. it's now connected without problem.

The first FB 2.1 version has a problem when attempt first connect to the database but the version 2.1.1 seem already fix that bug, as in the Zeos forum "Seawolf" analyze that "(CORE-1868) The server could crash in isc_dsql_free_statement(). fixed by A. Peshkof" is the problem that make Zeos crash at first connection to the DB. Here is the link to the discussion about this problem in Zeos forum. So if you want to use the Firebird 2.1 version with Zeos then move to Firebird version 2.1.1

Firebird 2.1.1 is Released

2008-07-16T19:12:00.000-07:00

The first update release of the Firebird database version 2.1 is released now. From the documentation about the bugs fix that I read is that many bugs fix that back ported from version 2.5. Here is the link to the download section of Firebird 2.1.1. As I try at this moment of this writing article, the link for installer FB2.1.1Win32 is seem broken.

(It's now fixed as soon as I report this to the Firebird support mailing list, thank you to all of the guys there)

Jim Starkey leave MySQL

2008-06-12T18:11:00.000-07:00

Today I look a news from firebirdnews and know that Jim (father of Interbase) leave MySQL, many people think what the next things (new technology) that may Jim develop? You can read the news in here

Zeos movement supporting FB 2.1

2008-06-10T22:46:00.000-07:00

There is an active discussion about development of ZeosLib to support FB 2.1 , for the moment FB 2.1 is not supported yet. The discussion is in this link. Want some challange to support this development? The development team always welcome.

Firebird RDBMS, Vote it

2008-05-26T19:25:00.000-07:00

And the last to vote is Firebird RDBMS the winner of "Best User Support" and "Best Project for The Enterprise" in Sourceforge Community Choice Awards 2007

ZeosDBO Project, Vote it

2008-05-26T19:16:00.000-07:00

Here again, vote for Zeos Database Object (ZeosDBO) the database components for MySQL, PostgreSQL, Interbase, Firebird, MS SQL, Sybase, Oracle and SQLite for Delphi, FreePascal/Lazarus, Kylix and C++ Builder. Nominate as "Best Tool or Utility for Developers"

DelphiCodeToDoc, Vote it

2008-05-26T19:04:00.000-07:00

Hi all, I use this utility to make a documentation in my project, please vote it if you like it. Hope you like it, because it's a nice tool :D nominate as "Best Tool or Utility for Developers"

Zeos 6.6.3-rc(Rev368) ZIBEventAlerter

2008-05-21T22:44:00.000-07:00

Yesterday I had a chat with "mdaems" one of the ZeosLib developer, and now I know how this new component work :D. Here are the steps that you must follow:

Drop the ZIBEventAlerter to your form and attach connection property to ZConnection
Add events name in the Events property of ZIBEventAlerter (one line for one event)
Set AutoRegister property of ZIBEventAlerter to false (because it still not work correctly even if you set it to true)
Set Registered property of ZIBEventAlerter to false (if you set it to true it will create AV when your application start because it will register the event before the connection establish)
Call RegisterEvents method of ZIBEventAlerter in OnAfterConnect event of ZConnection
procedure TForm1.con1AfterConnect(Sender: TObject);
begin
ZIBEventAlerter1.RegisterEvents;
end;
It also a good practise to call UnregisterEvents in OnBeforeDisconnect of ZConnection
procedure TForm1.con1BeforeDisconnect(Sender: TObject);
begin
ZIBEventAlerter1.UnRegisterEvents;
end;
Write your own action in OnEventAlert method of ZIBEventAlerter
Run your appication and see the magic :)

This component also still need refactoring to became more generic, because the heart of Zeos is can be used with more than 1 database. So this component is still in development, but you can use for Firebird/Interbase right now :D

Zeos 6.6.3-rc (Rev 368) What's new?

2008-05-20T21:59:00.001-07:00

There is a good news, in rev 368 there is 1 component added in ZeosLib (beside a lot of bug fixed of dataset problem). It's ZIBEventAlerter. As it sound, it's to catch the event in Firebird or Interbase database. Finally Zeos has it :D but there is still some problem, it's cann't work correctly :(, but it's a good start :D and perhaps some Zeos developers can fix it or some of you want to try to fix it? (challenge for all of guys who read this article ;))

This is the link to the download section of the latest ZeosLib component

Firebird History

2008-05-18T20:40:00.000-07:00

It's nice to know how it become a Firebird, here is the history of Firebird that I copied from Firebird official website.

1976-1981: Jim Starkey wrote Datatrieve at DEC.
1981-1984: DEC started RDB, their first relational database. Jim got impatient and started coding his own version, named JRD.
1983: Philippe Kahn founded Borland International.
1984-1985: Jim Starkey, Ann Harrison and Don Depalma signed contract with Apollo Computer, a workstation company, founding Groton Database Systems.
1985: Dave Root left Apollo to become the fourth founder.
1986: Groton became Interbase and grew for 5 years.
1986: Interbase 2.
1986: Ashton-Tate invested in Interbase.
1988: Interbase 3.
1988, summer: Cognos started working with IB.
1988: Ashton-Tate purchased 51% of Interbase.
1989: Cognos released StarBase on VAX/VMS., based on IB 2.X.
1991: StarBase was offered by Cognos on HP-UX, DG/UX, Ultrix, SunOS/Solaris, AIX and SCO, based on IB 3.X.
1991: Ashton-Tate purchased all of Interbase.
1991, October: Borland bought Ashton-Tate.
1992, early: Cognos released StarBase for HP MPE/XL.
1992: Interbase 3.3 was released in June. This was the first version with International European support. Followed the release of v3.2J (Japanese only release).
1992, late: Cognos stopped using the name StarBase and continued with the IB name.
1994: Interbase 4 appears.
1995, January 9: Borland ships Interbase 4.0 WorkGroup Server for Win NT and for Novell Netware.
1995, February 10: Borland launches WWW site.
1995, April 24: Borland sells Delphi 1 C/S. Interbase 4.0C is included.
1995, September 15: Interbase unveils InterClient.
1995, September 25: Magnavox selects IB4 in $3.5 million database deal for U.S. Army. Borland announces InterBase 4 for Unix.
1995, October 19: Ernst & Young Consulting wins ACS Software Challenge using Borland's Delphi and InterBase.
1995, December 15: Borland announces exclusive agreement with premier system integrator in Japan - Rios Corporation. This is the origin of IB4 for Linux and FreeBSD at Rios Corporation.
1996, March 4: Borland announces availability of Delphi 2. Interbase 4.1 is included.
1996, May 29: Borland announces InterClient, the JDBC driver for Interbase.
1996, July: Gary Wetsel resigns as Borland CEO. William F. Miller is named interim CEO.
1996, August 20: Borland announces Interbase 4.2.
1996, September 25: Whitney G. Lynn is named interim president and CEO. Paul Gross, senior VP of R&D, resigns.
1996, October 21: Corel licenses Paradox database from Borland.
1996, October 30: Borland ships IB 4.2.
1996, November 7: Philippe Kahn, founder of Borland, resigns as Director.
1996, November 25: Delbert W. Yocam is appointed Chairman and CEO.
1996, December: Jason Wharton delivers IB_Objects (IBO) 1.0.
1997, January 17: Borland awards a worldwide software and trademark master license for ReportSmith to Strategic Reporting Systems.
1997, February 26: Borland ships BCB 1.
1997, April 29: Borland forms ISC subsidiary to expand market opportunity for its InterBase product.
1997, May 5: Borland announces Delphi 3 C/S Suite. Interbase 4.2 is included with Delphi 3.
1997, July 15: Corel licenses SQL Builder, Interbase, LIBS and SQL Links from Borland to include them in Corel Paradox 8.
1997, September 15: Interbase unveils InterClient ready to ship.
1997, December 1: Interbase 5.0 is released.
1998, February 10: Borland announces BCB 3. With BCB, Interbase 5 is included.
1998, June 8: Borland international stockholders approve name change to Inprise Corporation.
1998, August 5: Interbase 5.1.1 is included with Delphi 4.
1998, August 12: Inprise transfers Visual dBase to Interbase subsidiary.
1998, August 20: Interbase 5 for Linux is announced.
1999, January 27: Inprise creates two separate divisions: Inprise and borland.com. Jim Weil, president of Interbase, is named president of the Inprise division. John Floisland, senior VP of worldwide marketing, is named president of borland.com.
1999, February 2: Inprise launches borland.com. Interbase 5.5 is included with BCB4.
1999, March: FIBPlus version 1 by Serge Buzadzhy.
1999, March 12: Inprise licenses Visual dBase to Ksoft, Inc.
1999, March 31: Inprise announces the resignation of Delbert Yocam (CEO) and the CFO.
1999, April 13: Inprise names Dale Fuller interim President and CEO.
1999, mid: Interbase 6 - kinobi in private beta testing.
1999, June 8: MS and Borland deal. The total value of investment and payment from MS to Inprise is US$125 million. This includes the right to use Inprise-patented technology in MS products.
1999, August: FIBPlus version 2.
1999, September 7: Inprise announces an alliance with Dunstan Thomas Limited to provide professional services for Inprise technology in the UK, Nordic, Benelux and Eastern European regions. Dunstan Thomas is an Interbase partner, too.
1999, September 10: Inprise ships Delphi 5 with Interbase 5.5.
1999, October 25: Interbase 5.6 available on Novell Netware 4.2 and 5.0 and Windows platforms (32 bits). IB5.6 was offered as a free upgrade for IB5.X licensees on Windows; for the first time a free upgrade.
1999, December 14: The resignation of 3 key people at ISC (Bill Karwin, Paul Beach and Wayne Ostiguy) halts Interbase development.
1999, December 17: Greg Deatz discovers the facts and posts to Mers with "Bad rumors abound" subject. Steve Tendon coined the term "black Friday" to refer to the exact date when the bad news were known. (Ironically, this day was my birthday, so I couldn't receive a worst gift.) First fiasco. Tidal waves start flooding Borland newsgroups. The "SaveInterbase" emailing list is created by Helen Borrie to avoid losing IB forever. The IBDI Group (IB Developer's Initiative) is founded by Helen Borrie, Jason Wharton and Dalton Calford to safeguard the interests of Interbase users.
1999, December 21: Two other ISC engineers resign.
2000, January 3: Borland announces IB6 will be open sourced and challenges other vendors to open-source their database products. SaveInterbase is replaced by IBDI. Borland's shares soar for some weeks as a result of the announcement.
2000, February 7: Corel - Inprise/Borland announce a future merger to create a Linux powerhouse in the next months.
2000, February 14: A new company is being created. Ann Harrison accepts the challenge of being the new ISC President and Jim Starkey will be technical advisor. (Isn't that Apple brought back its former people, too?) A series of IB-*** emailing lists are created with specific purposes.
2000, February 29: Paul Beach named Interbase VP Sales & Marketing.
2000, March, 22: Borland announces availability of BCB5. It ships with Interbase 5.6.
2000, April 27: Due to lobby from some shareholders and users, Borland reevaluates the merger with Corel.
2000, May 16: Borland and Corel terminate proposed merger.
2000, May: FIBPlus version 3.
2000, June 30: Second fiasco. IB6 is not released in time as expected due to pending legal issues.
2000, July 14: Dale Fuller authorizes Cobalt Networks to distribute IB6 binaries with their platforms.
2000, July 25: Inprise and not ISC is the company releasing Interbase 6.0 as a free and open source database, but without the documents and without the basic test suites. Is this a decent and honest open source offering? No ISC exists as an independent or subsidiary company.
2000, July 28: Third fiasco. Ann Harrison resigns to her position of acting as General Manager for the Interbase part of Borland. The deal between ISC and Borland has died. Borland's Ted Shelton delivers an official press release to IB-Architect before the article is posted in the web site. Borland prefers to retain the product "in the interest of shareholders".
2000, July 31: A group of enthusiast people create the Firebird tree at Source Forge, because the Interbase tree doesn't admit external people as developers.
2000, August 23: The IBPhoenix group unveils the www.ibphoenix.com site, with Ann Harrison and Paul Beach among others. Ann states that IBPhoenix is not affiliated with Inprise(r) in any way.
2000, September 8: Inprise publishes an open letter to our InterBase® customers, signed by Frank Slootman VP - Software Products. The person that established the first contact after the ISC deal died, Ted Shelton, is not longer in charge of the engine, because it was transferred to the Business/Enterprise division, even thought the Inprise website doesn't reflect that change at that time.
2000, November 27: Inprise issues a press release, announcing Jon Arthur's promotion to Director of Interbase, reporting to Frank Slootman, VP of Software Products.
2000, December 18: Frank Schlottmann-Goedde spots an hard coded password plus a bizarre internal UDF in IB, while working on the Firebird Project. Those breaches were there since 1994. Inprise is alerted discretely.
2001, January: Inprise is Borland again (BSC).
2001, January: FIBPlus version 4.
2001, January 10: CERT advisory: compiled back door plus doomsday internal UDF in IB made public along with different patches from IBPhoenix and Borland.
2001, January 11: Slashdot devotes a large thread to the backdoor.
2001, January 14: The Register publishes an article about the same security breach.
2001, March 14: Borland comes back to the paid releases.
2001, July 19: IBO v4 is released.
2001, November: Yaffil development is initiated by Aleksey Karyakin and Oleg Loa, based on Firebird, with improved productivity.
2001, December 4: Borland announces IB 6.5. Do not get confused by that press release, however, since the open source version doesn't have the new features listed here. Bill Todd's explanation of the same features is here.
2002, January: IBDI founders announce the dissolution of the group. Technical content moved to the Firebird site.
2002, March 12: Firebird 1.0 Final Release
2002, October 29: Borland announces InterBase v7.
2002, November 20: The FirebirdSQL Foundation is formally incorporated to raise development funds for Firebird.
2003, June 17: Borland announces InterBase v7.1 with support for MS' dot-net.
2003, December 2: Yaffil announces its source code will be merged with Firebird.
2004, February 21: Firebird 1.5 Final Release.
2004, April: Paul Ruizendaal reports that he was able to port Compiere to FB with minimal modifications thanks to his Oracle PL/SQL emulation layer.
2004, May 27: Nigel Weeks releases OpenAspect, a scalable, dependable, high-speed open source business management system, based on FB.
2004, August 2: The Firebird Book by Helen Borrie is published by Apress.

FB 2.1 Week of Year Feature

2008-04-17T21:06:00.000-07:00

I already installed it and try it a while, and the first thing I try is extract week. Now in FB 2.1 it supported week of year (base on ISO-8601)!!. So my past article is more simple to do :) the new syntax is:
extract(week from some_date_field)

FB 2.1 is released!!!

2008-04-17T19:33:00.000-07:00

Hey, FB 2.1 is released today!! Get it HERE. Let share new feature after I try to play with this version :D Any idea?

Get a week of year in Firebird PSQL

2008-04-17T18:14:00.000-07:00

2008 is now a busy year for me hehehe, long time not post an article again, mm... I want to share some technique in FB to get a week of year (I use it for my last project), I read a nice article from this site and see that in FB tracker there is a discussion about this stuff. The simple PSQL technique that I had learn is:

(EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM D-1) + 7) / 7

It can give us the week of year, but I also need the start and the end of that week, so I must think about this and I found a solution, here I capture some code from my project to know the start date and end date of that week of year

rangetglawal = cast((extract(year from D)) || '.01.' || '01' as date);
rangetglawal = rangetglawal - extract(weekday from rangetglawal);
week = (EXTRACT(YEARDAY FROM D) - EXTRACT(WEEKDAY FROM (D-1))+7)/7 +1;

viStartWeek = ((EXTRACT(YEARDAY FROM rangetglawal) - EXTRACT(WEEKDAY FROM (rangetglawal-1))+7)/7+1);

if (extract(year from D)=cast(extract(year from rangetglawal)as varchar(4))) then
begin
"viTempWeek" = week - "viStartWeek"-1;
rangetglawal = rangetglawal + ("viTempWeek"*7)+1;
end else
begin
rangetglawal=rangetglawal+((week-1)*7)+1;
end
rangetglakhir=rangetglawal+6;

The start date of that week will be in "rangetglawal" variable and the end of that week will be in "rangetglakhir" variable. I add plus 1 in the original code for get a week of year because the original code will result 0 for first week, and I want it to start from 1.

Enjoy this code and perhaps it can be usefull.

FBCopy 1.7 released (FB tool)

2008-01-25T17:03:00.000-08:00

FBCopy version 1.7 is released. FBCopy is a tool for Firebird. What this tool does? According to it's readme file, this tool is for copy data between Firebird tables and databases. FBCopy is a command-line tool, so you can use it inside batch scripts, cron jobs, etc. Currently, Linux and Windows versions are available.

The capabilities are:
* copying data between databases (sorted by dependency tree)
* copying generator values (since version 1.70)
* comparing table fields
* comparing data (with detailed output in HTML)
* open source under MIT license
* cross platform - works on Windows and Linux
* works with all Firebird versions (tested 1.0 - 2.1)
* easy to integrate into batch jobs
* light for remote use via SSH

You can download this nice tool in here

New Little Feature in Zeos

2008-01-23T17:01:00.000-08:00

Hi, there are 2 new methods on TZConnection component, they are:

function ExecuteDirect(SQL : String) : boolean;
function ExecuteDirect(SQL:string; var RowsAffected:integer):boolean;

That methods are to execute SQL DDL statement directly without need of TZQuery.
You can try and test this new feature in the Zeos testing Rev337. If the method not act correctly, you can tell us in the forum.

Firebird Roadmap 2008

2008-01-23T16:58:00.000-08:00

The Firebird project already release the FB 2.1 RC1 (vailable for field testing on 64-bit and 32-bit Windows and Linux (MacOSX to follow soon)) and also their roadmap in 2008.

BLOB Field using ZeosDBO 6.6.x

2008-01-17T00:18:00.000-08:00

Hi, I want to share how to save and load BLOB from database. The database I use here is Firebird, but I think Zeos behaviour is same. This example below is to save and load the JPEG image into and from Firebird database. If you use bitmap image then you can display it using TDBImage, but for JPEG you cann't do that otherwise you get an error that said that it is not a valid bitmap. So in this code, for display the image from database I use TDBImage with no binding (the datasource property still null). So let's look the code:

The component that I dropped:

uses jpeg, ........
type
TForm1 = class(TForm)
con1: TZConnection;
qr1: TZQuery;
qr1aaa: TStringField; //persistant field that only string value
qr1gambar: TBlobField; //persistent field "gambar" that used to save JPEG image
ds1: TDataSource;
dlgOpen1: TOpenDialog;
dbnvgr1: TDBNavigator;
dbeaaa: TDBEdit; //use binding to connect to field "aaa"
btn1: TButton;
btn2: TButton;
dbimg1: TDBImage;
procedure btn1Click(Sender: TObject);
procedure FormShow(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
procedure btn2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

The code:

procedure TForm1.FormShow(Sender: TObject);
begin
//Connect to Database and open query
con1.Connect;
qr1.Close;
qr1.Open;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
//Close query and connection
qr1.Close;
con1.Disconnect;
end;

procedure TForm1.btn1Click(Sender: TObject);
begin
if dlgOpen1.Execute then
begin
//Load JPEG image (or anything type of file) into database
qr1gambar.LoadFromFile(dlgOpen1.FileName);
end;
end;

procedure TForm1.btn2Click(Sender: TObject);
var j:TJPEGImage;
begin
//Display image from database to TDBimage (dbimg1) component
j:=TJPEGImage.Create;
j.Assign(qr1gambar);
dbimg1.Picture.Assign(j);
FreeAndNil(j);
end;

How the code work?
"btn1Click" method is clicked after the dataset is in edit or insert mode, then after it load from file then post it (I use DBNavigator in the above example). And then "btn2Click" is for display image from database to TDBImage component. Simply enough? yes, that's all.

Now, what about other format like Word, Excel or PDF document that you want to extract from the database? (the save method is same). The answer is, save it to file again. Zeos already implement it well, so you can do like this:

qr1gambar.SaveToFile('c:\somefile.doc');

Also simply enough? yes. So don't worry to use BLOB field with Zeos component :)
How about to save/load it from Stream? You can also use "SaveToStream" and "LoadFromStream" method.

Yesterday, Today, Tomorrow in Firebird

2007-12-20T18:11:00.000-08:00

Here is some date literal again. Firebird can calculate the value of yesterday, today and tomorrow into a date value. Here is the example that you can try by yourself.

select cast('yesterday' as date), cast('today' as date), cast('tomorrow' as date)
from rdb$database

ZeosDBO 6.6.2 Release Candidate

2007-12-12T18:12:00.000-08:00

Now, ZeosDBO 6.6.2 RC version is ready to download here and the help file can be download here. Many bugs have been fixed since the 6.6.1 version. Here is some of the Firebird bugs and general bugs that have been fixed since 6.6.1 version:

- 0000079: [dbc] Typo in SQl statement in TZInterbase6Connection.Open; (mdaems) - 0000070: [general] Missing license files (mdaems) - 0000033: [interbase/firebird] Fixes a bug in UpdateString procedure for ZDbcInterbase6Utils (mdaems) - 0000022: [general] Split ZPlainMysql.inc (mdaems) - 0000019: [interbase/firebird] string parameters don't work for blob fields (mdaems) - 0000016: [core] Integer field treated as Largeint (mdaems) - 0000055: [component] Wrong index variable used when doing "refresh" in ZUpdateSQL component , patch included (mdaems) - 0000056: [component] Protection fault when doing an EAbort exception in BeforeConnect of TZConnection (patch incl.) (mdaems) - 0)">0000059: [component] ZQuery-DSP-CDS empty field data fetched while using incremental fetching (PacketRecords>0) (mdaems) - 0000061: [component] Wrong data returning in select (mdaems) - 0000065: [general] FPC 2.2 doesn't compile (mdaems) - 0000069: [general] Compiler Warnings in Delphi 2006 (mdaems) - 0000071: [interbase/firebird] Floating point not display correctly (cipto_kh) - 0000049: [core] Lookup fields with int64 data type not work in Delphi/C++Builder 5 (mdaems) - 0000048: [general] TZSQLProcessor does not react when Script text is not properly delimited (mdaems) - 0000007: [interbase/firebird] TZSequence error, value from prior database (cipto_kh) - 0000013: [interbase/firebird] can not use a string as parameter value for integer/long parameters (mdaems) - 0000020: [interbase/firebird] second call to stored proc fail with a parameter mismatch error (cipto_kh) - 0000034: [interbase/firebird] Lookup field make other field in TZQuery lost the value (disappear from display but still exists in database) (mdaems) - 0000027: [interbase/firebird] problem handling DEFAULT NULL in domain *AND* in table (mdaems) - 0000028: [interbase/firebird] TZInterbase6DatabaseMetadata.HasNoWildcards - wrong result (cipto_kh) - 0000037: [component] ZEOS AND LAZARUS 0.9.23 and FPC 2.1.3 WIN32 ERROR (mdaems) - 0000038: [component] Can't open sqlite databases with Umlauts like äöü in path (fixed) (mdaems) - 0000040: [component] Error in conversion int64 to variant in InternalPost on Delphi/C++Builder 5 (ZAbstractDataset.pas) (mdaems) - 0000046: [component] fpc 2.1.4 cannot compile ZAbstractRODataset.pas (mdaems) - 0000042: [general] Using the same component (TZStoredProcedure) after ExecProc then using Open it will crash (cipto_kh) - 0000043: [component] ReadOnlyQuery + TDataSetProvider = Blank fields on details (mdaems) - 0000063: [component] TZSQLProcessor and comments handling (mdaems)
That list are taken from Zeos Mantis Bug Tracker. For your information ZeosDBO also run on Lazarus

ZeosDBO in Lazarus Project

2007-12-11T23:27:00.000-08:00

Today I look in Lazarus (Delphi like that running on Linux) website and there is a poll about Lazarus/ZeosLib developer conference, mmm.... it look that ZeosLib getting more popular in open source project. It's interesting to come in there, but I'm in Indonesia :( just can watch....

Get current date time in Firebird

2007-12-11T22:33:00.000-08:00

There is many way to get the current date time from the Firebird database, here are the example:

select current_date, current_time, current_timestamp, cast('now' as timestamp)
from rdb$database

The last one that use special keyword (date/time literal) called 'now' that can be cast to timestamp, date, or time. Now the question is, how to get the milisecond in SQL statement? I still don't know, maybe some people arround here can can share to us the answer?

Firebird 2.1 beta2 is OUT

2007-10-17T20:37:00.000-07:00

Firebird 2.1 beta 2 is out now, it's time to explore the new feature. It's hope the last beta version and after that it will go to Release Candidate version :)

The release notes of that version is in here. Many new feature that make it more interesting like Common Table Expression, aggregate function LIST, many build in function (that make us not needed to add UDF manually), built in monitoring table, and the cool one "Domain in PSQL"!!. It like FB have a feature plus than M$ SQL Server 2005 :) (CMIIW).

Firebird System Table (Part VI)

2007-10-17T20:25:00.000-07:00

Here maybe the last system table (that I really used in in my daily program) I would like to share, that system table is (I) called a "constraint system trigger" (I don't know the correct name for this data, so I just called like that ^_^ CMIIW). I usually also find the error in query that has the name like this "CHECK_xx", so to find the table that has this contraint then I create a view like this:

CREATE VIEW "vSYS_FindSystemTrigger"(
"triggerName",
"tableName",
"triggerInactive",
"triggerType")
AS
select r.rdb$trigger_name, r.rdb$relation_name, r.rdb$trigger_inactive, r.rdb$trigger_type
from rdb$triggers r
where r.rdb$system_flag = 4;

In that query I don't know the meaning of the "triggerType", so if one of you know the meaning of that field, please add a comment in here. It can give other Firebird users more knowledge :)

Firebird System Table (Part V)

2007-10-17T20:17:00.000-07:00

In this part V, I would like to share the system table that list the constraint of the database. I often use this view to find which constraint that make an error on my program (the table that the constraint belongs on, or just the index name that cause the error).

As we know in Delphi if our query make an error then if you trap the exception, it will show the name of the constraint error (if the error caused by constraint error).

CREATE VIEW "vSYS_FindConstraint"(
"consTipe",
"tableName",
"consName",
"indexName")
AS
select c.rdb$constraint_type, c.rdb$relation_name, c.rdb$constraint_name, c.rdb$index_name
from rdb$relation_constraints c

Firebird System Tables (Part IV)

2007-10-05T01:30:00.000-07:00

The system table for stored procedure is like here. The purpose of this view is the same like in Firebird System Table (Part II).

CREATE VIEW "vSYS_FindProcSource"(
"procName",
"procSource")
AS
select r.rdb$procedure_name, r.rdb$procedure_source
from rdb$procedures r
where r.rdb$system_flag = 0;

Firebird 2.0.3 is released

2007-09-29T04:48:00.000-07:00

On 26 September 2007, Firebird version 2.0.3 is released, you can download in here. This version is only bug fixed version from FB 2.0

Installing ZeosDBO in Delphi (rev version)

2007-09-26T20:04:00.000-07:00

I already make some revision on my previous post about how to install ZeosDBO components in Delphi. The revision link is here. ZeosDBO is one of the direct connection components to many RDBMS including Firebird. The idea of ZeosDBO is like JDBC, and to switch from one database to another database is really easy, find it by yourself after trying this amazing components. One thing, this component is FREE and OPEN SOURCE

Firebird System Tables (Part III rev 1)

2007-09-26T19:49:00.001-07:00

Here is some revision (from Romkin idea) to the query of Firebird System Tables part III.
In this query, it doesn't need anymore to has "case" statement to get the convertion from numeric type_name to more readable name. And I want to say thank you for the supporting comment on this little articles. Once correction from Milan Babuskov that told me "CONTAINING" is in Firebird since the first versions 1.0. Hope all articles in this blog can be usefull for FB community. Wait for the next Firebird System Tables again, stay tune on the same channel he..he..he.. :)

CREATE VIEW "vSYS_FindDependencies"(
"dependentName",
"dependentType",
"dependedOnName",
"dependedOnFieldName",
"dependedOnType")
AS
select d.RDB$DEPENDENT_NAME DEPENDENT_NAME,
(select t.RDB$TYPE_NAME from RDB$TYPES t
where d.RDB$DEPENDENT_TYPE=t.RDB$TYPE
and t.RDB$FIELD_NAME = 'RDB$OBJECT_TYPE'
) DEPENDENT_TYPE,
d.RDB$DEPENDED_ON_NAME DEPENDED_ON_NAME, d.RDB$FIELD_NAME DEPENDED_ON_FIELD_NAME,
(select t.RDB$TYPE_NAME from RDB$TYPES t
where d.RDB$DEPENDED_ON_TYPE=t.RDB$TYPE
and t.RDB$FIELD_NAME = 'RDB$OBJECT_TYPE'
) DEPENDED_ON_TYPE
from RDB$DEPENDENCIES d

Firebird System Tables (Part III)

2007-09-20T21:12:00.000-07:00

This article idea (Part III) is belong to the someone that put comment on my previous article, so I just accomodate his/her idea. And I already arrange to become a view like this:

CREATE VIEW "vSYS_FindDependencies"(
"dependentName",
"dependentType",
"dependedOnName",
"dependedOnFieldName",
"dependedOnType")
AS
select RDB$DEPENDENT_NAME,
case RDB$DEPENDENT_TYPE
when 0 then 'Table'
when 1 then 'View'
when 2 then 'Trigger'
when 3 then 'Computed field'
when 4 then 'Validation'
when 5 then 'Procedure'
when 6 then 'Expression index'
when 7 then 'Exception'
when 8 then 'User'
when 9 then 'Field'
when 10 then 'Index'
when 14 then 'Generator'
else RDB$DEPENDENT_TYPE
end DEPENDENT_TYPE,
RDB$DEPENDED_ON_NAME, RDB$FIELD_NAME,
case RDB$DEPENDED_ON_TYPE
when 0 then 'Table'
when 1 then 'View'
when 2 then 'Trigger'
when 3 then 'Computed field'
when 4 then 'Validation'
when 5 then 'Procedure'
when 6 then 'Expression index'
when 7 then 'Exception'
when 8 then 'User'
when 9 then 'Field'
when 10 then 'Index'
when 14 then 'Generator'
else RDB$DEPENDED_ON_TYPE
end DEPENDED_ON_TYPE
from RDB$DEPENDENCIES;

The idea is to know the dependency on some entity in the database. Perhaps it is also usefull for everyone.

Firebird System Table (Part II)

2007-09-19T20:51:00.001-07:00

Another FB system table is system table for trigger. The reason I create this view is to scan for some code inside the source of the trigger. For example, I want to change the field type of table A, then I want to know, this field is being used by which trigger, so I can search thorough this view:

CREATE VIEW "vSYS_FindTriggerSource"(
"triggerName",
"tableName",
"triggerInactive",
"triggerSource")
AS
select r.rdb$trigger_name, r.rdb$relation_name, r.rdb$trigger_inactive, r.rdb$trigger_source
from rdb$triggers r
where r.rdb$system_flag = 0

If you want to seach some text in the trigger source then you can use this simple query:

select *
from "vSYS_FindTriggerSource"
where "triggerSource" containing 'some_field'

The keyword "containing" here (if I'm not forget) is exists from FB 2. The difference between "containing" and "like" is the "like" is case sensitive where "containing" is case insensitive, and also you not need the "%" sign like in "like" keyword.

That view also display the status of the trigger, if the "triggerInactive" field value is zero, then it means that trigger is active and vice versa.

Firebird System Tables (Part I)

2007-09-17T23:06:00.000-07:00

Hi, whew it's has been long time since my last time post, yes I a little bit busy now, with some of my own project and some of my family work. Ok, now I want to share about Firebird System Tables, they are tables that side on every FB database that consist of metadata of user tables (table that you created), many question about this and also many purpose to know about this.

I often use this technique when I develop a database that consist of many tables and where there is an error on one of the field and then I must know this field is belong on which table, to solve that problem, I create a view like this:


CREATE VIEW "vSYS_FindField"(
"tableName",
"fieldName",
"default_val")
AS
select r.rdb$relation_name, f.rdb$field_name, f.rdb$default_source
from RDB$RELATIONS r, RDB$RELATION_FIELDS f
where r.rdb$system_flag = 0
   and r.rdb$relation_name=f.rdb$relation_name;

The FB System Tables can be known from "RDB$RELATIONS.RDB$SYSTEM_FLAG" greater than 0 (zero). The "RDB$RELATIONS" is a system table for user table and user view (off course it also contain of many things that you can look by yourself ;P) And "RDB$RELATION_FIELDS" is as you may guess is a system table for user field. It's now up to you to improve that query, maybe you want to include some more information, then go ahead :)

NB: Thank's Romkin for the correction, now I already applied your correction in this short article :)

Firebird Won 2 Sourceforge Community Awards

2007-08-15T19:37:00.000-07:00

From InfoWorld:
SourceForge unveils the winners of the ‘open-source Oscars’
“…The Firebird relational database was another double winner, being voted both best project for the enterprise and the project providing the best user support.”

Firebird 5th International Conference

2007-08-15T19:34:00.001-07:00

This years conference will be held in the Telekom Conference Hotel in Hamburg, Germany. There are different international flights to Hamburg. We recommend to check www.air-berlin.com with a lot of european destinations and very cheap pricing. Hamburgs international airport code: HAM http://www.ham.airport.de/index_en.phtml

There is a public train station nearby conference hotel. A map of the location with traveling infos will be send to all visitors

This year we have a special entry level track on thursday and friday. For advanced visitors you find a seperate thread. In the codecamp, you can ask on both days the experts and let them analyze your real world problem. On saturday you will find more general session topics.

Early bird conference fees (valid until 20th sep 2007):
-Full Conference Package 599 Euro *
3 days conference, 4 nights in the conference hotel
(17.-21.) , breakfast, lunch, dinner, evening events

-Conference Only Package 399 Euro *
3 days conference, lunch

-Main Conference Day only Package 169 Euro *
saturday only conference, 1 night in the conference hotel (20.-21.), breakfast, lunch, dinner, saturday evening event

-Main Conference Day only 99 Euro *, saturday only
conference, lunch

* all prices excl 19% VAT, required for all visitors 2nd visitor discount 10%, 3rd visitor discount 15% Please use the website www.firebird-conference.com or email info@h-k.de or fax +4970042397378 to send us your reservation. We hope to see you in Hamburg.

Naturally, we are still looking for sponsors! If you have been thinking about being a conference sponsor, NOW is the time to act. It is also possible to add special product sponsoring packages. Please contact hklemt@ibexpert.biz as soon as possible.

Taken from Firebird News

New site Firebird FAQ

2007-08-15T19:28:00.000-07:00

Hi guys, there is a new site for Firebird, it is Firebird FAQ
This site is built by Firebird users, for Firebird users to answer the most frequently ask question (FAQ). It's also has a good index to separate question topic. So if you want to know more about this fenomenal database, check it out :)

Vote for Firebird in Sourceforge Awards

2007-07-08T20:18:00.000-07:00

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)

2007-07-05T22:24:00.002-07:00

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

2007-07-05T22:24:00.001-07:00

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

2007-07-05T22:23:00.003-07:00

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

2007-07-05T22:23:00.001-07:00

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

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

Backup in Firebird

2007-07-05T22:22:00.002-07:00

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

2007-07-05T22:22:00.001-07:00

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

2007-07-05T22:21:00.001-07:00

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

2007-07-05T22:20:00.000-07:00

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

2007-07-05T21:34:00.001-07:00

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?

2007-07-05T21:33:00.003-07:00

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

2007-07-05T21:33:00.001-07:00

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

2007-07-05T21:30:00.003-07:00

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

2007-07-05T21:30:00.001-07:00

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

2007-07-05T21:28:00.000-07:00

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

2007-07-05T21:27:00.002-07:00

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

2007-07-05T21:27:00.001-07:00

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

2007-07-05T21:26:00.001-07:00

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

2007-07-05T21:25:00.002-07:00

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

2007-07-05T21:25:00.001-07:00

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

2007-07-05T21:23:00.002-07:00

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

2007-07-05T21:23:00.001-07:00

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

2007-07-05T21:22:00.000-07:00

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

2007-07-05T21:21:00.000-07:00

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

2007-07-05T21:15:00.000-07:00

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

2007-07-05T21:14:00.001-07:00

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

2007-07-05T21:13:00.001-07:00

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

2007-07-05T21:12:00.003-07:00

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

2007-07-05T21:12:00.001-07:00

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

2007-07-05T21:11:00.001-07:00

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

2007-07-05T21:08:00.000-07:00

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

2007-07-05T21:07:00.000-07:00

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

2007-07-05T21:06:00.000-07:00

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)

2007-07-05T20:56:00.000-07:00

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?

2007-07-05T20:49:00.000-07:00

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

2007-07-05T20:46:00.000-07:00

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)