Getting Processing working with SQL Server 2008

“messy times” from el frijole – (?)

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.

About these ads