mORMot Open Source

algalg

Member

Registered: 2020-05-18

Posts: 8

SynDB - Oracle OCI - Big Blob insert update

Hello I am new to mORMot Framework and SynDB and I am looking to use SynDB  to replace existing ADO connection to Oracle and MSSQL in an existing application.

First I am working with Oracle and looking at the options in working with BLOBs.

To insert / upload blobs to database I only see the option to bind the blob to a parameter of SQL statement, but this is limited by the MAX_INLINED_PARAM_SIZE = 32*1024*1024; constant to 32MB.

Is there a way in SynDB  to post large BLOB (2 GB) into Oracle in chunks?

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: SynDB - Oracle OCI - Big Blob insert update

Current SynDBOracle implementation limits BLOB to MaxInt (2Gb), MAX_INLINED_PARAM_SIZE  is only for array parameters (array of BLOB).

But be carefully -  Oracle itself is extremely slow with BLOB/CLOBs, especially with such large.

algalg

Member

Registered: 2020-05-18

Posts: 8

Re: SynDB - Oracle OCI - Big Blob insert update

Thanks, I see this in the code now.

But what is the best way to get 2GB file into Oracle blob field using SynDb? Load it into memory and bind and parameter using the BindBlob method?  Is there anyway to load the blob in chunks and avoid using all the RAM?

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: SynDB - Oracle OCI - Big Blob insert update

IMHO best way is not to store such huge BLOB in DB. Currently the only way to bind blob in SynDBOracle is load it into memory and bind and parameter using the BindBlob.

algalg

Member

Registered: 2020-05-18

Posts: 8

Re: SynDB - Oracle OCI - Big Blob insert update

Thank you for the explanation. I agree that it would be best to not to store huge blobs in DB, but I don't really have a choice at the moment.

Anyways, I can work with BindBlob and use Oracle DBMS_LOB.APPEND function to move the file from client to DB server in chunks.

algalg

Member

Registered: 2020-05-18

Posts: 8

Re: SynDB - Oracle OCI - Big Blob insert update

Hello,

I am getting back to this. I have decided to implement chunked reading and writing of blobs from and to a TStream. 

The initial implementation is now checked in to https://github.com/algalg/mORMot commints:

https://github.com/algalg/mORMot/commit … 1e2b1a13e4
https://github.com/algalg/mORMot/commit … dce5dcd97f

The use is:

For reading

var SQLDBRows: ISQLDBRows;
    FileStrm : TFileStream;
begin
  SQLDBRows := FConnection.Execute('select a from T', []);
  SQLDBRows.Step();
  FileStrm := TFileStream.Create('c:\test.txt-syn', fmCreate);
  try
    SQLDBRows.ColumnBlobToStream(0, FileStrm);
  finally
   FileStrm.Free;
  end;
end;

For writing

var SQLDBRows: ISQLDBRows;
    FileStrm : TFileStream;
begin

  FConnection.ExecuteNoResult('delete from T',[]);  // just to make sure we have only one row in the table for the demo
  FConnection.ExecuteNoResult('INSERT INTO T (a)  VALUES (EMPTY_BLOB())',[]);

  SQLDBRows := FConnection.Execute('select a from T for update', []);
  SQLDBRows.Step();

  FileStrm := TFileStream.Create('c:\test.txt', fmOpenRead or fmShareDenyNone);
  try

    SQLDBRows.ColumnBlobWriteFromStream(0, FileStrm);
  finally
    FileStrm.Free;
  end;
  SQLDBRows := nil;

I would like to pull this into the main branch after some tidying and commenting the code.  What is the process? Are in interested in this implementation?

I am planning to also implement this on the TOleDBStatement class.