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!
}