Ugh so I was forced to setup something with JDBC. It’s been like forever since I have messed with Java in forever. So I thought I’d try something simple first. I found this very simple program to query against the NorthWind database from here.

// Import the SQL Server JDBC Driver classes 
import java.sql.*;

class Example 
{  
       public static void main(String args[]) 
       {  
       try  
       { 
            // Load the SQLServerDriver class, build the 
            // connection string, and get a connection 
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
            String connectionUrl = "jdbc:sqlserver://ServerName;" + 
                                    "database=northwind;" + 
                                    "user=UserName;" + 
                                    "password=Password"; 
            Connection con = DriverManager.getConnection(connectionUrl); 
            System.out.println("Connected.");

            // Create and execute an SQL statement that returns some data.  
            String SQL = "SELECT CustomerID, ContactName FROM Customers";  
            Statement stmt = con.createStatement();  
            ResultSet rs = stmt.executeQuery(SQL);

            // Iterate through the data in the result set and display it.  
            while (rs.next())  
            {  
               System.out.println(rs.getString(1) + " " + rs.getString(2));  
            }

       }  
       catch(Exception e)  
       { 
            System.out.println(e.getMessage()); 
            System.exit(0);  
       } 
    } 
}

As you can see it’s pretty simple.  The server I’m using is on the default instance so I don’t need the instance name.  So first thing off, compile the program, and run it, right?

# ./javac sql.java
# ./java Example
com.microsoft.sqlserver.jdbc.SQLServerDriver

Well that’s great.  No doubt we actually need a driver from Microsoft, which surprisingly wasn’t too hard to find.  I’m sure the link will drift over the years, but right now here is the Microsoft JDBC Driver 6.2 for SQL Server.  From what I remember you would just use the jar flag, and be on your way.

# ./java -jar mssql-jdbc-6.2.2.jre8.jar Example
no main manifest attribute, in mssql-jdbc-6.2.2.jre8.jar

Great.  What’s this crap?

Well it turns out that you now need a Manifest.txt file.  Oh and the best part is that it needs a blank line at the end of the file.  So much time spent trying to figure that one out.

Manifest-Version: 1.0
Class-Path: mssql-jdbc-6.2.2.jre8.jar
Main-Class: Example

Ok, now to make my life easier I’m just going to throw this thing into a jar.

jar -cfm Example.jar Manifest.txt Example.class mssql-jdbc-6.2.2.jre8.jar

and now we get to the real fun, trying to get it to run.  My main testing SQL server is an ancient SQL Server 7.0 SP4 which I really need to just finally get around to upgrading.  While it’s served it’s time as a good base test instance, time has finally come to that point where nothing is going to talk to it anymore.  But while I was crazy enough to try to talk to it I got this fun error:

WARNING: ConnectionID:1 Prelogin error: host ServerName port 1433 Unexpected end of prelogin response after 0 bytes read

I guess the hint is the Prelogin, as it’s failing the higher security checks for the authentication.  So I quickly installed a 2003 server along with SQL Server 2005.  And oddly enough it was lacking the Northwind database, but I did find this great site, northwinddatabase.codeplex.com with a handy SQL script to generate the database.

Update the java file to point to the new server, and …

# ./java -jar Example.jar
Connected.
ALFKI Maria Anders
ANATR Ana Trujillo
ANTON Antonio Moreno
AROUT Thomas Hardy
….
WILMK Matti Karttunen
WOLZA Zbyszek Piestrzeniewicz

And there we go!  Hurrah!