Tuesday, March 1, 2011

Connect to MS SQL Northwind sample from Java

 

(See below for update when running on x64 platform)

I just printed these names from Microsoft’s sample NorthWind database.

Here are the steps I used:

  1. I installed the Northwind database:
    1. download from here:
    2. copy the files to a new folder C:\Databases
    3. started SQL Server Management Studio
    4. Attached Northwind.mdb
      image
  2. Enabled TCP/IP connection on my SQL Server using SQL Server Configuration Manager
    image
    1. You need to restart the service (I used Windows services manager)
  3. Installed JDBC driver from MS:
    1. download from here
    2. unzipped it to C:\Program Files\Microsoft SQL Server JDBC Driver 3.0
    3. copied sqljdbc_auth.dll
      1. from C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\auth\x86
      2. to C:\WINDOWS\system32
  4. Used Netbeans to make a new Java Application and
    1. set the Main class as follows
package jdbctest;











 











import java.sql.*;











 











public class Main {











 











    public static void main(String[] args) {











 











        // Create a variable for the connection string.











        String connectionUrl = "jdbc:sqlserver://localhost;instanceName=SQLEXPRESS;"











                + "databaseName=Northwind;integratedSecurity=true;";











 











        // Declare the JDBC objects.











        Connection con = null;











        Statement stmt = null;











        ResultSet rs = null;











 











        try {











            // Establish the connection.











            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");











            con = DriverManager.getConnection(connectionUrl);











 











            // Create and execute an SQL statement that returns some data.











            String SQL = "SELECT TOP 10 * FROM dbo.customers";











            stmt = con.createStatement();











            rs = stmt.executeQuery(SQL);











 











            // Iterate through the data in the result set and display it.











            while (rs.next()) {











                System.out.println(rs.getString(4) + " " + rs.getString(6));











            }











        } // Handle any errors that may have occurred.











        catch (Exception e) {











            e.printStackTrace();











        } finally {











            if (rs != null) {











                try {











                    rs.close();











                } catch (Exception e) {











                }











            }











            if (stmt != null) {











                try {











                    stmt.close();











                } catch (Exception e) {











                }











            }











            if (con != null) {











                try {











                    con.close();











                } catch (Exception e) {











                }











            }











        }











    }











}





























    1. I copied this from the samples in:




      C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\help\samples\connections\connectURL.java







    2. the URL string is:




      "jdbc:sqlserver://localhost;instanceName=SQLEXPRESS;" + "databaseName=Northwind;integratedSecurity=true;"







    3. the SQL statement is:




      "SELECT TOP 10 * FROM dbo.customers"







    4. set the application libraries to include sqljdbc4.jar




      image








      you’ll find it:








      C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu














I actually did more than this, but I’ve left out the false starts.





When running under NetBeans on a Windows 7 (X64) platform



I had to copy the “correct” sqljdbc_auth.dll  to the top folder of project being tested.


image



The “correct” DLL is the one for the JVM running – not the Windows platform.



Unless you have done something “special” you are most likely running the x32 version of the JVM.

No comments: