count(*) VS count(somefield)

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

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

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

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

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

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

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

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

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

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

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

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

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:

1. Drop the ZIBEventAlerter to your form and attach connection property to ZConnection
2. Add events name in the Events property of ZIBEventAlerter (one line for one event)
3. Set AutoRegister property of ZIBEventAlerter to false (because it still not work correctly even if you set it to true)
4. 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)
5. Call RegisterEvents method of ZIBEventAlerter in OnAfterConnect event of ZConnection
   procedure TForm1.con1AfterConnect(Sender: TObject);
   begin
   ZIBEventAlerter1.RegisterEvents;
   end;
6. It also a good practise to call UnregisterEvents in OnBeforeDisconnect of ZConnection
   procedure TForm1.con1BeforeDisconnect(Sender: TObject);
   begin
   ZIBEventAlerter1.UnRegisterEvents;
   end;
7. Write your own action in OnEventAlert method of ZIBEventAlerter
8. 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?

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

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

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!!!

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 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)

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

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

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

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.