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....

Matthew Flaschen · Accepted Answer · 2011-10-25T16:55:13.200

3

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.

edited Oct 25 '11 at 16:55

answered Jun 25 '10 at 23:31

Matthew Flaschen

278,309
50
514
539

I've made an edit following the Apple Example, but still get no output to the screen...


function readDrinks(id, category, drinkname, our_cost, cust_cost){ db.transaction(function(tx) { tx.executeSql('SELECT * FROM Drinks', [], function readDatahandler(tx, results) { //Handle results of Category List Query for (i=0; i

It's for the iphone if that makes any difference... – Mac-Gon Jun 26 '10 at 02:39

@bmcgonag, there were a couple of issues, including your create table syntax, specifically the AUTONUMBER. Note you can test SQLite syntax with the [command line client](http://www.sqlite.org/sqlite.html) or a tool like [SQLite Manager](http://sqliteman.com/). I made a working demo at http://jsfiddle.net/WcV6Y/7/ – Matthew Flaschen Jun 26 '10 at 03:22

score 2 · Answer 2 · answered Mar 31 '11 at 18:42

try something like this

tx.executeSql('SELECT * FROM foo', [], function (tx, results) {
             var len = results.rows.length;
             for (var i = 0; i < len; ++i) {
                var obj = results.rows.item(i);
                alert(obj);
              }
          });

also see this for short tutorial http://html5doctor.com/introducing-web-sql-databases/

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

2 Answers2

Linked