0

following the official documentation of phonegap / cordova on handling databases, I have implemented the code and it worked well for me for a single table, getting JSON data types with AJAX callback.

My problem is to implement this method with several tables.

Here's an example of my code for two tables, but it doesn't work:

index.html

<html>
    <head>
        <script src="js/updater.js"></script>
    </head>
    <body>
        <input type="button" onclick="update()" value="Update DB!"></input>
    </body>
</html>

updater.js

var transactions=[];
var jqxhr=null;
function update()
{
    jqxhr = $.ajax( "http://www.example.com/file.php?id=100" )
    .done(function(data) 
    { 
        data = JSON.parse(data);
        $.each(data.elements,function(index, item)
        {
            transactions.push("INSERT INTO elements VALUES('"+ item.id + "','"+ item.name + "','"+ item.tel + "','"+ item.mail +"')");
        });
    })
    .fail(function() { console.log("error"); })
    .always(function() { console.log("complete"); });
    jqxhr = $.ajax( "http://www.example.com/file.php?id=200" )
    .done(function(data) 
    { 
        data = JSON.parse(data);
        $.each(data.people,function(index, item)
        {
            transactions.push("INSERT INTO people VALUES('"+ item.id + "','"+ item.name + "','"+ item.nick + "','"+ item.gender +"')");
        });
    })
    .fail(function() { console.log("error"); })
    .always(function() { console.log("complete"); });
    var db = window.openDatabase("Database", "1.0", "Example", 2097152);
    db.transaction(populateDB, errorCB, successCB);
}
function populateDB(tx) 
{
    tx.executeSql('DROP TABLE IF EXISTS elements');
    tx.executeSql('CREATE TABLE IF NOT EXISTS elements (id INTEGER NOT NULL PRIMARY KEY, name TEXT(32), tel TEXT(20), mail TEXT(64))');

    tx.executeSql('DROP TABLE IF EXISTS people');
    tx.executeSql('CREATE TABLE IF NOT EXISTS people (id INTEGER NOT NULL PRIMARY KEY, name TEXT(32), nick TEXT(20), gender TEXT(10))');
    for (var i=0;i<transactions.length;i++)
    {
        tx.executeSql(transactions[i]);
    }
}
function errorCB(err) 
{
    alert("Error processing SQL: "+err.code);
}
function successCB() 
{
    alert("Database updated!");
}

Could you help me, please? Thanks!

candlejack
  • 1,189
  • 2
  • 22
  • 51

1 Answers1

1

By default, all $.ajax requests are sent asynchronously, so i suppose the problem is that 

transactions.push("INSERT INTO people...

invokes after writing to database is done.

You can put second request at the end of "done" function of first request, and writing to database at the end of "done" function of second request. It looks quite ugly, even if you split it in several functions, but it should work.

Using of $.Deferred improve a little how code looks like: http://api.jquery.com/category/deferred-object/

jQuery.ajax "async" (which you can set to "false") setting is deprecated since JQuery 1.8, so it's not a good option. https://api.jquery.com/jQuery.ajax/

UPDATE. Code for the first option (only important part):

function update()
{
    jqxhr = $.ajax( "http://www.example.com/file.php?id=100" )
    .done(function(data) 
    { 
        data = JSON.parse(data);
        var elementsCount = data.elements.length;
        $.each(data.elements,function(index, item)
        {
            transactions.push("INSERT INTO elements VALUES('"+ item.id + "','"+ item.name + "','"+ item.tel + "','"+ item.mail +"')");
            if (index == elementsCount - 1) //making sure all elements are added to array
            {
                updateSecond();
            }
        });
    });
}

function updateSecond()
{
    jqxhr = $.ajax( "http://www.example.com/file.php?id=200" )
    .done(function(data) 
    { 
        data = JSON.parse(data);
        var elementsCount = data.people.length;
        $.each(data.people,function(index, item)
        {
            transactions.push("INSERT INTO people VALUES('"+ item.id + "','"+ item.name + "','"+ item.nick + "','"+ item.gender +"')");
            if (index == elementsCount - 1) //making sure all elements are added to array
            {
                dbWrite();
            }
        });
    });
}

function dbWrite()
{
    var db = window.openDatabase("Database", "1.0", "Example", 2097152);
    db.transaction(populateDB, errorCB, successCB);
}

Code definitely doesn't look like ideal, but it should work properly.

Regent
  • 5,142
  • 3
  • 21
  • 35