SQL Console in Mozilla
This weblog has referenced the Mozilla SQL extension already multiple times. In this entry, you will find some actual code which uses that extension to provide a simple Mozilla-based client for a local or remote database. I will explain the source code from bottom to top, so be prepared -- we will dive right in.
Let's start with the connection initialization. Your script needs to run under chrome or have XPConnect privileges. NOTE: You need to have the Mozilla SQL extension build and installed. Otherwise this will not work. The examples work with XULRunner, Firefox, Mozilla/SeaMonkey once the extension is installed correctly.// create a new SQLite connection
// "?type=sqlite" defines the connection type
var conn = Components
.classes["@mozilla.org/sql/connection;1?type=sqlite"]
.getService(Components.interfaces.mozISqlConnection);
// the SQLite extension operates only on existing
// writable files. this you might need to do a
// "touch /tmp/foobar.sqlite" before this works.
// syntax: host, port, database name, username, password
conn.init("", 0, "/tmp/foobar.sqlite", "", "");
// test table might not exist, so ignore exception.
// (newer SQLite versions support IF EXISTS)
try {
conn.executeQuery("drop table foo");
} catch(ex) {}
conn.executeQuery("create table foo (a int, b char(30))");
conn.executeUpdate("insert into foo values (5, 'test')");
Note that we use executeQuery for DDL (data definition language) and executeUpdate for DML (data modifying language). In fact, all DML (update, insert, delete) should occur using executeUpdate while all other statements should be run using executeQuery. The difference between the two functions is: executeQuery returns a result set. executeUpdate returns the number of affected rows. The distinction makes sense to some extent, although the function names could be better. Because of that, I suggest writing a wrapper for it.
function query(sql) {
try {
if (!sql.match(/^[ ]*(update|insert|delete)/i)) {
display_result(conn.executeQuery(sql));
} else {
alert("" + conn.executeUpdate(sql)
+ " rows affected");
}
} catch(ex) {
alert(conn.errorMessage);
}
}
We first determine whether the statement contains DML or something else. We then execute the statement. If we have a result set, we display it using a separate function. Otherwise, we show the number of affected rows. If an exception occurs, we display the last error message which occured on the connection.
Let us now look at result enumeration handling. The enumeration is returned from result.enumerate() and handled this way:
function display_result(result) {
var column_count = result.columnCount;
var have_more_data = result.rowCount > 0;
var set = result.enumerate();
var rows = [];
while (have_more_data) {
have_more_data = set.next();
var cols = [];
for (var i = 0; i < column_count; i++) {
cols.push(set.getVariant(i));
}
rows.push(cols.join(", "));
}
alert("" + rows.length + " row(s) in result set:\n"
+ rows.join("\n"));
}
The pattern used here (have_more_data) is the most succinct I could come up with considering the unusual iterator interface of the enumeration. Unlike other iterators, next() does not return false if there are no further rows in the set. Instead it will raise an exception. If next() returns true, you may call next() one more time. But how do we figure out if we may call next() the first time? We have no choice other than looking at the rowCount property. Again, a weird interface.
So, that is basic query handling with Mozilla's SQL extension. I've written a simple SQL query tool including a command-line history function (just 99 lines). You can get it here. For those who don't want to build the SQL extension themselves, I'll publish some binary builds for Mozilla 1.8 and 1.9.
Documentation about the used interfaces can be found here:
mozISqlConnection
mozISqlResult
mozISqlResultEnumerator
