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.

Yesterday, Today, Tomorrow in Firebird

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

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

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

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?