Populate phonegap sqllite database with JSON array

Question

I'm trying to populate a phonegap sqlite database with a JSON array, the web service to get the JSON is working and the data is loaded just fine but when I try insert it into my table it keeps giving me an undefined sql error.

Here's my code:

function populateDB(tx) {
    //Setup database table
    tx.executeSql('DROP TABLE IF EXISTS Profiles');
    tx.executeSql('CREATE TABLE IF NOT EXISTS Profiles (id unique, name, email, phone)');
    //Get data
    var values;
    var base_url = "https://www.hoomz.nl/staging/index.php/api/";
    $.getJSON(base_url + 'profiles', function(result) {
        $.each(result, function(i, item) {
            console.log(item.name);
            console.log('(' + item.id + ', "' + item.id + '", "' + item.id + '", "' + item.id + '"),');
            values = values + '(' + item.id + ', "' + item.id + '", "' + item.id + '", "' + item.id + '"),';
        });
        console.log(values);
        tx.executeSql('INSERT INTO Profiles (id, name, email, phone) VALUES' + values + ';');
    });
    console.log(values);
    tx.executeSql('INSERT INTO Profiles (id, name, email, phone) VALUES' + values + ';');
}

Also gives this error: Uncaught InvalidStateError: An attempt was made to use an object that is not, or is no longer, usable.

Why do you have the last executeSql there? Looks like a mistake (but probably not THE mistake). — Raymond Camden
– Raymond Camden, Commented Mar 28, 2014 at 15:47

Surajit Sarkar · Accepted Answer · 2014-03-28 21:17:08Z

5

The error thrown in this case is that when SQL engine is trying to insert the values the object no longer exists.You should instead store the JSON results in a global variable and then do the SQL operations.Try the code below and see the results.

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script type="text/javascript" charset="utf-8" src="cordova.js"></script>
<script type="text/javascript" charset="utf-8">
   var db = null;
   var resultJSON;
   function insertIntoDB() {
       db.transaction(function (tx){
       tx.executeSql('DROP TABLE IF EXISTS Profiles');
       tx.executeSql('CREATE TABLE IF NOT EXISTS Profiles (id , name, email, phone)');
       var recursiveFunction = function(index) {
        if (index < resultJSON.length) {
            tx.executeSql('INSERT INTO Profiles (id, name, email, phone) VALUES (?, ?, ?, ?)',
               [resultJSON[index].id,resultJSON[index].name,resultJSON[index].email,resultJSON[index].phone], function (){index++; recursiveFunction(index)}, errorCB);
         }
      }
      recursiveFunction(0);
    });
   }

   function errorCB(err) {
    console.log("Error processing SQL: "+err.code+":"+err.message);
   }

   function onDeviceReady() {
     db = window.openDatabase("test", "1.0", "Test DB", 1000000);
     var base_url = "https://www.hoomz.nl/staging/index.php/api/";
     $.getJSON(base_url + 'profiles', function(result) {
      resultJSON = result;        
      insertIntoDB();
     });
    }
    document.addEventListener("deviceready", onDeviceReady, false);
  </script>

answered Mar 28, 2014 at 21:17

Surajit Sarkar

1,4449 silver badges12 bronze badges

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

2 Comments

Surajit Sarkar Over a year ago

@DennisvanMeel u r wc

Mahfud Muhammad Over a year ago

Should we put create db statement db = window.openDatabase() in onDeviceReady function? Can i put it in other place. Because i create cordova project with cli so function onDeviceReady() in other js file.

Raymond Camden · Accepted Answer · 2014-03-28 15:49:21Z

0

I think I see your issue. You are looping over each result and appending to values. After thew loop you do your insert. But if you had 2 or more results, then the values variable will look like

( stuff here)(stuff here)(stuff here)

You probably want that insert inside the each.

answered Mar 28, 2014 at 15:49

Raymond Camden

10.9k3 gold badges36 silver badges73 bronze badges

Comments

Rahul Kumar · Accepted Answer · 2014-03-28 17:27:53Z

You are doing a little bit wrong. you are working on iteration "for each loop" and you are inserting the value in string variable "Value" at each iteration that cause error because the insert contains 4 columns but "Value" contains the nth number of values try the below code:

 function populateDB(tx) {
    //Setup database table
    tx.executeSql('DROP TABLE IF EXISTS Profiles');
    tx.executeSql('CREATE TABLE IF NOT EXISTS Profiles (id unique, name, email, phone)');

    //Get data
    var values;
    var base_url = "https://www.hoomz.nl/staging/index.php/api/";
    $.getJSON(base_url + 'profiles', function (result) {
        $.each(result, function (i, item) {
            console.log(item.name);
            console.log('(' + item.id + ', "' + item.id + '", "' + item.id + '", "' + item.id + '"),');
            tx.executeSql('INSERT INTO Profiles (id, name, email, phone) VALUES(' + item.id + ', "' + item.id + '", "' + item.id + '", "' + item.id + '")');
        });
    });
}

Collectives™ on Stack Overflow

Populate phonegap sqllite database with JSON array

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related