Search code examples
javajdbc

JDBC: Simple MSSql connection example not working

I am learning Java and need to just run something simple to retrieve some data from MSSQL via JDBC. The example in my book doesn't work (but it is several years old) and this example below from MS doesn't work for me either:

http://msdn.microsoft.com/en-us/library/ms378956(v=sql.90).aspx

Here's my code:

package javasql;
import java.sql.*;
import java.util.*;

public class Program {

    private static String url = "jdbc:sqlserver://localhost\\SQLExpress;database=Northwind;integratedSecurity=true;";
    //private static String userName = "sa";
    //private static String password = "myPassword";

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        RunDemo();
    }

    public static void RunDemo() {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection connection = DriverManager.getConnection(url);

            Statement statement = connection.createStatement();
            ResultSet results = statement.executeQuery("SELECT ProductName, Price FROM Products ORDER BY ProductName");

            while(results.next()) {
                System.out.println("Product Name: " + results.getNString("ProductName") + " Price: $" + results.getFloat("UnitPrice"));
            }

        } catch (ClassNotFoundException | SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
}

When I run the code, I don't get any exceptions thrown.. I just get this in the output window:

run:
com.microsoft.sqlserver.jdbc.SQLServerDriver
BUILD SUCCESSFUL (total time: 0 seconds)

I am using NetBeans 7.2. Please someone give me a working example.

EDIT:

By the way, for the connection string, where you see the \\SQLExpress, I did try removing that and using instanceName=SQLExpress instead.. but that didn't have any effect either.

EDIT 2:

OK, I downloaded the latest JDBC driver for MSSQL from MS and referenced the 2 JAR files in there. Now I'm getting this output:

run:
The connection to the host localhost, named instance SQLExpress failed. 

Error: "java.net.SocketTimeoutException: Receive timed out". 

Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434.  
For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host.
BUILD SUCCESSFUL (total time: 15 seconds)

Progress.. at least we can see it is trying to connect now, can someone enlighten me as to the above error though?

EDIT 3:

2 more problems fixed.. one is enable SQL Server Browser and the second was enabling TCP/IP for SQL Server. Thanks @Vikdor Now I'm getting this error:

run:
The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
BUILD SUCCESSFUL (total time: 15 seconds)

I checked windows firewall and added an inbound rule to allow that port, but I'm still getting the above error. Any ideas?

EDIT 4:

Tried the solution in this link: http://www.coderanch.com/t/306316/JDBC/databases/SQLServerException-TCP-IP-connection-host

No longer getting error in EDIT 3. Now getting another...

run:
Sep 21, 2012 11:33:16 AM com.microsoft.sqlserver.jdbc.AuthenticationJNI <clinit>
WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path
This driver is not configured for integrated authentication. ClientConnectionId:577f359e-4774-45f3-96fb-588785911817
BUILD SUCCESSFUL (total time: 14 seconds)

Getting very tired of this now.. why Java, why?? Seriously...I'm glad I work mostly with .NET. Well, when i find the solution, I will post it here to make sure it can help others before they go mad as I am about to...

EDIT 5:

This helped: java connecting to MicrosoftSQLServer 2005

I put the directory path into my PATH environment variable. Didn't work, so I also placed the sqljdbc_auth.dll into my JDK folder C:\Program Files\Java\jdk1.7.0_04\bin. Solved.

Solution

  • OK, so here's what solved my problems:

    1. Download latest MSSQL JDBC driver from here: http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx

    2. Referenced the 2 JAR files in my project: sqljdbc.jar and sqljdbc4.jar (I'm not yet sure if both of the above are required or just one..)

    3. Make sure the SQL Server Browser windows service is running

    4. Open SQL Server Configuration Manager and go to Protocols for SQLEXPRESS under SQL Server Network Configuration. Right-click on TCP/IP and choose Properties. Set Enabled = YES.

    5. While you're there, click on IP Addresses tab and find the section IP All. Set TCP Port to 1433.

    6. Add sqljdbc_auth.dll to your PATH Environment Variable. In my case: D:\Java\sqljdbc_4.0\enu\auth\x64

    7. Copy the sqljdbc_auth.dll to your JDK directory. In my case: C:\Program Files\Java\jdk1.7.0_04\bin

    I hope this helps someone.