Save any kind of file binary to database and read again

Hi everyone,

i need to save any kind of file (e.g. .pdf, jpg, docx, dwg) binary to a database and read and open it back again.
Is this possible with Xojo?

I know it deals with binary streams and blob field, but don’t know how to do it? Any examples?

Hi @Kai_Mummert1

Take a look in the Examples that come with Xojo, I know there is a BLOB example for SQLite

Some DB’s are different as I think Postgres has Large Objects?

You can also write the data as TEXT after converting it to some encoded format like Base64 or Hex.

2 Likes

Hi Kem,
this sounds interesting with Base64, but i can’t find any examples but with strings.
I need to encode other filetypes, i’m sure i can do this, but how can i decode them and open them again with the default app for this fileformat?

please let bytes , bytes, store it as binary or blob types.
because any string conversion took time and can corrupt data.

but how can i decode them and open them again with the default app for this fileformat?

its encode and decode then
binary->String as Base64->binary

Thanks to all, but i’m still a little confused. Sorry…

So let’s be practical:

1.) I load a file with a folderitem
2.) stream it with a binary stream

stream = BinaryStream.Open(f, True)
3.) save it to a string

data = stream.Read(stream.Length)
4.) close the stream and write this data-string to a MySQL database blob

but now?

How do i read this data from the blob and how do i write to memory for opening the file again.

Excuse the knot in my brain…

However you store the data in the database (BLOB or encoded string), you will also need to store the meta-data so you can properly restore it. At the least, that’s the file name.

To restore, you’d fetch the filename and data from the database, create a new empty file (BinaryStream.Create) with the right name, then write the data to that file.

If you want to restore the file exactly (same creation and modification date), and maybe save space too, you could:

  1. Zip the file to a temporary file.
  2. Read the zip file contents and store that to the database (again, BLOB or encoded string).

You would not need any other data about the zip file since it’s transitory.

To restore:

  1. Read the data from the database.
  2. Create a temp file and write the data to it.
  3. Unzip the temp file to its final destination.

On Mac, you can use zip or ditto through a Shell to zip/unzip. On Linux, zip. On Windows, you can look for code on the forum that will tell Windows to do it for you. Or you can use MBS or Thomas Templeman’s zip package on all platforms.

Here is an example of a function to encode a pdf file into a string to be written to a blob record in a database:

Private Function PDFtoBlob(f as FolderItem) as String
Dim PdfContents as String =“”
dim bs as BinaryStream = BinaryStream.open(f)
dim sBlock as string = bs.Read(bs.Length)
bs.Close
PdfContents=EncodeBase64(sBlock)
sBlock=“”
Return PdfContents
End Function

Here is an example of a function to return the encoded string back to a pdf file

Private Sub BlobtoPDF(PdfContents as String, f as FolderItem)
Dim mb as MemoryBlock
mb=DecodeBase64(PdfContents)

dim bs as BinaryStream = BinaryStream.Create(f,True)
bs.Write(mb)
bs.Close

End Sub

Hope that helps.

1 Like

Don’t need to save a Zip file to store compressed blobs. …

There are undocumented classes in Xojo that are very unlikely to change (and should eventually be exposed): _gzip and _gunzip. Very handy.

I use them so i do not need to save temp files for DB blob type stuff…

I also use them with largish files my current app saves … They are JSON zipped in memory that I then store with a binary stream, with an app specific extension.

-Karen

Thanks to all, these are all informations that i needed