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?

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?

• javascript
• json
• sqlite
• cordova

4 Answers 4

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

                });

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

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.

@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