DB storing of Blob Streams

[lexdean]

I have created a virtual memory block that layout data much like a registry with keys and records in Delphi. You could say it’s much like a Mongo database as in PHP or Java for web development.
The database holds streams, Wide and Delphi strings, integer’s, Words, Extended, Double, currency, Font data, and I can add on any type Delphi record I wish. It labels each record with a Delphi string 16 characters long at the moment.
I’m able to separate a key with sub keys and records into a separate memory block with a single procedure. And when a record is changed its key stores a change has been made. This is to update a blob in a file record before removal.

Now I’m looking for options of how to store data to files.
a) I could use MySQL but that has a fee to use the cluster version. And I have no direct connection to the MySQL LIB.DLL and store data as a blob. Has anyone seen such code?
b) I also looking for something equal to MySQL in my own Delphi code. And I’m thinking to create a cluster version at a later date.
I’m thinking the database may use separate files to keep the blobs of data more accessible than using a single file. As to update a Blob of data will take a rebuild of the file.
I see two options so far:- I’m thinking for reliability it could be better to create a new file, copy data to the top of the updated blob, then copy the blob, then copy the data after the blob, delete the old file and rename the new file to replace it. The other option is to copy data after a blob, then truncate the file to the start of the blob, then write the new blob, then rewrite the end of the file again. The problem is this could be messier to recover from if power is lost but faster implementation. I’m thinking of a management recording system to assist data recovery if power is lost.

any advice please.

Lex Dean

[Ian Hinson]

I know that this reply is too late for the OP, but here's a way to store binary in a database that does not support BLOBs.

1) Create a table that stores line numbers and strings up to length 80.
eg. CREATE TABLE MyBLOBS (ID LONG, LineNo LONG, Base64 TEXT(80),
CONSTRAINT PK_MyBLOBS PRIMARY KEY (ID, LineNo));

2) Use the EncdDecd unit in Delphi to encode the binary into Base64.
Each line will be no bigger than 76 characters wide.

3) Work through each line of the Base64 (eg. using a TStringList) and store it into the database line by line.
You need to include the LineNo of the string you are storing to so you can retrieve it again sequentially.

When you need to get the binary back out of the database just use
SELECT Base64 FROM MyBLOBS
WHERE ID = ?
ORDER BY LineNo;
You then load each line into a TStringList, and decode TStringList.Text back to binary.

No need then to pay for a blob-compatible version of MySQL