CODEWITHSUNDEEP
mysqlsencha-touch
timmyc
timmyc

Reputation: 522

Storing Base64 PNG in MySQL

I am using Sencha Touch to capture data from a user on an iPad. This includes a standard form (name, email, etc.) as well as the customer's signature (see the plugin here).

Essentially, the plugin takes the coordinates from the user's signature and gives me back Base64 PNG data.

Once I have the signature data, I want to store it. My two questions are:

  1. Should I store the Base64 data in my (MySQL) database along with the rest of the user's information, or should I create a static file and link as necessary?

  2. If storing in the database is the way to go, what data type should I use?

Upvotes: 6

Views: 13736

Answers (4)

ilidiocn
ilidiocn

Reputation: 390

Set your field as Blob data type, it stores perfectly base64EncodedString

Upvotes: 1

gertas
gertas

Reputation: 17145

Just save files in BLOB field. Such PNG file shouldn't be larger than 1KB if you turn some optimizations (grayscale or B/W).

Storing files outside DB seems easy but there are things to consider:

  • backup,
  • additional replication if multi-server
  • security - access rights to files dir, but also to files,
  • no transactions - e.g. DB insert ok but file write fails,
  • need to distribute files within multiple directories to avoid large dir listings (depends on filesystem capabilities)

Upvotes: 4

Marc B
Marc B

Reputation: 360772

There's no need to base64 encode the image. MySQL's perfectly capable of storing binary data. Just make sure you use a 'blob' field type, and not 'text'. text fields are subject to character set translation, which could trash your .png data. blob fields are not translated.

As well, base64 encoding increases the size of text by around 35%, so you'd be wasting a large chunk of space for no benefit whatsoever.

However, it's generally a bad idea to store images in the database. You do have the advantage of the image being "right there" always, but makes for absolutely huge dumps at backup time and all kinds of fun trying to get the image out and displayed in your app/web page.

it's invariably better to store it externally in a file named after the record's primary key for ease of access/verfication.

Upvotes: 9

k to the z
k to the z

Reputation: 3185

Blob will store Base64. It will get you what you need. Storing it in the database gives you built in relational capabilities that you would have to code yourself if you stored it in a static file. Hope this helps. Good luck sir.

Edit: mark's right about binary v. base 64

Upvotes: 3

Related Questions