Getting Processing working with SQL Server 2008


"messy times"
from el frijole(?)

(N.B. If you read down to the comments you’ll notice that Michael Friis has written a shorter and far more readable version of this process with my erroneous steps removed at http://friism.com/processing-and-sql-server-in-windows )

I’ve just spent a day getting Processing (aka Proce55ing) working with a Microsoft SQL Server 2008 database. It wasn’t hard, just a bit fiddly, so I thought others might appreciate it if I documented my route. Partly because of the Processing language’s open source heritage most of the discussion about databases on the Processing site centres on MySQL, but with a few changes it can be made to work for SQL Server too.

STEP 1
I already had Microsoft SQL Server 2008 installed.

STEP 2
Next I downloaded the SQL Server 2005 JDBC Driver and unzipped it to the suggested default location. Ignore the 2005 in it’s name – it works for 2008 as well though does not confer the additional 2008 features. Following the set-up instructions I added "C:\Program Files\Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.2\enu\sqljdbc.jar" to the CLASSPATH environmental variable, though I think this might work without that step.

STEP 3
I used the code in Ben Fry’s book "Visualizing Data". It’s on page 291 in the section titled "Using MySQL with Processing". I adjusted it slightly – Ben hides the construction of the connection string inside the Database class’ constructor.

Here’s the helper class (added in a second tab)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

class Database
{
  Connection conn;
  String connectionURL;
  
  public Database (String connectionURL)
  {
    this.connectionURL = connectionURL;
        
    this.conn = connect();
  }
  
  public Connection connect()
  {
    try
    {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
    }
    catch (ClassNotFoundException e)
    {
      e.printStackTrace();
    }
    catch (InstantiationException e)
    {
      e.printStackTrace();
    }
    catch (IllegalAccessException e)
    {
      e.printStackTrace();
    }    
    
    try
    {
      return DriverManager.getConnection(this.connectionURL);
    }
    catch (SQLException e)
    {
      e.printStackTrace();
      return null;
    }    
  }
  
  public ResultSet query(String query)
  {
    try
    {
      Statement st = this.conn.createStatement();
      ResultSet rs = st.executeQuery(query);
      return rs;
    }
    catch (SQLException e)
    {
      e.printStackTrace();
      return null;
    }     
  }
}

and the main code is …

import java.sql.ResultSet;

void setup()
{
  Database db = new Database("jdbc:sqlserver://localhost:1433;databaseName=Book;integratedSecurity=true;");
  ResultSet rs = db.query("SELECT TOP 100 * FROM Words");
  
  try
  {
    while (rs.next())
    {
      String word = rs.getString(1);
      println(word);
    }
  }
  catch (SQLException e)
  {
    e.printStackTrace();
  }
}

But this threw an error: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect, and so I thought I’d try to get it working in the NetBeans IDE first.

STEP 4

At first I couldn’t get the code to build and needed to add a few import statements (e.g. this should have been a clue that I was using the wrong SQL library but there you go). Then I found Nipawit Luangaroon’s excellent post all about accessing SQL Server from NetBeans in which he gives clear simple instructions on adding the correct library to the project’s CLASSPATH:  http://www.linglom.com/2007/03/04/accessing-sql-server-on-netbeans-using-jdbc-part-i-create-a-connection/

STEP 5

Then I got a different error message: WARNING: Failed to load the sqljdbc_auth.dll com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. This time the online documentation came to the rescue: http://msdn.microsoft.com/en-us/library/ms378428.aspx I needed to copy the file sqljdbc_auth.dll from "C:\Program Files\Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.2\enu\auth\x86" to "C:\Windows\System32".

STEP 6

Still not quite there. The error message I got this time was back to com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host  has failed. java.net.ConnectException: Connection refused: connect. So I needed to check if the SQL server was running on the port I expected. I tried "telnet localhost 1433" from the cmd prompt which fail with "Connecting To localhost…Could not open connection to the host, on port 1433: Connect failed". Actually I’m missing out a step.

STEP 7

Install telnet. Vista doesn’t seem to have telnet enabled by default but if you go to Control Panel -> Programs and Features -> Turn Windows features on or off -> [check] Telnet Client -> OK

STEP 8

To find out what port SQL Server 2008 is running on I went into the management studio and expanded "Management" in the Object Explorer and then opened SQL Server Logs -> Current. That had a line saying that the server is listening on port 1434 (I thought it was on 1433). So my line of code became

Database db = new Database("jdbc:sqlserver://localhost:1434;databaseName=Pullman;integratedSecurity=true;");

Done :-)

It’s all working now and I’m a happy bunny. I don’t know how many other folk are working through the same path but I thought I’d post it just-in-case it’s useful.

———- Revision ———-

I’ve just been struggling to get this working on Linda’s laptop. I think I missed out documenting a step. You seem to need a library directory containing sqljdbc.jar in the Processing libraries folder. For me that means creating a directory named sqljdbc inside C:\Program Files\processing-0135-expert\libraries. Within that directory create another called library, and inside that copy sqljdbc.jar from C:\Program Files\processing-0135-expert\libraries. Without this I get the same exception I’m seeing on Linda’s laptop: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver but with it the sketch works. I’ll check that fix works on Linda’s laptop too tomorrow.

2 Responses to “Getting Processing working with SQL Server 2008”

  1. friism Says:

    Hi Tim, thanks for taking the time to write this guide. I found some inconsistencies and have written a slightly shorter description here:
    http://www.itu.dk/~friism/blog/?p=148

  2. dumbledad Says:

    Michael: that’s great. Thanks for tidying up the process and pointing out my CLASSPATH red-herring. 1,000,000 times more readable than this post!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: