3

I'm experimenting with the built in SQL support in the Safari Browser and I want to select a random query via Javascript. 

SELECT * FROM questions ORDER BY random()

Returns not authorized to use function: random

See this screenshot.

Any suggestions?

user422644
  • 31
  • 2

2 Answers2

3

Query using a non-random order, then shuffle the results:

tx.executeSql('SELECT * FROM questions',[], function(tx, resultSet) {
    var resultArray = [];

    for(var i=0; i < resultSet.rows.length; i+=1) {
        resultArray.push(resultSet.rows.item(i));
    }

    var shuffledArray = shuffle(resultArray);

    // do something with the shuffled array...
});

Where shuffle() could be something like this: https://stackoverflow.com/a/962890/490560

Community
  • 1
  • 1
Ignitor
  • 2,907
  • 33
  • 50
  • Alternatively, include underscorejs and use `_.shuffle()` or even `_.sample()`; the later gives you a random result of given length. – pdu Oct 05 '14 at 14:58
0

Perhaps something like the following would work:

SELECT *
  FROM (SELECT RANDOM() as RANDOM_NUM, Q.*
          FROM QUESTIONS Q)
  ORDER BY RANDOM_NUM

Share and enjoy.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
  • Thanks for your reply but weirdly I get the same error again. not authorized to use function: random Is this a security issue? – user422644 Aug 17 '10 at 10:53
  • @user422644 - sure sounds that way. Interestingly, this page (http://www.sqlite.org/omitted.html) at the SQLite site says that GRANT and REVOKE aren't supported so I'm not sure how one would work around this. Wish I could offer more ideas. – Bob Jarvis - Слава Україні Aug 17 '10 at 11:12
  • Another way would be to shuffle the results after I got them. db.transaction( function(transaction) { transaction.executeSql( 'SELECT * FROM questions', [], function (transaction, result) { // best way to shuffle results and loop? }, errorHandler ); }, transactionErrorHandler ); – user422644 Aug 17 '10 at 11:21