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

LVL 2
synergiqAsked:
Who is Participating?
 
Gurvinder Pal SinghConnect With a Mentor Commented:
there are few issues i see here

1) make your db variable global so that it can be used outside this createDB  method
2) always call a query like this
db.transaction(function(tx)
{
     tx.executeSql(sqlQuery, [], function(tx, rs) {}, function(error) {});
});

sqlQuery should be the query that you have to execute, and this query can be a create, insert, update, select or a delete query.

Remember that these sql query calls are asynchronous ones.  
0
 
synergiqAuthor Commented:
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.
0
 
Gurvinder Pal SinghCommented:
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);
            }
      }
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
synergiqAuthor Commented:
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

0
 
Gurvinder Pal SinghCommented:
does it give any error? it must have alerted some error code. please share the same.
0
 
synergiqAuthor Commented:
I think I've got it sorted now. The page was refreshing before the query had a chance to run I think.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.