Cordova SQLite save BLOB

Question

I have a problem with Cordova SQLite plugin.

How can I save BLOB image to SQLite?

I have BLOB object in JS:

Blob:{
size: 96874
type: "image/jpeg"
__proto__: Blob
length: 1}

And I trying to save it

INSERT INTO images (img) VALUES (?);

And when i trying to get this image:

SELECT img FROM images

I get this string:

img: "{"type":"image\/jpeg","size":96874}"

How can i convert it to BLOB again?

Also i trying to save images in base64, but i can't save big images to SQLite, because base64 string is very huge. (It's works with small images)

Please, help me and sorry for my English.

Answer 1

You cannot save JavaScript Blob objects to the SQLite Plugin. It doesn't support the Blob format. Yes I know it has a BLOB type, but confusingly it's not the same thing. :)

WebSQL/SQLite Plugin does support binary strings as an alternative to Blobs. However, you may run into issues because there are numerous bugs in the implementation on both iOS and Android (some details are here).

In PouchDB we worked around these issues, so the attachment API abstracts everything away for you. Follow the PouchDB attachment guide and it will convert Blobs for you and store them in the database. To create a PouchDB that talks to the SQLite Plugin, you will need to do the following:

/* prefer websql, which will actually use the SQLite Plugin if available */
var db = new PouchDB('mydbname', {adapter: 'websql'});
if (!db.adapter) {
  /* fall back to IndexedDB for FirefoxOS, Windows Phone, etc. */
  db = new PouchDB('mydbname');
}

If you want to convert between Blobs and binary strings or a variety of other formats, check out blob-util.

Answer 2

You could use blob literals and the hex function to treat blob values as hex strings at the interface between SQL and JavaScript, that works fine.