Trying to populate JavaScript database via variables

I am trying to populate my database using variables, and am unsuccessful. I am filling the variables with data from localStorage (successfully, as shown by console.log), but they are not being inserted into the DB.

I’ve tried a variety of + and " combinations, but none have worked. What’s the correct syntax to make this work?

 tx.executeSql("INSERT INTO DEMO (id, data1, data2, data3) VALUES (id, formdata1Get, formdata2Get, formdata3Get)");

The full function:

function populateDB(tx) {
	console.log("4. populateDB(tx) begins");
	var formdata1Get = localStorage.getItem('formdata1Set'); // get data from localStorage
	var formdata2Get = localStorage.getItem('formdata2Set');
	var formdata3Get = localStorage.getItem('formdata3Set');
	console.log("formdata1Get = " + formdata1Get); // correct!
	// Comment out to retain prior entries:
	// tx.executeSql('DROP TABLE IF EXISTS DEMO');
        tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data1 TEXT NULL, data2 TEXT NULL, data3 TEXT NULL)');
        tx.executeSql("INSERT INTO DEMO (id, data1, data2, data3) VALUES (id, formdata1Get, formdata2Get, formdata3Get)");
	queryDB(tx);
}

Perhaps the variables are not being seen as variables holding data. Error message is:
Error processing SQL: 5

That would be my guess. I’m more used to seeing rather messy looking javascript code like
‘str’ + var + ‘str’ + var + ‘str’ etc.

Problem is that there are no strings in the code in the first place, so it’s awkward to figure out. They’re all variables to begin with:

tx.executeSql("INSERT INTO DEMO (id, data1, data2, data3) VALUES (id, formdata1Get, formdata2Get, formdata3Get)");

I see only 3 variables

	var formdata1Get = localStorage.getItem('formdata1Set'); // get data from localStorage
	var formdata2Get = localStorage.getItem('formdata2Set');
	var formdata3Get = localStorage.getItem('formdata3Set');

True 'query str ’ + var + ', ’ + looks stupid, but that’s the way.

The following did not work. Is this the approach you are hinting at? This gives new possibilities. Thanks!

    var query = "'INSERT INTO DEMO (id, data1, data2, data3) VALUES (id, " + formdata1Get + ", " + formdata2Get + ", " + formdata3Get + "'";
    tx.executeSql(query);

No, this is better:

function populateDB(tx) {
	console.log("4. populateDB(tx) begins");
	var formdata1Get = localStorage.getItem('formdata1Set'); // get data from localStorage
	var formdata2Get = localStorage.getItem('formdata2Set');
	var formdata3Get = localStorage.getItem('formdata3Set');
	console.log("formdata1Get = " + formdata1Get); // correct!
        tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data1 TEXT NULL, data2 TEXT NULL, data3 TEXT NULL)');
      var query = "'tx.executeSql(INSERT INTO DEMO (id, data1, data2, data3) VALUES (id, " + formdata1Get + ", " + formdata2Get + ", " + formdata3Get + "')";
	queryDB(tx);
}

// Execute the query

function queryDB(tx) {
	console.log("5. function queryDB(tx)");
	tx.executeSql("SELECT * FROM DEMO", [], querySuccess, errorCB);
}

No errors, but now I need to find out why it doesn’t get the results into innerHTML.

Thanks!

Assuming the id is auro-incrementing, should the VALUE id be an emptry string?

Hmmm, you’re right, it is. Previous version of this code had that value entered by hand in a prepopulated way.

I made it a date:

function populateDB(tx) {
	console.log("4. populateDB(tx) begins");
	var d = new Date();
	var id = d.getTime();
	var formdata1Get = localStorage.getItem('formdata1Set'); // get data from localStorage
	var formdata2Get = localStorage.getItem('formdata2Set');
	var formdata3Get = localStorage.getItem('formdata3Set');
	console.log("id = " + id);
    tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id UNIQUE, data1 TEXT, data2 TEXT, data3 TEXT)');
	var query = "'tx.executeSql(INSERT INTO DEMO (id, data1, data2, data3) VALUES (" + id + ", " + formdata1Get + ", " + formdata2Get + ", " + formdata3Get + "')";
	queryDB(tx);
}

Doing this:

console.log("query = " + query);

Showed me that there were no ’ and ’ around each of the values. This is going to be messy!

I got it sorted out:

    tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id UNIQUE, data1 TEXT, data2 TEXT, data3 TEXT)');
	var query1 = "tx.executeSql('INSERT INTO DEMO (id, data1, data2, data3) VALUES ";
	var query2 = "(\\"" + id + "\\", \\"" + formdata1Get + "\\", \\"" + formdata2Get + "\\", \\"" + formdata3Get + "\\")');";
	var query = query1 + query2;
	console.log("query = " + query);
	queryDB(tx);
}

console.log:

query = tx.executeSql('INSERT INTO DEMO (id, data1, data2, data3) VALUES ("1395167283856", "111", "222", "333")'); 

compares favorably with example at http://docs.phonegap.com/en/3.0.0/cordova_storage_storage.md.html:

tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');

Hmmm … still not showing in innerHTML though.

The query isn’t formed as a command, so a slight modification:

	var query1 = "('INSERT INTO DEMO (id, data1, data2, data3) VALUES ";
	var query2 = "(\\"" + id + "\\", \\"" + formdata1Get + "\\", \\"" + formdata2Get + "\\", \\"" + formdata3Get + "\\")');";
	var query = query1 + query2;
	console.log("query = tx.executeSql" + query);
	tx.executeSql + query;
	queryDB(tx);
}

Still doesn’t show in innerHTML.

Since I’m now using a textarea for the output field, I don’t think I can use innerHTML. Need to use “value.” Is this the correct way to indicate the textarea:

document.getElementById("output").value

For <textarea id=“output”></textarea>

This isn’t working either. But I think I’m on the right track.

Actually AFAIK innerHTML will work with a textarea, I have a feeling the prolem lies elsewhere.

You can test by temporarily commenting out the line and doing something like
… innerHTML = “test string”;

Yeah, document.getElementById(“output”).innerHTML += “test string”; works fine.

I’m guessing the problem is a naming conflict with “id”. i.e. the database thinks you want to insert the value of the id field into the id field.
And you’re right. it sure can get messy looking.
Does this work?

function populateDB(tx) {
	console.log("4. populateDB(tx) begins");
	var d = new Date();
	var new_id = d.getTime();
	var formdata1Get = localStorage.getItem('formdata1Set'); // get data from localStorage
	var formdata2Get = localStorage.getItem('formdata2Set');
	var formdata3Get = localStorage.getItem('formdata3Set');
	console.log("id = " + id);
    tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id UNIQUE, data1 TEXT, data2 TEXT, data3 TEXT)');
	var query = "'tx.executeSql(INSERT INTO DEMO (id, data1, data2, data3) VALUES (\\"" + new_id + "\\", \\"" + formdata1Get + "\\", \\"" + formdata2Get + "\\", \\"" + formdata3Get + "\\"')";
	queryDB(tx);
}

No, that doesn’t do it. Pretty clever, though. Here’s the current code:

function populateDB(tx) {
	console.log("4. populateDB(tx) begins");
	var d = new Date();
	var new_id = d.getTime(); // set the current time as the id
	console.log("id = " + new_id);
	var formdata1Get = localStorage.getItem('formdata1Set'); // get data from localStorage
	var formdata2Get = localStorage.getItem('formdata2Set');
	var formdata3Get = localStorage.getItem('formdata3Set');
    tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id UNIQUE, data1 TEXT NULL, data2 TEXT NULL, data3 TEXT NULL)');
	var query1 = "('INSERT INTO DEMO (id, data1, data2, data3) VALUES ";
	var query2 = "(\\"" + new_id + "\\", \\"" + formdata1Get + "\\", \\"" + formdata2Get + "\\", \\"" + formdata3Get + "\\")');";
	var query = query1 + query2;
	console.log("query = [tx.executeSql]" + query);
	tx.executeSql + query;
	queryDB(tx);
}

Error processing sql: 0

Bugs can be stubborn … buggers.
Maybe the id field needs a datatype?


    tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id UNIQUE, data1 TEXT NULL, data2 TEXT NULL, data3 TEXT NULL)');

id TEXT NOT NULL does not work; no change. As was, it went “by the book” as id UNIQUE.

Tested in Chrome, Nexus 7 Android device, and ADT + Eclipse emulator.

With the addition of console.log("Last inserted row ID = " + results.insertId); I get the following error in Chrome:

Uncaught InvalidAccessError: Failed to read the ‘insertId’ property from ‘SQLResultSet’: The query didn’t result in any rows being added.

You can see the line in the code below:

function querySuccess(tx, results) {
	console.log("6. querySuccess(tx, results) begins");
	var len = results.rows.length;
	console.log("Table contents: " + len + " rows found."); // get the number of results
	for (var i = 0; i < len; i++) { // loop as many times as there are row results
	document.getElementById("output").innerHTML += "\
" + i + ". ID = " + results.rows.item(i).id +
			"\
 data1 = " + results.rows.item(i).data1 +
			"\
 data2 = " + results.rows.item(i).data2 +
			"\
 data3 = " + results.rows.item(i).data3 + "\
";
	} document.getElementById("output").innerHTML += "\
done"; // this line works; above doesn't
	[B]console.log("Last inserted row ID = " + results.insertId);[/B] // last inserted ID. Error: no rows were added.
}

I put actual content in the query, and they did not show either:

var query1 = “(‘INSERT INTO DEMO (id, data1, data2, data3) VALUES ";
var query2 = "(\“1234\”, \“abc\”, \“def\”, \“ghi\”)’);”;
// var query2 = “(\”" + new_id + “\”, \"" + formdata1Get + “\”, \"" + formdata2Get + “\”, \"" + formdata3Get + “\”)’);";

In Chrome, it was parsed correctly:
query = [tx.executeSql](‘INSERT INTO DEMO (id, data1, data2, data3) VALUES (“1234”, “abc”, “def”, “ghi”)’);

I found the problem. This doesn’t work:

tx.executeSql + query;

This does (it executes correctly and puts the information in the textarea):

tx.executeSql(‘INSERT INTO DEMO (id, data1, data2, data3) VALUES (“1234”, “abc”, “def”, “ghi”)’);

NOT WORKING:
tx.executeSql + “(’” + query1 + query2 + “’)”;