SQLite Blob data to mySQL blob data field

Databases
1

Local workstations have a SQLite database. I can add/edit/delete records including inserting a record
and then editing that record to enter blob data which is a PDF read in with BinaryStream.

These local workstations (Windows 7,10, Apple Mac OSX) can read the PDF files of each other no
problem.

My hosting provider provides me with a mySQL database which I want to use as a clearing house server
(using it as a test database server for now). The records entered into any local SQLite database, then uploaded to the mySQL server database, can be retrieved from this mySQL database to any of the other workstations .

However, while I have no problem putting the PDF into the local SQLiteBlob column (and reading it back out on
any workstation), I have not been able to find help in writing the code for copying the SQLite blob data to the
mySQL server (Insert data into mySQL blob field from SQLiteBlob field)

Having said that, I know that Christian has a plugin that will do that but before I spend $149 for
that single function, I want to explore if there is a way to do it with Xojo code.

Thanks

Lee Miller

2

I would encode the blob data into a string format (you can keep the field type as blob) and then when you want the data out of the field, retreive the string, and decode it into the blob format. MBS has several plugins to do it easily. You can write you own with using EncodeHEX/DecodeHEX.

3

Scott: So are you saying copying data between a SQLiteBlob field and a mySQL blob field just can;t be done in binary format in Xojo without an assortment of 3rd party add ins?

4

Not at all
Base64 Encode the data so YOU control the “blob format” and then put the base 64 data in either SQLite’s blob column or mySQL’s
It does mean you have to decode the data when you want to show the PDF BUT you have full control over endianness and every other aspect of how a “blob” is handled
When you just use the native “blob” format of whatever database you lose that control

5

and this is where I have seen issues. Converting it to a “string” means you have 100% control over the data and its integrity.

6

Lee is using ActiveRecord so I sent him an example of how we do it.

[code]dim bs as BinaryStream = BinaryStream.open(f)

dim iBlockSize as integer = 1024 * 512 'copy in 1/2 megabyte chunks
dim iBlockCt as integer = Ceil(bs.Length / iBlockSize) 'figure out how many chunks there are

for i as integer = 1 to iBlockCt

dim sBlock as string = bs.Read(iBlockSize)

dim oDocBlob as new DataDocument.t_documentblob
oDocBlob.iDocument_ID = me.iDocument_ID
oDocBlob.iDocumentSequence = i
oDocBlob.save

oDocBlob.SaveChunk(sBlock)

next

bs.Close[/code]

The SaveChunk does this:

[code] dim sql as string = “SELECT * FROM t_documentblob WHERE DocumentBlob_ID=” + Str(iDocumentBlob_ID)
dim rs as RecordSet = DataDocument.DB.SQLSelectRaiseOnError( sql )
if rs.EOF then
break
return
end if
rs.Edit
rs.Field(“DocumentBlob”).StringValue = EncodeBase64(sContents)
rs.Update
if db.Error then
dim s as string = DataDocument.DB.ErrorMessage
raise new BKS_Database.DatabaseException(db )
end if

db.Commit[/code]

It’s not elegant but it’s worked well on several projects.