Random Etc. Notes to self. Work, play, and the rest.

A Quick Note on using SQLite in Processing

SQLite is a great standalone SQL database engine - not ideal for every situation (particularly large websites), but more than good enough to have already made its way into desktop projects from Apple, and more recently Adobe and Google.

My colleague Mike recently used it as a way to distribute data from a 511.org transit website scraping project he's been doing. I wanted to see if I could download his data (gathered and processed using python) and access it using Processing.

Web searches for SQLite and Java (java wrapper sqlite, etc.) turn up lots of matches, including this promising tutorial from Tim Anderson, but sadly the most prominent matches didn't work for me. It's a real pain to get up and running and installed in a reliable way, mainly due to the need to compile SQLite natively for each platform and then talk to the native code using Java.

Enter this amazing project, a JDBC library for SQLite written in pure java that uses NestedVM to compile the C code for SQLite into something any Java VM can use. It's frightening to think about the amount of misdirection, abstraction and interpretation going on here, but it worked first time and plenty fast enough for my purposes.

Take a look after the jump for the code I ended up with. I hope people find it useful:

 
 
// download the pure java lib from http://www.zentus.com/sqlitejdbc/
//   ... then put the jar file in the code folder (drag and drop it onto this sketch)
 
// I read http://www.pysquared.com/files/Java/JavaSQLiteExample/ for a basic primer too
 
import java.sql.*;
 
void setup() {
 
  size(100,100);
  noLoop(); 
 
  try {
 
    // It's ugly, even for a java idiom, but you need to do this so that
    //  DriverManager doesn't throw an error looking for sqlite.
    Class.forName("org.sqlite.JDBC");
 
    // create a database, or open an existing database, in the sketch data folder:
    // I have no idea if this works in an applet yet
    String dbFileName = dataPath("transit.db");
    println("opening: " + dbFileName);
 
    // this normally expects a URL, hence the funky jdbc:sqlite: protocol before the file path
    Connection conn = DriverManager.getConnection("jdbc:sqlite:"+dbFileName);
 
    // http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Connection.html
    // NB: the docs say "If the same SQL statement is executed many times, it may
    // be more efficient to use a PreparedStatement object."
    Statement stmt = conn.createStatement();
 
    // http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html
    // executeQuery returns "true if the first result is a ResultSet object;
    //                       false if it is an update count or there are no results"
    ResultSet rs = stmt.executeQuery("SELECT * FROM stops LIMIT 10"); // delete "LIMIT 10" to get everything!
 
    // http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSet.html
    // "A ResultSet cursor is initially positioned before the first row;
    //  the first call to the method next makes the first row the current row"
    while (rs.next()) {
      String provider   = rs.getString("provider");
      String route_name = rs.getString("route_name");
      String schedule_name = rs.getString("schedule_name");
      String stop_location = rs.getString("stop_location");
      String stop_time = rs.getString("stop_time");
      String schedule_url = rs.getString("schedule_url");
 
      println("provider: " + provider);
      println("route_name: " + route_name);
      println("schedule_name: " + schedule_name);
      println("stop_location: " + stop_location);
      println("stop_time: " + stop_time);
      println("schedule_url: "+ schedule_url);
    }
 
    conn.close();
  }
  catch(Exception e) { // ClassNotFoundException, SQLException
    e.printStackTrace();
  }
 
}
 
void draw() {
  // nothing yet!
}
 

4 Comments

nice. thanks tom!

talking of SQLite, carolin and did a project together which uses SQLite too. it’s build around the Apple Mail 2.0 database (”~/Library/Mail/Envelope Index”), which is SQLite. documentation is here:
http://carohorn.de/anymails/

F

Posted by Florian Jenett on 31 July 2007 @ 7am

[...] In the spirit of continuing our impromptu database week on Processing Blogs (my post, toxi’s post, then Florian Jennet updating the sql library), I thought I’d post another quick example using Lucene. [...]

Posted by Random Etc. : Blog Archive : A quick (less certain) note on using Lucene in Processing on 1 August 2007 @ 10pm

[...] 3.点击sketch-add file 把下载好的sqliteJDBC.jar文件放入sketch文件夹。 4.在sketch文件夹内新建data文件夹存放数据库之用。 参考a quick note on using sqlite in processing [...]

Posted by Plasticthinking - unlimited edition » Blog Archive » SQLite和processing on 29 May 2009 @ 9am

[...] Two ways wrapper Java JDBC driver for use. I adopt the SQLiteJDBC. You can look at from A Quick Note on using SQLite in Processing [...]

Posted by SQLite in Java quickly « When IE meets SE… on 23 July 2009 @ 10am