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
      
2. Enabled TCP/IP connection on my SQL Server using SQL Server Configuration Manager
   
   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
   
   
   
   
   
   
   
   
   
   
   
   
   
   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.

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.