TOracleQuery with TLOBLocator

[MadhuReddy]

hi there,
I have downloaded DOA component for delphi7. I am using TOracleQuery to store blob data(TImage data) in the database and to retrieve blob data from the database.

MyForm.FormCreate looks like

Image1.Picture.Bitmap.LoadFromFile('c:\4.bmp');

// Connect to the Database

OracleSession1.LogonUsername := 'madhu';
OracleSession1.LogonPassword := 'madhu123';
OracleSession1.LogonDatabase := 'oracle';
OracleSession1.LogOn;
OracleQuery1.Session := OracleSession1;

//Create one sample table that hold blob data
with OracleQuery1 do
begin
SQL.Clear;
SQL.Add('Create table test_blob(myimage blob, id varchar2(100))');
try
Execute;
except
//Table already created
end;
end;


I am loading an image into Image1(TImage) and connected to the database using OracleSession. And OracleQuery.session is assigned to Oraclesession.

MyForm.SaveImageToDBbuttonclick is looks like

var
lBlob: TLOBLocator;
begin
// Store an image into the database

lBlob := TLOBLocator.CreateTemporary(OracleSession1,otBlob,True);
Image1.Picture.Bitmap.SaveToStream(lBlob);

with OracleQuery1 do
begin
SQL.Clear;
DeclareVariable('lblob',otBlob);
SQL.Text := 'Insert into test_blob(myimage,id) valueslblob,''1'')';
SetComplexVariable('lblob',lBlob);
Execute;
end;
OracleSession1.Commit;
lBlob.Free;

end;

I created one temporary TLOBlocator and saved the image1(TImage) stream to the lblob(TLOBLocator). And execut the insert query to insert blob(Image1) data.


MyForm.LoadImageFromDBbuttonclick is looks like

var
lBlob: TLobLocator;
lId : Integer;
begin
// Retrieve image from database
with OracleQuery1 do
begin
SQL.Clear;
SQL.Add('Select myimage,id from madhu_blob') ;
Execute;
end;

lId := OracleQuery1.FieldAsInteger('id');
lBlob := OracleQuery1.LOBField('myimage');

if not lBlob.IsNull then
Image2.Picture.Bitmap.LoadFromStream(lBlob);

end;


Here, I execute one select statement and get the blob data to the lblob(TLOBLocator). Image2 is loaded from lblob.

Q. In fromcreate database is connected, I clicked on the SaveImageToDB button then one record is inserted successfully. Next I clicked LoadImageFromDB button then in query execution (OracleQuery1.Execute) one exception raised: access voilation raised in the module oraclient10.dll. However one record is inserted successfully in the SaveImageToDBbuttonclick. I closed the application and re ran the application, now I directly clicked LoadImageFromDB button, blob data retrieved from the database and displayed on to the Image2(TImage).

What I have observed is, in the same session, I could not insert a blob record and retrieve the same. If one session(first time running application) is inserts a blob record and another session (next time running application) retrieves the data successfully.

Thanks in advance

 

[Marco Kalter]

In your example there are 2 errors:

1. It inserts into test_blob but fetches from madhu_blob.
2. If you reuse OracleQuery1 you must use Clear instead if SQL.Clear, so that the declared bind variable from the insert is also deleted.

After these 2 corrections it works okay for me, though I never got the access violation.

 

[MadhuReddy]

thank you Marco Kalter. I solved my problem by using OracleQuery1.Clear instead of OracleQuery1.SQL.Clear. Thank you once again.