I have a need to store large files in the PostgreSQL (version 8.4). For this I use field with type Bytea.
But when I try to save in DB data with size of 100-200 Mb I received 'Out of memory' exception.
I wrote simple application on Delphi 7 that simulate inserting large data into the blob field. There is the script for table creation:
Code: Select all
create table test_blobs
(
id numeric(10) primary key,
bfile bytea default null
);
create sequence seq_test_blobs_id start with 1 increment 1 cache 10;
There is the code that I use for inserting large amount of data to DB:
Code: Select all
//this function is used for initializing connection to the DB
//paramater Open is used for identification if we want open new connection or close existing one
function TMainForm.InitConnection(Open : boolean = true) : boolean;
begin
if not Open then
begin
if SQLConnection.Connected then
SQLConnection.Close;
MessageBox(Application.Handle, PChar('Disconnected!'), PChar('Information'), MB_OK or MB_ICONINFORMATION);
memLog.Lines.Add('------------------------------');
Result := true;
Exit;
end;
Result := SQLConnection.Connected;
if not Result then
begin
SQLConnection.Params.Values['HOSTNAME'] := edHost.Text;
SQLConnection.Params.Values['SERVER PORT'] := edPort.Text;
SQLConnection.Params.Values['DATABASE'] := edDB.Text;
SQLConnection.Params.Values['USER_NAME'] := edUser.Text;
SQLConnection.Params.Values['PASSWORD'] := edPass.Text;
try
SQLConnection.Open;
Result := true;
memLog.Lines.Add(Format('HOST %s (%s)', [edHost.Text, edPort.Text]));
memLog.Lines.Add(Format('DATABASE %s', [edDB.Text]));
memLog.Lines.Add('------------------------------');
MessageBox(Application.Handle, PChar('Connected!'), PChar('Information'),MB_OK or MB_ICONINFORMATION);
except
on E: Exception do
MessageBox(Application.Handle, PChar('Connection failed!' + #13#10 + E.Message), PChar('Error'), MB_OK or MB_ICONERROR);
end;
end;
end;
//this procedure is used for simulation loading large data to the DB
//the size of the data is measured in Megabytes
procedure TMainForm.btnLoadClick(Sender: TObject);
var
tmpQ : TSQLQuery;
ms : TMemoryStream;
tmpStr : string;
begin
if edSize.Text = EmptyStr then //if user forget enter the size of loaded data then
edSize.Text := '1'; //the size will be 1 Mb
if not InitConnection then
Exit; //exit if connection initialization failed
tmpQ := TSQLQuery.Create(nil);
tmpQ.SQLConnection := SQLConnection;
tmpQ.SQL.Add('insert into test_blobs values (nextval(''seq_test_blobs_id''), cast(:bfile as bytea));');
//simulation of large data using DupeString.
tmpStr := DupeString('a', StrToInt(edSize.Text) * 1024 * 1024);
//loading data to the MemoryStream
//size of used memory will be increased in twice
ms := TMemoryStream.Create;
ms.WriteBuffer(Pointer(tmpStr)^, Length(tmpStr));
ms.Position := 0;
//loding data to the paramater using LoadFromStream
tmpQ.ParamByName('bfile').LoadFromStream(ms, ftBlob);
try
tmpQ.ExecSQL();
memLog.Lines.Add(Format('size %s Mb : OK', [edSize.Text]));
except
on E : Exception do
memLog.Lines.Add(Format('size %s Mb : FAILED. %s', [edSize.Text, E.Message]));
end;
ms.Free;
tmpQ.Free;
end;
I posted source code of my test application on this russian forum SQL.ru (http://www.sql.ru/forum/actualthread.as ... tid=952893). And I received answer from the user DEVART that suggested to use Large Object data type (lo) and set parameters OIDAsLargeObject and DetectParamTypes to true. But it didn't help me.
Then I try to use the answer from this post http://forums.devart.com/viewtopic.php? ... tea#p55869. But without results. When I try to insert data into field with type OID I received type cast error. Field with type OID is casted as ftInteger.
Questions:
1. Is there some example of inserting data to the field with Large Object(LO) data type?
2. What is the cause of casting OID data type as ftInteger?
P.S.
I didn't find ability to attach file to the message. But I can send the whole Delphi 7 project of my test application if it will help.