Selecting a Random Row from a Web SQL Table

There seems to be a bug in Webkit that generates an exception when trying to use the RANDOM() method to retrieve a random record from a database table.

Here’s a work-around:

db.runQuery("select count(*) as thecount from friend", 
            function(records) {
             var randomNum = Math.floor(Math.random() * records[0].thecount) + 1;
             db.runQuery(new String().concat(" select *",
                                             " from friend",
                                             " where id not in ( ",
                                             "    select id ",
                                             "    from friend ",
                                             "    limit " + (randomNum - 1),
                                             " )",
                                             " limit 1"), 
                         function(record) {
                            // random record returned here!
                            console.log(record);
                         }
              );
            }
          );

Here’s the runQuery function that I wrote in order to abstract some of the websql technobabble:

this.runQuery = function(sql, callback) {
        this.db.transaction(function (tx) {
            tx.executeSql(sql, 
                          [],
                          function(tx,rs) {
                            var a = [];
                            for (var i=0; i < rs.rows.length; i++) {
                                a[a.length] = rs.rows.item(i);
                            }
                            callback(a);
                          },
                          function() {             
                            console.log('fail',arguments);
                          }
            )
        });
 }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s