Hi all

I've just subscrided to the list and I hope I'll find plenty
of solutions/pointers to my interbase related problems.
(I'm not a totally newbie to c/s programming though).

I use Firebird (WI-T0.9.4.41 Firebird Test1), Delphi 5 and IBX.
One of my first concerns was about using blobs for
saving/retreiving raw data. The manual (Interbase 6, Data Definition Guide)
on page 144, says (Working with stored procedures)

"
Declaring input parameters
.
...The name of the parameter must be unique within the procedure, and
the datatype can be any standard SQL datatype except BLOB and arrays.
.
.
Declaring output parameters
.
...The name of the parameter must be unique within the procedure, and
the datatype can be any standard SQL datatype except BLOB and arrays.
"

Then I searched the message archive of this list (among others) for blob
information and the result was a bit disappointing. The conclusion:
handling interbase blobs is a difficult task. Not true of course.

So I wrote a small test project to see what can be done.
It works fine and just as expected. So my question is:
Do I miss anything here?

Here is the test project (and sorry for the long post)


unit Unit1;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
IBQuery, IBDatabase, Db, IBCustomDataSet, IBStoredProc, StdCtrls, Grids,
DBGrids;

type
TForm1 = class(TForm)
IBDatabase1: TIBDatabase;
IBStoredProc1: TIBStoredProc;
IBTransaction1: TIBTransaction;
IBQuery1: TIBQuery;
btnQuery_Write: TButton;
btnQuery_Read: TButton;
Memo: TMemo;
btnSP_Write: TButton;
btnSP_Read: TButton;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
procedure btnQuery_WriteClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
procedure btnQuery_ReadClick(Sender: TObject);
procedure btnSP_WriteClick(Sender: TObject);
procedure btnSP_ReadClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
MS : TMemoryStream;
end;

var
Form1: TForm1;

implementation

{$R *.DFM}

(*

CREATE DATABASE "C:\BLOB_DB.GDB"
USER "SYSDBA"
PASSWORD "masterkey"
PAGE_SIZE 4096;
COMMIT WORK;

CREATE TABLE "BLOBTEST"
(
"ID" INTEGER,
"DATA" BLOB SUB_TYPE 0 SEGMENT SIZE 80
);

COMMIT WORK;

/* procedures */
SET AUTODDL OFF;
SET TERM ^ ;

CREATE PROCEDURE sp_ADD_DATA(ID INTEGER, DATA BLOB) AS
BEGIN
INSERT INTO BLOBTEST VALUES (:ID, :DATA);
END ^

CREATE PROCEDURE sp_SELECT_DATA RETURNS(ID INTEGER, DATA BLOB) AS
BEGIN
FOR
SELECT * FROM BLOBTEST
INTO :ID, :DATA
DO
SUSPEND;
END ^

SET TERM ; ^
SET AUTODDL ON;
*)




{ stream utilities }
(*--------------------------------------------------------------------------
--------*)
procedure iWrite(i : integer; Stream : TMemoryStream);
begin
Stream.WriteBuffer(i, SizeOf(i));
end;
(*--------------------------------------------------------------------------
--------*)
procedure sWrite(S : string; Stream : TMemoryStream);
var
Len : integer;
begin
Len := Length(S);
Stream.WriteBuffer( Len, SizeOf(Len) );
Stream.WriteBuffer( PChar(S)^, Len);
end;
(*--------------------------------------------------------------------------
--------*)
function iRead(Stream : TMemoryStream): integer;
begin
try
Stream.ReadBuffer(Result, SizeOf(Result));
except
Result := -1;
end;
end;
(*--------------------------------------------------------------------------
--------*)
function sRead(Stream : TMemoryStream): string;
var
Len : integer;
Temp : string;
begin
try
Stream.ReadBuffer(Len, SizeOf(Len));
SetString(Temp, PChar(nil), Len);
Stream.ReadBuffer(PChar(Temp)^, Len);
Result := Temp;
except
Result := '';
end;
end;




var
iCount : integer = 0;


{ TMan }
(*--------------------------------------------------------------------------
------*)
type
TMan = class
public
Name : string;
Age : integer;
procedure Save(MS : TMemoryStream);
procedure Load(MS : TMemoryStream);
procedure Init(N : string);
end;

(*--------------------------------------------------------------------------
------*)
procedure TMan.Init(N : string);
begin
Inc(iCount);
Name := N;
Age := iCount;
end;
(*--------------------------------------------------------------------------
------*)
procedure TMan.Load(MS : TMemoryStream);
begin
MS.Position := 0;
Name := sRead(MS);
Age := iRead(MS);
end;
(*--------------------------------------------------------------------------
------*)
procedure TMan.Save(MS : TMemoryStream);
begin
MS.Clear;
sWrite(Name, MS);
iWrite(Age, MS);
MS.Position := 0;
end;


{ events }
(*--------------------------------------------------------------------------
------*)
procedure TForm1.FormCreate(Sender: TObject);
begin
MS := TMemoryStream.Create;
end;
(*--------------------------------------------------------------------------
------*)
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
MS.Free;
end;
(*--------------------------------------------------------------------------
------*)
procedure TForm1.btnQuery_WriteClick(Sender: TObject);
var
M : TMan;
begin
M := TMan.Create;
M.Init('Query_Write');
M.Save(MS);
try
IBTransaction1.StartTransaction;
try
IBQuery1.Close;
IBQuery1.SQL.Clear;
IBQuery1.SQL.Add('INSERT INTO BLOBTEST VALUES (:ID, :DATA)');
IBQuery1.Prepare;
IBQuery1.ParamByName('ID').AsInteger := M.Age;
IBQuery1.ParamByName('DATA').LoadFromStream(MS, ftBlob);
IBQuery1.ExecSQL;
IBTransaction1.Commit;
except
IBTransaction1.Rollback;
end;
finally
M.Free;
end;
end;
(*--------------------------------------------------------------------------
------*)
procedure TForm1.btnQuery_ReadClick(Sender: TObject);
var
M : TMan;
begin
M := TMan.Create;
MS.Clear;
try
IBTransaction1.StartTransaction;
try
IBQuery1.Close;
IBQuery1.SQL.Clear;
IBQuery1.SQL.Add('SELECT * FROM BLOBTEST');
IBQuery1.Prepare;
IBQuery1.Open;
TBlobField(IBQuery1.FieldByName('DATA')).SaveToStream(MS);
M.Load(MS);

Memo.Lines.Add(IntToStr(M.Age));
Memo.Lines.Add(M.Name);

IBTransaction1.Commit;
except
IBTransaction1.Rollback;
end;
finally
M.Free;
end;
end;
(*--------------------------------------------------------------------------
------*)
procedure TForm1.btnSP_WriteClick(Sender: TObject);
var
M : TMan;
begin
M := TMan.Create;
M.Init('SP_Write');
M.Save(MS);
try
IBTransaction1.StartTransaction;
try
IBStoredProc1.StoredProcName := 'SP_ADD_DATA';
IBStoredProc1.Prepare;
IBStoredProc1.ParamByName('ID').AsInteger := M.Age;
IBStoredProc1.ParamByName('DATA').LoadFromStream(MS, ftBlob);
IBStoredProc1.ExecProc;
IBTransaction1.Commit;
except
IBTransaction1.Rollback;
end;
finally
M.Free;
end;
end;
(*--------------------------------------------------------------------------
------*)
procedure TForm1.btnSP_ReadClick(Sender: TObject);
begin
IBQuery1.Close;
IBQuery1.SQL.Clear;
IBQuery1.SQL.Add('SELECT * FROM sp_SELECT_DATA');
IBQuery1.Prepare;
IBQuery1.Active := True;
end;




Regards
Theo



-----------------------------------
Theo Bebekis
Thessaloniki, Greece
bebekis@...
-----------------------------------