asked on

Insert data into local DB on Android phone with PhoneGap

I'm trying out PhoneGap for the first time. All I want to do is add some data to the local database when a form is submitted.

The code that creates the DB is working fine but the one that runs when the form is submitted never inserts. I've tried using jQuery (ideally I'd like to use jQuery) $('#my_form').submit and like in the example below I've tried to add an event listener and while both will fire off an alert when they submit the data is never added to the DB.

What do I need to change to get the data to insert into the database?

<script type="text/javascript" charset=utf-8">
	var createDB = function() {
		if(!window.openDatabase)
		{
			alert('Database not supported!');
		}else{
			var db = window.openDatabase('mydb', '0.1', 'MyDB', 100000);
			db.transaction(createTables, errorCB, successCB);
			
			alert(db);
			
			document.getElementById('my_form').addEventListener("submit", insertData);
		}
	}
	
	var createTables = function(tx) {
		tx.executeSql('CREATE TABLE IF NOT EXISTS MyTable (id INTEGER PRIMARY KEY, name VARCHAR(255))');
	}
	
	function insertData() {
        db.transaction(populateDB, errorCB, successCB);
    }

    function populateDB(tx) {
         tx.executeSql('INSERT INTO MyTable (customer_name) VALUES ("Jimmy")');
    }
	
	var errorCB = function(err) {
		alert('Error in SQL: '+err.code);
	}
	
	var successCB = function() {
	}

	function init() {
		document.addEventListener("deviceready", createDB, true);
	}
</script>

Open in new window

ASKER CERTIFIED SOLUTION

Gurvinder Pal Singh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

synergiq

ASKER

Stupid question but how would I make db a global variable? I've seen about 3 different ways to do it and I'm not sure which to use.

Gurvinder Pal Singh

declare the var outside and initialize it inside :)

var db;
var createDB = function() {
            if(!window.openDatabase)
            {
                  alert('Database not supported!');
            }else{
                  db = window.openDatabase('mydb', '0.1', 'MyDB', 100000);
                  db.transaction(createTables, errorCB, successCB);

                  alert(db);

                  document.getElementById('my_form').addEventListener("submit", insertData);
            }
      }

synergiq

ASKER

The initial createTables function inserts fine so the table is there but the second insertSurvey function doesn't seem to be inserting a row but I'm not sure why.

I've added my full js code below.

var db;

var createDB = function() {
        if(!window.openDatabase)
        {
                alert('Database not supported!');
        }else{
                db = window.openDatabase('mydb', '0.1', 'MyDatabase', 100000);
                createTables();

                //Catch the survey form submission
                document.getElementById('survey_form').addEventListener("submit", insertSurvey);
        }
}

var createTables = function(tx) {
        db.transaction(function(tx)
        {
             tx.executeSql('CREATE TABLE IF NOT EXISTS Surveys (id INTEGER PRIMARY KEY, customer_name VARCHAR(255), boiler_make VARCHAR(254))', successCB(), errorCB(error));
        });
}

var insertSurvey = function() {
        db.transaction(function(tx)
        {
             tx.executeSql('INSERT INTO Surveys (customer_name, boiler_make) VALUES ("Jimmy", "Baxi")', successCB(), errorCB(error));
        });
}

var errorCB = function(err) {
        alert('Error in SQL: '+err.code);
}

var successCB = function() {
        alert('SQL Success');
}

function init() {
        document.addEventListener("deviceready", createDB, true);
}

Open in new window

Gurvinder Pal Singh

does it give any error? it must have alerted some error code. please share the same.

synergiq

ASKER

I think I've got it sorted now. The page was refreshing before the query had a chance to run I think.