SitePoint Sponsor

User Tag List

Results 1 to 24 of 24
  1. #1
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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?

    HTML Code:
     tx.executeSql("INSERT INTO DEMO (id, data1, data2, data3) VALUES (id, formdata1Get, formdata2Get, formdata3Get)");
    The full function:

    Code JavaScript:
    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
    Last edited by Mittineague; Mar 17, 2014 at 16:50. Reason: reformatting bbcode tags
    Steve Husting

  2. #2
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,228
    Mentioned
    194 Post(s)
    Tagged
    2 Thread(s)
    That would be my guess. I'm more used to seeing rather messy looking javascript code like
    'str' + var + 'str' + var + 'str' etc.

  3. #3
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    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:

    HTML Code:
    tx.executeSql("INSERT INTO DEMO (id, data1, data2, data3) VALUES (id, formdata1Get, formdata2Get, formdata3Get)");
    Steve Husting

  4. #4
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,228
    Mentioned
    194 Post(s)
    Tagged
    2 Thread(s)
    I see only 3 variables
    Code JavaScript:
    	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.

  5. #5
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    I see only 3 variables
    Code JavaScript:
    	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!

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

  6. #6
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Solved!

    No, this is better:

    HTML Code:
    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!
    Steve Husting

  7. #7
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,228
    Mentioned
    194 Post(s)
    Tagged
    2 Thread(s)
    Assuming the id is auro-incrementing, should the VALUE id be an emptry string?

  8. #8
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mittineague View Post
    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:

    HTML Code:
    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);
    }
    Steve Husting

  9. #9
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Doing this:

    Code:
    console.log("query = " + query);
    Showed me that there were no ' and ' around each of the values. This is going to be messy!
    Steve Husting

  10. #10
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I got it sorted out:

    HTML Code:
        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:
    HTML Code:
    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/co...orage.md.html:
    HTML Code:
    tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');
    Hmmm ... still not showing in innerHTML though.
    Steve Husting

  11. #11
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The query isn't formed as a command, so a slight modification:

    Code:
    	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.
    Steve Husting

  12. #12
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    document.getElementById("output").value
    For <textarea id="output"></textarea>

    This isn't working either. But I think I'm on the right track.
    Steve Husting

  13. #13
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,228
    Mentioned
    194 Post(s)
    Tagged
    2 Thread(s)
    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";

  14. #14
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yeah, document.getElementById("output").innerHTML += "test string"; works fine.
    Steve Husting

  15. #15
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,228
    Mentioned
    194 Post(s)
    Tagged
    2 Thread(s)
    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?
    Code JavaScript:
    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);
    }

  16. #16
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    No, that doesn't do it. Pretty clever, though. Here's the current code:

    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
    Steve Husting

  17. #17
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,228
    Mentioned
    194 Post(s)
    Tagged
    2 Thread(s)
    Bugs can be stubborn ..... buggers.
    Maybe the id field needs a datatype?
    PHP Code:
        tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id UNIQUE, data1 TEXT NULL, data2 TEXT NULL, data3 TEXT NULL)'); 

  18. #18
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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 += "\n" + i + ". ID = " + results.rows.item(i).id +
    			"\n data1 = " + results.rows.item(i).data1 +
    			"\n data2 = " + results.rows.item(i).data2 +
    			"\n data3 = " + results.rows.item(i).data3 + "\n";
    	} document.getElementById("output").innerHTML += "\ndone"; // this line works; above doesn't
    	console.log("Last inserted row ID = " + results.insertId); // last inserted ID. Error: no rows were added.
    }
    Steve Husting

  19. #19
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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")');
    Steve Husting

  20. #20
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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 + "')";
    Steve Husting

  21. #21
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This puts the new_id in, but leaves the other vars all blank:

    tx.executeSql('INSERT INTO DEMO (id, data1, data2, data3) VALUES (\"' + new_id + '\"' + ', \"' + formdata1Get + '\", \"' + formdata1Get + '\", \"' + formdata1Get + '\")');

    innerHTML display:
    ID = 1395181245363
    data1 =
    data2 =
    data3 =
    Steve Husting

  22. #22
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Solved!

    THIS WORKS!

    HTML 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 TEXT NOT NULL, 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; 
    	tx.executeSql('INSERT INTO DEMO (id, data1, data2, data3) VALUES (\"' + new_id + '\"' + ', \"' + formdata1Get + '\", \"' + formdata2Get + '\", \"' + formdata3Get + '\")');
    	queryDB(tx);
    }
    Steve Husting

  23. #23
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately, the data is not retained in a device when the app is exited. In a device, it behaves just like sessionStorage.

    Also, as it stands, the data is repeated with each submission with the "innerHTML+=" scripting -- it repeats all the earlier submissions as well as the latest one.

    Back to the drawing board!
    Steve Husting

  24. #24
    SitePoint Wizard
    Join Date
    Feb 2007
    Location
    Southern California
    Posts
    1,384
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StevenHu View Post
    Unfortunately, the data is not retained in a device when the app is exited. In a device, it behaves just like sessionStorage.

    Also, as it stands, the data is repeated with each submission with the "innerHTML+=" scripting -- it repeats all the earlier submissions as well as the latest one.

    Back to the drawing board!
    I have an idea on how to fix this!
    Steve Husting


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •