Adding to Storage DB from another page

[WebSteve]

How do I add to a Storage DB from an html page? 

If I had a page called storage2.js with this in it:

// Wait for Cordova to load

document.addEventListener("deviceready", onDeviceReady, false);

// Populate the database

function populateDB(tx) {

tx.executeSql('DROP TABLE IF EXISTS BIRDS');

tx.executeSql('CREATE TABLE IF NOT EXISTS BIRDS (id unique, bodyType TEXT NOT NULL, category TEXT NOT NULL, name TEXT NULL, photo TEXT NULL, resource TEXT NULL, caption TEXT NULL)');

tx.executeSql('INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (1, "Short Course", "SC8.2e", "SC8.2e Ready-To-Run", "http://www.teamassociated.com/pictures/cars_and_trucks/SC8.2e/SC8.2e_RTR_2560x2048_sm.jpg", "http://www.teamassociated.com/cars_and_trucks/SC8.2e/RTR/","Modeled after the short-course race trucks that compete in the Lucas Oil Off Road Racing Series, the SC8.2e RTR takes the next evolutionary step in Team Associated\'s 1:8 scale short-course line by adding the new performance suspension developed on our R.O.A.R. National Championship-winning RC8.2 buggy.")');

tx.executeSql('INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (2, "Short Course", "SC10GT", "SC10GT Ready-To-Run", "http://www.teamassociated.com/pictures/cars_and_trucks/SC10GT/SC10GT_RTR_2560x2048_sm.jpg", "http://www.teamassociated.com/cars_and_trucks/SC10GT/RTR/","For many people in the RC world, nothing beats a 2-stroke nitro-breathing engine. From the sound, to the smoke, to the brutal power, nitro delivers an experience in a RC truck like nothing else can. Now you can experience that awesome nitro power in the short-course class with the SC10GT!")');

tx.executeSql('INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (3, "Short Course", "SC10 4×4", "SC10 4x4 RTR Combo", "http://www.teamassociated.com/pictures/cars_and_trucks/SC10_4x4/SC10_4x4_Kit_2560x2048_sm.jpg", "http://www.teamassociated.com/cars_and_trucks/SC10_4x4/RTR_Combo/","The SC10 4x4 Ready-To-Runs are RC replicas of the 800+ horsepower short course trucks driven in the Lucas Oil Off Road Racing Series.")');

tx.executeSql('INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (4, "Short Course", "SC10", "SC10 RS RTR Combo", "http://www.teamassociated.com/pictures/cars_and_trucks/SC10/SC10_RTR_Procomp_2560x2048_sm.jpg", "http://www.teamassociated.com/cars_and_trucks/SC10/RS_Combo/","The SC10RS (Race-Spec) RTR Combo is a ready-to-run replica of the trucks driven in the Lucas Oil Off Road Racing Series.")');

tx.executeSql('INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (5, "Buggy", "RC8.2e", "RC8.2e Factory Team", "http://www.teamassociated.com/pictures/cars_and_trucks/RC8.2e/RC8.2e_2560x2048_sm.jpg", "http://www.teamassociated.com/cars_and_trucks/RC8.2e/Factory_Team/","Team Associated has taken all of the refinements from the RC8.2 and have applied them to our electric-power platform. The RC8.2e has already been proven as a winner after TQ\’ing and winning the 2011 Sidewinder Nitro Explosion in the capable hands of Ryan Cavalieri.")');

}

// Query the database.

function queryDB(tx) {

tx.executeSql("SELECT * FROM BIRDS", [], querySuccess, errorCB);

}

// Query the success callback

function querySuccess(tx, results) {

var len = results.rows.length;

console.log("BIRDS table: " + len + " rows found.");

for (var i=0; i<len; i++){

document.getElementById("output").innerHTML +=

"<div class='even'><p class='title'>" + results.rows.item(i).name + "</p>" +

"<img src='' width='100px' height='100px'" + results.rows.item(i).photo + ">" +

"<p class='caption'>" + results.rows.item(i).caption + "</p>" +

"<p class='more'><a href=''" + results.rows.item(i).resource + ">More</a></p>";

}

}

// Transaction error callback

function errorCB(err) {

console.log("Error processing SQL: "+err.code);

}

// Transaction success callback

function successCB() {

var db = window.openDatabase("Database", "1.0", "Birds", 2000000);

db.transaction(queryDB, errorCB);

}

// Cordova is ready

function onDeviceReady() {

var db = window.openDatabase("Database", "1.0", "Birds", 200000);

db.transaction(populateDB, errorCB, successCB);

}

... and create this html page to add another row to it, it doesn't add any data:

<!DOCTYPE html>

<html>

<head>

<title>Prepopulated DB (PG Storage Example)</title>

<script type="text/javascript" charset="utf-8" src="cordova-2.0.0.js"></script>

<script type="text/javascript" charset="utf-8" src="storage2.js"></script>

<script type="text/javascript" charset="utf-8">

// Wait for Cordova to load

document.addEventListener("deviceready", onDeviceReady, false);

function onDeviceReady() {

var db = window.openDatabase("Database", "1.0", "Birds", 200000);

db.transaction(populateDB, errorCB, successCB);

function populateDB(tx) {

tx.executeSql('CREATE TABLE IF NOT EXISTS BIRDS (id unique, bodyType TEXT NOT NULL, category TEXT NOT NULL, name TEXT NULL, photo TEXT NULL, resource TEXT NULL, caption TEXT NULL)');

tx.executeSql('INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (1, "Short Course", "new", "new", "http://www.teamassociated.com/pictures/cars_and_trucks/SC8.2e/SC8.2e_RTR_2560x2048_sm.jpg", "http://www.teamassociated.com/cars_and_trucks/SC8.2e/RTR/","NEW.")');

}

</script>

</head>

<body onload="onDeviceReady()">

<button value="bookmark" onclick="populateDB(tx)">Add Bookmark</button>

</body>

</html>

Error messages includes 

Uncaught ReferenceError: tx is not defined

Uncaught SyntaxError: Unexpected end of input (for the <!DOCTYPE html> line)

I want to adapt the Storage DB to create bookmarks from any page (each row showing unique ID, page title, and page filename). Why won't the above code work?

Thanks!

[jan-willem gmelig meyling]

You can't call your transaction function like Function(tx), cause in that case, tx is indeed not defined.

You need to remove the tx parameter out of your transaction function. Then make your db variable global by moving it out of the function ( leaving db = window.... As is, but adding var db = null; at the top of your code.

Then call your tx function like this:

function SQL(query) {
db.transaction(function(tx) {
tx.executeSQL(query);
}, function(err) {
throw err;
}, function() {
console.log("Succesfully executed " + query);
});
}

[jan-willem gmelig meyling]

By the way the last function will be the callback so you probably want the results to, so add an function parameter for the results aswell ;) you can also pass the callback to your tx function like this:

function SQL(query, callback) {
// defining a default callback when argument is not defined
callback = callback || function(results) {
console.log("We have results!", results);

}

db.transaction(function(tx) {
 tx.executeSQL(query);
}, function(err) {
 throw err;

}, function(data) {

 console.log("Succesfully executed " + query);

callback(data);
});
}

[WebSteve]

Thank you for your help, but I'm not sure how to fit these replies into my code. Do I add your code to mine, or...? Javascript is not my strong suit, I'm afraid. I'm trying to understand it from a PHP/MySQL angle and feel lost already. 

[jan-willem gmelig meyling]

Hi Steve,

First of all, remove al duplicate code. There is no need to have the same functions both in your HTML and the JS include and results in this kind of issues. I suggest you just remove all the javascript code in your HTML file.

What your trying to do is calling the undefined tx parameter. You should open your database in a global variable, and calling the db.executeSQL for every transaction instead.

Your button is calling  populateDB(tx). The first issue is that you have defined populateDB twice , since the HTML also has acces to your populateDB function in your Javascript include. The second problem is that by calling function(tx) manually (instead of it being triggered by the native db.transaction method) results in an undefined tx argument which doesn't have a executeSql method aswell. I believe this thing is called closures, you should look it up for more information, but I will give you some short expl.:

Basicly we must think that the db.transaction method, is just a function, calling another function with some tx argument passed.

db.transaction = function(transaction, callback, errorcallback) {

 var tx = "probably some object here";

 transaction(tx);

}

I like to use localStorage like this:

====================================================

// Don't forget this one;

var db;

// Open the database on deviceready 

// Please make sure that this function is indeed called, otherwise I would test without Cordova since the webSQL also works natively in , for example, Chrome;

document.addEventListener("deviceready", function() {

// I've shortened this beast for sake of readability, but it does the same as your code

  db = window.openDatabase("Database", "1.0", "Birds", 200000);

  PopulateDB() ;

}, false);

// This is a function to do simple database transactions without results or callbacks

function insert(query) {

 db.transaction(function(tx) {

  tx,executeSql(query);

})

}

// This will be your function to populate your DB

function PopulateDB() {

insert('DROP TABLE IF EXISTS BIRDS');

insert('CREATE TABLE IF NOT EXISTS BIRDS (id unique, bodyType TEXT NOT NULL, category TEXT NOT NULL, name TEXT NULL, photo TEXT NULL, resource TEXT NULL, caption TEXT NULL)');

insert('INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (1, "Short Course", "SC8.2e", "SC8.2e Ready-To-Run", "http://www.teamassociated.com/pictures/cars_and_trucks/SC8.2e/SC8.2e_RTR_2560x2048_sm.jpg", "http://www.teamassociated.com/cars_and_trucks/SC8.2e/RTR/","Modeled after the short-course race trucks that compete in the Lucas Oil Off Road Racing Series, the SC8.2e RTR takes the next evolutionary step in Team Associated\'s 1:8 scale short-course line by adding the new performance suspension developed on our R.O.A.R. National Championship-winning RC8.2 buggy.")');

insert('INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (2, "Short Course", "SC10GT", "SC10GT Ready-To-Run", "http://www.teamassociated.com/pictures/cars_and_trucks/SC10GT/SC10GT_RTR_2560x2048_sm.jpg", "http://www.teamassociated.com/cars_and_trucks/SC10GT/RTR/","For many people in the RC world, nothing beats a 2-stroke nitro-breathing engine. From the sound, to the smoke, to the brutal power, nitro delivers an experience in a RC truck like nothing else can. Now you can experience that awesome nitro power in the short-course class with the SC10GT!")');

insert('INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (3, "Short Course", "SC10 4×4", "SC10 4x4 RTR Combo", "http://www.teamassociated.com/pictures/cars_and_trucks/SC10_4x4/SC10_4x4_Kit_2560x2048_sm.jpg", "http://www.teamassociated.com/cars_and_trucks/SC10_4x4/RTR_Combo/","The SC10 4x4 Ready-To-Runs are RC replicas of the 800+ horsepower short course trucks driven in the Lucas Oil Off Road Racing Series.")');

insert('INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (4, "Short Course", "SC10", "SC10 RS RTR Combo", "http://www.teamassociated.com/pictures/cars_and_trucks/SC10/SC10_RTR_Procomp_2560x2048_sm.jpg", "http://www.teamassociated.com/cars_and_trucks/SC10/RS_Combo/","The SC10RS (Race-Spec) RTR Combo is a ready-to-run replica of the trucks driven in the Lucas Oil Off Road Racing Series.")');

insert('INSERT INTO BIRDS (id, bodyType, category, name, photo, resource, caption) VALUES (5, "Buggy", "RC8.2e", "RC8.2e Factory Team", "http://www.teamassociated.com/pictures/cars_and_trucks/RC8.2e/RC8.2e_2560x2048_sm.jpg", "http://www.teamassociated.com/cars_and_trucks/RC8.2e/Factory_Team/","Team Associated has taken all of the refinements from the RC8.2 and have applied them to our electric-power platform. The RC8.2e has already been proven as a winner after TQ\’ing and winning the 2011 Sidewinder Nitro Explosion in the capable hands of Ryan Cavalieri.")');

}

// function to get results and append the data to your html

function getResults() {

  tx.executeSql("SELECT * FROM BIRDS", [], function(tx, results) {

var len = results.rows.length;

console.log("BIRDS table: " + len + " rows found.");

for (var i=0; i<len; i++){

           // (i) needs to be [i], just a javascript thing 

document.getElementById("output").innerHTML +=

"<div class='even'><p class='title'>" + results.rows.item[i].name + "</p>" +

"<img src='' width='100px' height='100px'" + results.rows.item[i].photo + ">" +

"<p class='caption'>" + results.rows.item[i].caption + "</p>" +

"<p class='more'><a href=''" + results.rows.item[i].resource + ">More</a></p>";

           }; 

}, function(error) {

 throw error;

});

Now you can call the getResults() function from your button or an event and the data from your database will be inserted to your html

[WebSteve]

I think we are missing something. I wanted a different page, any html page, to write a row to the database. You seem to think that I want to click a button from anywhere and write the contents of the DB to that page. 

My plan is that I'll have a button on each html page that will write a unique row to the DB consisting of its filename, page title and unique ID. This will be a bookmark list when a bookmark page comes up and prints out the list. The HTML will use the filename to make a link of the title, and a Delete button will use the ID to delete the row if they don't want the Bookmark any more. 

I don't see in your code where I make a button onClick write to the DB. The original code worked fine as is for automatically populating the innerHTML from the DB. Were you just showing me a different way of doing it? 

The reason for the SQL code on the HTML page ( which was wrongly coded) was to write that (future) bookmark row to the DB. If I could see the syntax for writing that one page, then I'll understand how to use the code on all the other pages. 

I hope this makes sense! 

And thank you for your kind and helpful responses! 

[Kerri Shotts]

TOTally off-topic, but any particular reason you're going the with what seems to be a multi-page architecture route? Or are the html pages you are using getting loaded in via XHR?

Somewhat related: I would suggest making a db.js file, say, in a scripts directory (like www/scripts/db.js). That way you can avoid having to write duplicate code when working with the database. Then you can just include that script on each html page… just thinking of ways to help simplify things once you've got it figured out.

_________________________________________

~Kerri Shotts, photoKandy Studios LLC

   Wanna be our neighbor? Our Facebook page & Twitter feed.

--
-- You received this message because you are subscribed to the Google
Groups "phonegap" group.
To post to this group, send email to phon...@googlegroups.com
To unsubscribe from this group, send email to
phonegap+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/phonegap?hl=en?hl=en
 
For more info on PhoneGap or to download the code go to www.phonegap.com
 
To compile in the cloud, check out build.phonegap.com
 
 

[WebSteve]

The only way I know of for a page to set a bookmark is for the reader on that page to tap a button to indicate setting a bookmark. I thought an onClick event would then set the bookmark as a row in a database, including the filename, page title, and unique ID. I thought this was an elegant way of doing it. If I were to do it with localstorage, then the code would get horrendously complex, since I'd need to repeat for each page. With the former way, the bookmark is added to the DB only when the user actually writes it in with the onClick event.

I thought my question was pretty simple. So is it incredibly complicated to create this event on a page to write it into the storage as outlined in the PG Storage API?

Thanks!

[WebSteve]

This is NOT a single-page architecture. And yes, I know to remove the Drop Table command  :) The separate HTML pages are already there, and I wanted to add this feature to the app. It's highly unlikely they'll be adding new bookmarks several times per app usage.

Thanks,
Steve H


On Tuesday, September 18, 2012 10:26:18 AM UTC-7, Erik Yuzwa wrote:

Hi @WebSteve,

No, it is not a very complex problem, and @Kerri's point about SPA design is a fairly important one..

http://en.wikipedia.org/wiki/Single-page_application

When you "physically" travel to different .html pages, you're forcing a teardown / buildup of the javascript in browser memory as it's wiped between refreshes / page changes.

The code posted above (aka the "birds" code) will help you out..it has everything there with a few tweaks:

1. Remove the DROP TABLE statement (otherwise each page load will drop the existing database *grin*)
2. On a button click event you can call a javascript method to use INSERT your required data into the database.

You should be on your way with that.

(Given your PHP background, you could also just use a jQuery.ajax method to post the data to a listening PHP webservice which dumps the data into the database, but I realize you've already decided not to go down this road..)

hth,

Erik

[Kerri Shotts]

Well, you /could/ use localStorage if you abuse the "keys" a bit... Think of it something like this:

function createBookmarkForCurrentPage()

{

    localStorage["bookmark_" + document.title] = window.location.href;

}

To remove a bookmark, just remove the item from localStorage. To check for the bookmark, you can query localStorage and see if you get anything other than an empty string or null from it.

Then, since I assume you will need to show a list of these, you can loop through all the items in localStorage and pick out the bookmarks, like so:

for (k in localStorage)

{

    if (k.indexOf("bookmark")>-1)

    {

        // k is a bookmark :-) the href of the page is the value: localStorage[k],

        // while the title is k.substr(9,...)

    }

}

Granted, it feels like a bastardization of localStorage, but really -- a bookmarking solution for a fixed set of pages could be viewed just as easily as a key-value problem, not a db problem. 

Furthermore, put the above into some functions, put it in a script file, and then load the script in every page. That way should you need to change the script, your pages get the changes immediately. The only other thing you'd need to add to each page would be the onclick event to trigger toggling the bookmark.

A db works, yes -- and the concept would be much the same as the above, but I just wanted to point out that localStorage wouldn't have to be horribly complicated. 

______________________________
Kerri Shotts
photoKandy Studios LLC

📱 Phone: +1 (312) 380-1035
🌐 Web: http://photokandy.com 

Twitter: @photokandy

[WebSteve]

Thank you again, Kerri!!

Steve