CODEWITHSUNDEEP
javascriptsqlitefirefox-addonstorage
helt
helt

Reputation: 5207

How to get the value of SELECT COUNT(*)?

I've literally been trying all day to make Firefox to obey my will...

I want : 

int c = SELECT COUNT(*) FROM ...

I've tried executeAsync({...});, but I believe it's the wrong paradigm, as I want the result immediately. (And mozIStoragePendingStatement results in errors)

var count = 0;
var conn = Services.storage.openDatabase(dbfile); // Will also create the file if it does not exist
let statement = conn.createStatement("SELECT COUNT(*) FROM edges LIMIT 42;");
console.log("columns: " + statement.columnCount);  // prints "1";
console.log("col name:" + statement.getColumnName(0)); // is "COUNT(*)"

while (statement.executeStep())
    count = statement.row.getResultByIndex(0); // "illegal value"
    count = statement.row.getString(0); // "illegal value", too
    count = statement.row.COUNT(*); // hahaha. still not working
    count = statement.row[0]; // hahaha. "undefinded"
    count = statement.row[1]; // hahaha. "undefinded"
}
statement.reset();

It basically works but I dont get the value. What's wrong with all the statements (those within the loop).

Thanks for any hints...

Upvotes: 0

Views: 514

Answers (2)

Lj Pearson
Lj Pearson

Reputation: 115

Try aliasing count(*) as total, then fetch that

Upvotes: 1

Wladimir Palant
Wladimir Palant

Reputation: 57681

I've tried executeAsync({...});, but I believe it's the wrong paradigm, as I want the result immediately.

You shouldn't want that, the Storage API is asynchronous for a reason. Synchronous access to databases can cause a random delay (e.g. if the hard drive is busy). And since your code executes on the main thread (the same thread that services the user interface) the entire user interface would hang while your code is waiting for the database to respond. The Mozilla devs tried synchronous database access in Firefox 3 and quickly noticed that it degrades user experience - hence the asynchronous API, the database processing happens on a background thread without blocking anything.

You should change your code to work asynchronously. Something like this should do for example:

Components.utils.import("resource://gre/modules/Services.jsm");

var conn = Services.storage.openDatabase(dbfile);
if (conn.schemaVersion < 1)
{
  conn.createTable("edges", "s INTEGER, t INTEGER");
  conn.schemaVersion = 1;
}

var statement = conn.createStatement("SELECT COUNT(*) FROM edges");
statement.executeAsync({
  handleResult: function(resultSet)
  {
    var row = resultSet.getNextRow();
    var count = row.getResultByIndex(0);
    processResult(count);
  },
  handleError: function(error) {},
  handleCompletion: function(reason) {}
});

// Close connection once the pending operations are completed
conn.asyncClose();

See also: mozIStorageResultSet, mozIStorageRow.

Upvotes: 1

Related Questions