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?

Answer 1

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(){

                });

Answer 2

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
});