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-25 16:55:13Z

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, 2011 at 16:55

answered Jun 25, 2010 at 23:31

Matthew Flaschen

286k53 gold badges523 silver badges554 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mac-Gon Over a year ago

I've made an edit following the Apple Example, but still get no output to the screen... <pre> <code> 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<results.rows.length; i++) { var row=results.rows.item(i); var stringout = "Category: " + row['category'] + "\n"; } alert(stringout); } ); }); } </code> </pre> It's for the iphone if that makes any difference...

Matthew Flaschen Over a year ago

@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 or a tool like SQLite Manager. I made a working demo at jsfiddle.net/WcV6Y/7

Kai CriticallyAcclaimed Cooper · Accepted Answer · 2011-03-31 18:42:24Z

2

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/

answered Mar 31, 2011 at 18:42

Kai CriticallyAcclaimed Cooper

9782 gold badges15 silver badges27 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related