How can I insert a json object into a sqlite table in javascript, phonegap (cordova)

Question

I'm trying to implement a cache using sqlite in javascript. I have a json object that I'm trying to cast into a string and input into the database but keep getting a syntax error.

the table consists of two fields a md5 string and a json string, heres how I define the db and table

   db = window.openDatabase("Database", "1.0", "Cordova Demo",10485760);

    db.transaction(function(tx){
         tx.executeSql('DROP TABLE IF EXISTS CACHE_DATA');
         tx.executeSql('CREATE TABLE IF NOT EXISTS CACHE_DATA (md5 TEXT UNIQUE, data TEXT)');
    },function(tx,error){
        console.log('tx error: ' + error);
    },function(){
        console.log('tx success');
    });

This is how I'm trying to input the data where the variable d.data is a json object.

   var jsonString = JSON.stringify(d.data, null, '');

            db.transaction(function(tx){
                tx.executeSql('INSERT OR REPLACE INTO CACHE_DATA (md5, data) VALUES ("'+hash+'", "'+jsonString+'")');
            },function(tx,error){
                console.log(JSON.stringify(tx.message));
                console.log(JSON.stringify(error));
            },function(){
                console.log('tx success');
            });

             console.log(jsonString);

Which will throw and error

08-27 23:19:55.702: E/SQLiteLog(29831): (1) near "networks": syntax error

The actual string I'm tring to input looks like this

08-27 23:19:55.652: D/CordovaLog(29831): {"networks":[{"id":"66","name":"Test"}],"expires":1346138396}

I was thinking it had something to do with the quotes in the json string but the field type is just text so I'm not sure what the syntax error could be.

Any ideas?

Brian · Accepted Answer · 2012-08-28 11:13:43Z

7

I figured out the JSON string needed to be escaped after it was turned into a string

            var jsonString = escape(JSON.stringify(d.data));

            db.transaction(function(tx){
                tx.executeSql('INSERT OR REPLACE INTO CACHE_DATA (md5, json, expires) VALUES ("'+hash+'", "'+jsonString+'","'+expireStamp+'")');
            },function(tx,error){
                console.log(JSON.stringify(tx.message));
                console.log(JSON.stringify(error));
            },function(){

                });

answered Aug 28, 2012 at 11:13

Brian

4,42815 gold badges64 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Samuli Hakoniemi · Accepted Answer · 2012-08-28 08:05:05Z

3

Have you tried passing them as an array:

var valuesInArray = [hash, JSON.stringify(d.data, null, "");

tx.executeSql("INSERT OR REPLACE INTO CACHE_DATA (md5, data) VALUES (?, ?)", valuesInArray, function(tx, result)  {
  // Success Callback
});

edited Aug 28, 2012 at 8:05

answered Aug 28, 2012 at 7:55

Samuli Hakoniemi

19.1k1 gold badge64 silver badges79 bronze badges

Comments

Ajay Gupta · Accepted Answer · 2018-02-06 10:05:14Z

1

We can also try encodeURI() and decodeURI(). Here is the quick example:

var jsonString = encodeURI(JSON.stringify(d.data, null, ''));

        db.transaction(function(tx){
            tx.executeSql('INSERT OR REPLACE INTO CACHE_DATA (md5, data) VALUES ("'+hash+'", "'+jsonString+'")');
        },function(tx,error){
            console.log(JSON.stringify(tx.message));
            console.log(JSON.stringify(error));
        },function(){
            console.log('tx success');
        });

And for decode

var data = JSON.parse(decodeURI(sqlData.data));

Note: escape() and unescape() may be work. But escape() function was deprecated in JavaScript version 1.5.

answered Feb 6, 2018 at 10:05

Ajay Gupta

2,98725 silver badges28 bronze badges

Comments

Shehram Tahir · Accepted Answer · 2017-07-22 19:35:18Z

0

@Brian

First Question - Is that possible to convert a json object into string and store that string into sqlite database ?

Second Question

If the json is containing urls of mutiple images. Then in online mode it will fetch the url images from the web. but if we have stored those urls in sqlite database then it is possible to show those images in offline mode too in cordova apps ?

if yes then how we will store those images

begginer in cordova

help required in transforming the online app into offline app. ionic-angular 2 app

answered Jul 22, 2017 at 19:35

Shehram Tahir

1,0712 gold badges11 silver badges20 bronze badges

Collectives™ on Stack Overflow

How can I insert a json object into a sqlite table in javascript, phonegap (cordova)

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related