Reading info from sqlite database, Syntax? How do I use it in html5 webapp?

Question

I have a webapp that I'm building, and have just started with SQLite. I have been able to create my form, open the database I created, create the table, and fields i need, and enter data into the fields.

Now I'm trying to read the data back out with a SELECT statement, to show it on screen and as a list of the columns. I just don't know the syntax for the SELECT statemnt in javascript or HTML5 beyond

'SELECT * FROM MyTable'...I know it can be done, just need some help with the syntax of getting the results onto the screen.

I start with this.

   

var db = window.openDatabase('TabOrder', '', 'Bar Tab Orders', 2500000);

 function insertDrinks(category, drinkname, our_cost, cust_cost){
  db.transaction(function(tx){
   tx.executeSql('INSERT INTO Drinks (category, drinkname, our_cost, cust_cost) VALUES (?, ?, ?, ?)', [category, drinkname, out_cost, cust_cost]);
  });
 }

 $(document).ready(function() {
  db.transaction(function(tx) {
   tx.executeSql('CREATE TABLE IF NOT EXISTS Drinks(id INTEGER PRIMARY KEY Autonumber, category TEXT, drinkname TEXT, our_cost FLOAT(6,2), cust_cost FLOAT(7,2))', []);
  });
 });
 

I later have this....

 View Cat / Drink List
 
 function readDrinks(id, category, drinkname, our_cost, cust_cost){
  db.transaction(function(tx) {
   tx.executeSql('SELECT * FROM Drinks', [id, category, drinkname, our_cost, cust_cost]);
  });
 document.write(id, category + " are the categories.");
 }
 

I just tried to piece it together, and have no idea what i'm doing with it beyond the basic SQL.

Any help is greatly appreciated...and this is for a client side DB, not one connecting to the web.

thanks....

Answer 1

See the spec and this Apple tutorial. In short, you need to add data and error callbacks. Also, you should be passing an empty array (or null_ because your query has no parameters.

db.transaction(function(tx) {
   tx.executeSql('SELECT * FROM Drinks', 
                 [],
                 function(tx, results)
                 {
                   // results is a http://dev.w3.org/html5/webdatabase/#sqlresultset .  
                   // It has insertId, rowsAffected, and rows, which is
                   // essentially (not exactly) an array of arrays. 
                 },
                 function(tx, error)
                 {

                 }
   );
});

It's up to you whether to use named or anonymous functions.

EDIT: I made a working demo at http://jsfiddle.net/WcV6Y/7/ . It's tested in Chrome 5.0.375.70.