How to insert image in sqlite?

Question

I am developing a cross platform application using cordova.
I need to insert image inside sqlite. I am getting lots of code for android but I find it difficult to do with javascript. I am getting err.code:5 when I run the following code in my iPhone.

document.addEventListener("deviceready", onDeviceReady, false);
var img;
var currentRow;
var b = new Blob();
function previewFile() {
    // var preview = document.querySelector('img');
    var file    = document.querySelector('input[type=file]').files[0];
    var reader  = new FileReader();
    // var package_name = document.getElementById("pr").value;

    reader.onloadend = function () {
        // img = reader.result;
        if(file.type.match('image.*'))
        {
            img = reader.result;
            // ref.push({"image":image,"service":arr,"package_name":package_name});
        }
        else
        {
            alert("select an image file");
        }
    }

    if (file) {
        reader.readAsDataURL(file);
    } else {
        preview.src = "";
    }
    var image1 = encodeURI(img);
    // var b = new Blob();
    b = image1;

    console.log(b);
    console.log(image1);
    //document.write('<img src="'+image+'"/>');
}
function onDeviceReady() {
    var db = window.sqlitePlugin.openDatabase({name:"sqlite"});
    db.transaction(populateDB, errorCB, successCB);
}
function populateDB(tx) {
    tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id INTEGER PRIMARY KEY AUTOINCREMENT, name,number,image BLOB)');
}
function insertDB(tx) {
    tx.executeSql('INSERT INTO DEMO (name,number,image) VALUES ("' +document.getElementById("txtName").value
                    +'","'+document.getElementById("txtNumber").value+'","' +b+ '")');

}
function goInsert() {
    var db = window.sqlitePlugin.openDatabase({name:"sqlite"});
    db.transaction(insertDB, errorCB, successCB);
}

My html code:

<input type="file" onchange="previewFile()">    
<button onclick="goInsert()">Insert</button>

How to do this. Can someone help me? Thanks in advance...

This thread has the answer: http://stackoverflow.com/questions/11790104/how-to-storebitmap-image-and-retrieve-image-from-sqlite-database-in-android — Brad L., Dec 06 '15 at 09:52
Thank you @ Brad L. I referred these thread and found it useful. But I am having problem reading my image as bitmap. Can you please help me? — Anu, Dec 07 '15 at 03:59
This may help you figure things out. http://stackoverflow.com/questions/7375635/xhr-send-base64-string-and-decode-it-in-the-server-to-a-file — gro, Dec 09 '15 at 18:13
Yes. Cordova evolved from using frames to xhr over the years, and has embraced using xhr on a go-forward basis — gro, Dec 10 '15 at 20:57
see http://stackoverflow.com/questions/15970446/how-to-save-captured-picture-into-sqlite-as-blob-and-retrieve-back-in-phonegap — gro, Dec 12 '15 at 16:00
Thank you so much @gro... this helps me. But for now I dont want to capture image from camera and insert it instead I need to get images from gallery. Will that solution work? — Anu, Dec 13 '15 at 14:46
Yes, it should. The source doesn't matter, just what file format you end up working with. — gro, Dec 13 '15 at 19:49
Thank you @gro...Can you just make a answer to this post with some working example. Please? — Anu, Dec 14 '15 at 04:04

score 0 · Answer 1 · answered Dec 14 '15 at 10:35

0

Convert your image (in memory) to a byte[] and then save it your sql db as varbinary(max).

answered Dec 14 '15 at 10:35

what's the difference between this approach and blob type? – gumuruh Aug 20 '20 at 04:18

score -1 · Answer 2 · answered Dec 14 '15 at 10:45

Inserting blob into database will make your application slow as well as laggy, instead save the path of the selected picture into the database.

And when you want to upload the image to the server use fileupload feature of cordova.

If you are getting image from server than download that image locally and save that path into the database

window.requestFileSystem(LocalFileSystem.PERSISTENT, 0,
    function onFileSystemSuccess(fileSystem)
    {
        fileSystem.root.getFile(
            "dummy.html", {create : true, exclusive : false},
            function gotFileEntry(fileEntry)
            {
                var sPath = fileEntry.fullPath.replace("dummy.html", "");
                var fileTransfer = new FileTransfer();
                fileEntry.remove();

                fileTransfer.download(
                    "http://www.w3.org/2011/web-apps-ws/papers/Nitobi.pdf",
                    sPath + "theFile.pdf",
                    function(theFile)
                    {
                        console.log("download complete: " + theFile.toURI());
                        showLink(theFile.toURI());
                    },
                    function(error)
                    {
                        console.log("download error source " + error.source);
                        console.log("download error target " + error.target);
                        console.log("upload error code: " + error.code);
                    }
                );
            }, fail);
    }, fail);

How to insert image in sqlite?

2 Answers2