How do I add various document types to a Firebird BLOB field with Delphi

Question

Background: Our product includes a document management system that allows users to save and retrieve various document types (.PDF, .DOC, .JPG, .PNG, .TIF, etc.). Currently there is a record in the database that stores document information, but the documents themselves are stored on the server's hard drive. The database record includes the disk file name so it can be retrieved and displayed. We now need to migrate all the documents into the database. For some of our clients, that could mean thousands of individual document files. We are aware of the pros and cons of having the docs in the DB, but still need to migrate them to the DB.

The plan: I plan to loop through the table containing all the document information. Each record now has a new, empty BLOB field, and a text field to hold the file extension (so that I know how to display it later). The plan is to retrieve the file and save it in the new BLOB field, along with its extension, as I loop to the next record/document.

Question: The articles I've read so far use Streams to create a totally new record. The type of stream depends on the article (TMemoryStream, TFileStream, TBlobStream). Since I already have a record, I'm interested in knowing the best way to fill the BLOB field. I would appreciate it if someone can offer an example of Delphi code that would add such a document to a Firebird database. I've been coding for too many years to count, but have never had the opportunity to work with BLOB streams.

Answer 1

You can use your DataSet's CreateBlobStream() method to access a BLOB field of an existing record. You can write whatever you want to that stream.

Answer 2

Iterate through your Query:

Query.close;
Query.sql.text := 'select * from ATable';
Query.open;
While not Query.eof do
Begin
if FileExists(Query.FieldbyName('DocumentName').asstring) then
Begin
Query.Edit;
TBlobField(Query.Fieldbyname('Document').LoadFromFile(
Query.FieldbyName('DocumentName).asstring);
Query.post;
end;
Query.next;
end;