How to know that Web SQL already exist?

Question

I am making very simple application in cordova for ios/android. I have made it all but I am getting problem in database. I am trying to make sure that when app run it look for internet and if internet is available than it login using api, but it also create two databases one for user login(I know it is not good practice, but my requirement will search something to encrypt database) and save only current user in database. While other db will be used to store offline working of user. But when I am not clear how to know if my db is already made. Following are codes, kindly check, as I have search everywhere and seem like there is no solution except to check tables but when I do check tables my program stops to work.

When application start it asks userid, password than after validation following commands run.

document.addEventListener("deviceready", onDeviceReady(user, serviceURL, pass), false)

Following is function I am using on deviceready, if internet available it log in user and create db, if internet is not available it access db, but here is problem how I know if db exist so let login user otherwise alert him that internet is required for first time.

function onDeviceReady(user, serviceURL, pass) {
if(navigator.network.connection.type == Connection.NONE)
{
  var db = window.openDatabase(
                       'test',
                       '1.0.0',
                        'Test Database',
                        64 * 1024
                        );

      db.transaction(function (tx) {
      tx.executeSql('SELECT * FROM USERS', [], function (tx, results) {
      var len = results.rows.length, i;
      msg = "<p>Found rows: " + len + "</p>";
                                 }
                                 alert(msg);
                                 for (i = 0; i < len; i++){
                                 alert(results.rows.item(i).user );
                                 }
                                 }, null);
                   });
    }
else
{
    alert("Internet is available!!");


$.getJSON(serviceURL + 'APIjson.php?user='+user+'&pass='+pass+'&action=login',    function(data)
              {
              if(data.error==-1)
              {
              alert("Invalid user id or password.");
              return false;
              }
              else {
              //navigator.notification.activityStop(); // for android only
              // ActivityIndicator.hide(); // for IOS

              window.localStorage["user_id"] = data.id;

              var db = window.openDatabase(
                                           'test',
                                           '1.0.0',
                                           'Test Database',
                                           64 * 1024
                                           );

              db.transaction(function populateDB(tx) {

              tx.executeSql('DROP TABLE IF EXISTS USERS');
              tx.executeSql('CREATE TABLE IF NOT EXISTS USERS (id unique, user, pass)');
              var sql = 'INSERT INTO USERS (id, user, pass) VALUES (?, ?, ?)';
              tx.executeSql(sql,[data.id, user, pass]);
              },function errorCB(tx, err) {
              alert("Error processing SQL: "+err);                                 
                             },function successCB() {
                             alert("success!");                                 
                             });
              window.location.href = "contact.html";
              return true;
              }
              })
}
}

I also tried to add when database retrieve on len that howmany rows came and it should alert, but it failed and my app stop to work

   if (!len > 0) {
      alert("Sorry you need to connect internet for first time use");
                                 return false;

Answer 1

You should move the javascript redirect into the success callback:

db.transaction(function populateDB(tx) {
        tx.executeSql('DROP TABLE IF EXISTS USERS');
        tx.executeSql('CREATE TABLE IF NOT EXISTS USERS (id unique, user, pass)');
        var sql = 'INSERT INTO USERS (id, user, pass) VALUES (?, ?, ?)';
        tx.executeSql(sql,[data.id, user, pass]);
    },function errorCB(tx, err) {
        alert("Error processing SQL: "+err);                                 
    },function successCB(){
        alert("success!");                                 
        window.location.href = "contact.html";
});

The problem is that you are navigating away from your current page before the transaction has the chance to complete. The whole point of a call back is that it is called at a later time when the async task completes. However when you navigate away from the page, the webview will simply drop all tasks on the current page and move on.

Secondly, instead of a drop create you should use:

db.executeSql('CREATE TABLE IF NOT EXISTS tableName (someID text primary key, data text)');

To see if the data was added:

I suggest you use the chrome console to interact with the db. https://developers.google.com/chrome-developer-tools/docs/remote-debugging

You will need to write a couple db.executeSql(selectStatement) eg:

db.transaction(function(tx) {
  tx.executeSql("select * from table1 where theID ='"+theID+"';", [], function(tx, rs) {
for (var i=0; i < rs.rows.length; i++) {
  console.log(rs.rows.item(i));
  console.log('data found in db');
}
if(rs.rows.length < 1){
  console.log('data not in db');
    }
  },function(tx,e){console.log(e.message);});
});

To see the database, use the chrome debugger:

1. Connect your phone in debug mode/start emulator
2. In chrome go to this url: chrome://inspect/
3. Find your app and click inspect. A new window will open up.
4. Click on resources tab on top.
5. In the left pane click WebSQL (see screenshot)