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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gurvinder Pal SinghCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JavaScript

From novice to tech pro — start learning today.