Updating BLOB field by chunks ??

[Michał Ściwiarski]

Is it possible to update the BLOB field in firebird with large pdf file divided by chunks, similar to MS SQL method UPDATE at "varbinary(max)" field using (WRITE (expression,@Offset,@Length)) ?

I use Delphi 2007 (32-bit) with IBQuery component to load large pdf files to database. I have a problem with "Out of memory" if the file size is bigger then 600MB. I'd like to bypass the 32-bit delphi memory manager problem and divide the file by small 100MB chunks (using TMemoryStream) and append it to the BLOB filed in loop.

Thanks for advice.

Michał

[Dimitry Sibiryakov]

08.04.2021 12:47, Michał Ściwiarski wrote:
> I use Delphi 2007 (32-bit) with IBQuery component to load large pdf files to database. I
> have a problem with "Out of memory" if the file size is bigger then 600MB. I'd like to
> bypass the 32-bit delphi memory manager problem and divide the file by small 100MB chunks
> (using TMemoryStream) and append it to the BLOB filed in loop.

The problem is in Delphi components. For unknown reason TIBBlobStream is caching whole
BLOB in memory. Using TMemoryStream you double that amount of requested RAM and thus run
out of address space.
The solution is not to use TIBBlobStream but write the file through Firebird ISC API
directly then bind received BLOB ID to the query's parameter.

--
WBR, SD.

[Mark Rotteveel]

No you can't, but I'd be surprised if Delphi didn't have ways to stream
data directly from disk to a blob, without having to load the entire
file into memory first (e.g. using a TFileStream instead of a
TMemoryStream).

I don't know Delphi however, but otherwise you could of course always
append to a blob, using something like `update ... set blobcolumn =
blobcolumn || ?`, but I would only do that as a last resort, as this
would be inefficient compared to streaming from the file to the blob.

Mark

[Michał Ściwiarski]

No you can't, but I'd be surprised if Delphi didn't have ways to stream
data directly from disk to a blob, without having to load the entire
file into memory first (e.g. using a TFileStream instead of a
TMemoryStream).

Unfortunately Delphi 2007 does not offer the direct stream method with IBquery. FireDac in current Delphi version can do that.

Best regards

Michal 

[Michał Ściwiarski]

The solution is not to use TIBBlobStream but write the file through Firebird ISC API
directly then bind received BLOB ID to the query's parameter.

 Could you please give me some example or documentation how to use this method?  

Best regrads

Michal

[Dimitry Sibiryakov]

08.04.2021 13:07, Michał Ściwiarski wrote:
>  Could you please give me some example or documentation how to use this method?

%FIREBIRD%\examples\api\api8.c

--
WBR, SD.

[Dimitry Sibiryakov]

08.04.2021 13:09, Michał Ściwiarski wrote:
> Unfortunately Delphi 2007 does not offer the direct stream method with IBquery.

Do not use TIBQuery. Use TIBSQL.

--
WBR, SD.

[liviuslivius]

Hi

Not directly releated to your question, but it is not good to have large binary objects inside database itself. Better use some share and in the database contain only link to it.

Regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: Michał Ściwiarski <msciw...@gmail.com>

Data: 08.04.2021 12:51 (GMT+01:00)

Do: firebird-support <firebird...@googlegroups.com>

Temat: [firebird-support] Updating BLOB field by chunks ??

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/0a6e116d-b870-450d-88d2-41a7a00f86d3n%40googlegroups.com.

[Michał Ściwiarski]

Yes I know... but Im not a developer of this database.. I only need to load the data (huge amount of pdf files) to it...

Cheers

Michal