Loading PDF in MySQL database community edition

Databases
1

Dear All,

I am working on a project requiring PDFs to be loaded into MySQL database (community edition). We can’t simply store PDFs in a file location to be reached by the client application as the client application is on a microsoft tablet, which is only allowed to communicate with a MySQL server (port 3306 is only open port).

I googled to below code together but keep on getting error message:

DB Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Blob) values ('aVCCT1J3MEtHZ29 …

I have tried different combinations in both the database and in the code:

NewRow.Column(“Blob”).StringValue=EncodeBase64(PDFString) NewRow.Column(“Blob”).BlobValue= …

Column “Blob” defined in table as Stringvalue as well as Blobvalue.

The error message is very consistent.

It seems that the following code and its translation to SQL to talk to MySQL is not compatible with Blobvalues: db.AddRow(“BlobTabel”,NewRow)

I have also tried to first create a new record in the table and then use Select query, with Rowsfound1.editrow Rowsfound1.column(“Blob”).blobvalue = EncodeBase64(PDFString) Rowsfound1.saverow

I also used it without EncodeBase64 as this is already done earlier in the function PDFToBlob (Included at the bottom).

I have downloaded the MBS add in but have not used it yet as I believe this should be possible to do with standard Xojo.

Can you please let me know how to resolve this issue?

Thanks and cheers,

GT

Code to pickup PDF from filesystem and saving it into MySQLCommunityServer database:

var PDFString as String Var f As FolderItem var db as new MySQLCommunityServer

var NewRow as new DatabaseRow

db.host = TifaCalcVariables.TFDataBaseServerGlobal db.port = TifaCalcVariables.TFDataBaseServerPortGlobal db.DatabaseName= TifaCalcVariables.TFDatabaseNameGlobal db.UserName = TifaCalcVariables.TFGebruikerGlobal db.Password = TifaCalcVariables.TFWachtWoordGlobal

db.TimeOut=15

db.Connect

f = folderitem.showopenfiledialog(“*.pdf”)

PDFString = app.PDFtoBlob(f)

NewRow.Column(“Blob”).StringValue=EncodeBase64(PDFString)

try

db.BeginTransaction db.AddRow(“BlobTabel”,NewRow)

db.CommitTransaction

Catch error As DatabaseException MessageBox("DB Error: " + error.Message) End Try

db.close

Function app.PDFtoBlob:

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

2

Well, if you have a BLOB field in MySQL (actually a LONGBLOB), you would directly store PDF data there and skip all the Base 64 encoding.

e.g.

Var row As New DatabaseRow

// ID will be added automatically
row.Column("Name").StringValue = name
row.Column("PDF").StringValue = PDFData

Try
  mDB.AddRow("PDFFile", row)
Catch e As DatabaseException
  AddDataStatusLabel.Text = "DB Error: " + e.Message
  Return False
End Try
1 Like
3

Dear Christian, Thanks a lot for trying to help me out. Highly appreciated!
However, i do get exactly the same error message as before:

DB Error: You have an error in your SQL syntax; check the manual that corresponds to your MYSQL server version for the right syntax to use near 'Blob) values ('xdfladlfady') at Line 1

It seems to me that there is an issue in the background with the communication between Xojo and MySQL. I am not using any SQL statements to update the Blob column, but the standard Xojo functionality as you describe above.

So I am still stuck with the same issue. Any other options?

4

Can you give more information, like:

  • is this a desktop project or web project
  • MySQL version
  • do you have any other problem with MySQL or is only with Blobs
  • can you create a sample project that you can share with dropbox, google drive, etc. that can show the problem
5

Hi Alberto, this is a desktop project for the Mac. MySql database version 8.0.32 (MySQL Community Server - GPL). compiled for Macos13 (x86-64).
This is the only issue I have with MySql. I can create a sample project to share that I can we-transfer to anyone. Thanks for helping out!

6

Maybe better use BlobValue instead of StringValue.
And the field type is LONGBLOB?

7

I used both Blobvalue and Stringvalue, and used field type Longblob, mediumBlob and Longtext (in every combination)…

8

BLOB is a reserved word. Use a different name for the column.

3 Likes
9

Dear Tim, That was the solution! Thanks a million!!! It works now perfectly fine.

10

Can you mark Tim’s comment as the solution?

You should see a Square with a check mark in it below Tim’s comment, you just need to click that to make it active.

11

How do you display a PDF saved in a database again?
Is my attempt to temporarily save it and display it using the PDF reader correct or is there a better solution?
My attempt:
https://www.dropbox.com/scl/fi/z25b1nwy3ka0xbj9yltm8/test-PDF-als-string.xojo_binary_project?rlkey=8voapdd1mayvwtxaasbv9d1at&dl=1

12

There are a ton of ways. e.g. you could render picture for a PDF page with PDFKit, DynaPDF or WindowsPDF classes in MBS Xojo Plugins.

Or use controls like PDFViewControlMBS for macOS, PDFViewIOSControlMBS, WinPreviewControlMBS for Windows or QLPreviewViewControlMBS for macOS.

13

Open this example in Xojo to see how the PDF is shown using a Canvas:
image

it may help. (Maybe only for PDF created with Xojo?)

14

You are correct with this – it is only for PDFDocument created in code. The official blog post on the matter is an inefficient HTMLViewer solution that isn’t really exemplary. The real answer is there is no built in way to do so. To display a PDF in the most battery friendly way possible, without including all of chromium embedded framework in your app, is to use MBS plugins.

1 Like