Connecting Java with SQL express

Question

I've designed a database management system using Java and MySQL. I used WAMP as the server. Everything was perfect with that..until now..

Now, I have to go for SQL Express instead of WAMP. I know nothing about SQL Express. I've installed it under defaults.

However, now I can connect to the database in Microsoft SQL Server Management Studio with following selections, (LORDXAX-PC is my computer's name)

But I need to connect the database with my Java program. (I've used JDBC successfully before, with WAMP)

Need help to make this a success?? I've tried searching found certain things, but it gives errors. Can someone provide me with clean instructions..

Any help is appreciated. Thanks!

---

EDIT (2)

This is a complete code demonstrating the issue..

package ExpressTest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class MAIN {

    public static void main(String[] args) {
        new MAIN().start();
    }

    private void start() {
        System.out.println("START");
        String query = "SELECT * FROM logindata";
        getData(query);
        System.out.println("END");
    }

    public ResultSet getData(String query) {
        Connection CON = null;
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            CON = DriverManager.getConnection(URL, USER, PASS);
            RESULT = CON.createStatement().executeQuery(query);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return RESULT;
    }
    private String URL = "jdbc:jtds:sqlserver://localhost/brandix;instance=sqlexpress";
    private String USER = "";
    private String PASS = "";
    private static ResultSet RESULT;
}

This is the complete NetBeans output.

run:
START
java.sql.SQLException: Network error IOException: Connection refused: connect
    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:417)
    at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
    at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:185)
    at java.sql.DriverManager.getConnection(DriverManager.java:582)
    at java.sql.DriverManager.getConnection(DriverManager.java:185)
    at ExpressTest.MAIN.getData(MAIN.java:24)
    at ExpressTest.MAIN.start(MAIN.java:16)
    at ExpressTest.MAIN.main(MAIN.java:10)
Caused by: java.net.ConnectException: Connection refused: connect
    at java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
    at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
    at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
    at java.net.Socket.connect(Socket.java:519)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
END
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:311)
    at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:261)
    at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:318)
    ... 7 more
BUILD SUCCESSFUL (total time: 7 seconds)

Solution 1

Though many answers are available. I would like to give one.

As you are using Window Authentication.

Change JTDS URL from

private String URL = "jdbc:jtds:sqlserver://localhost/brandix;instance=sqlexpress";

TO

private String URL = "jdbc:jtds:sqlserver://localhost/brandix;instance=sqlexpress;useNTLMv2=tru‌​e;domain=workgroup";

OR Another alternative is:

Download the [Microsoft JDBC driver][1]rather than JTDS. Add sqljdbc4.jar to your classpath.

Update your connection string (JDBC URL) for you server

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

URL : jdbc:sqlserver://localhost;user=root;password=‌​123;"

If still problem, persist. Check with,

Enable TCP/IP network protocol, which is disable by default, and set the TCP/IP port to 1433 which is again default port no.

1. Open the SQL Server Configuration Manager Start -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager

2. Then at the left hand tree. Select SQL Server 2005 Network Configuration-> Protocol for SQLEXPRESS-> TCP/IP.

3. Right click and enable it.

4. A window box appear on double click the TCP/IP. Click on the "IP Addresses" Tab

5. Set the TCP Port value to 1433 then click apply

6. Restart SQL Service.

Solution 2

You will have to use a driver capable to talk to SQL Server, like jTDS for example.

Now for that case, your code would have to look similar to this:

public ResultSet getData(String query) {
    Connection CON = null;
    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver");
        CON = DriverManager.getConnection(URL, USER, PASS);
        RESULT = CON.createStatement().executeQuery(query);
    } catch (Exception ex) {
        System.err.println(">> " + ex.getMessage());
    }
    return RESULT;
}
private String URL = "jdbc:jtds:sqlserver://servername/brandix;instance=sqlexpress";
private String USER = "";
private String PASS = "";
private static ResultSet RESULT;

Also see @SPee's answer.

Solution 3

Basically your steps are:

1. Install the Microsoft JDBC driver of SQL Express into your classpath
2. Update your connection string (JDBC URL) for you server
3. Update your queries for SQL Express

Step 2 might require you to make SQL Express available through a TCP/IP connection, instead of a local pipe/named instance.

Step 3 might be the hardest. You must check your queries to be valid for SQL Express, use functions that exist with the correct parameters. And for a Database Management System you must also update all your queries for user management, table management, etc.

Solution 4

First download the JDBC-Driver from the microsoft website.

http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx

Add sqljdbc4.jar to your application.

Copy the sqljdbc_auth.dll in c:\windows\system32 and the /bin folder of your java installation.

You can use a connection url like this:

jdbc:sqlserver://D-PC-IT-02;instanceName=LOKALEINSTANZ;databaseName=MyDatabase;integratedSecurity=true; 

A reboot may be necessary.

View more solutions

Comments

[Anubis]

Thanks, I followed your instructions. Downloaded JTDS and added jtds-1.2.6.JAR to my project libraries (I'm using Netbeans). (But I haven't done anything else, like copying a dll or some other file to \windows\* directory...) Then I've tried using the above code. Well, at the first time, firewall appeared asking permission for access and I allowed it. Now every time I try to connect, it waits for couple of seconds and throws this exception,Network error IOException: Connection refused: connect. Any help?

[MicSim]

See the FAQ for jTDS for this: jtds.sourceforge.net/faq.html#connectionRefused. Also follow the instructions in this Microsoft article if needed: support.microsoft.com/default.aspx?scid=kb;EN-US;914277.

[Anubis]

Thanks, i've done as you said. How should i replace Class.forName("net.sourceforge.jtds.jdbc.Driver"); statement?

[Anubis]

Ah, i've followed your previous answer..anyway, i'll try this too

[Anubis]

I've done according to your edited answer. It throws this now: java.sql.SQLException: The USENTLMV2 connection property is invalid.. Do you want the full stacktrace? It's too long to put here, i'll add it to my post, if needed..

[Hardik Mishra]

@Anubis: Remove the property. I have not deep idea on SQL server with Windows Authentication. If You can resintall to use it SQL server.

[Anubis]

OK, i can login under SQL Server Authentication too. I've just now created a user root with password 123 and successfully connected to the database in MS SQL Server Management Studio. So, I thinks the problem is with the Java code. Let's get stick to JTDS or MS JDBC; anything you are prefer..what can i do now

[Anubis]

Will you please provide me with a complete code using Microsoft JDBC you suggested..I'm stuck here. Tried everything I can..

[Anubis]

Finally, i managed to make it run. You helped me a lot with this. Many thanks, really appreciate your help !!! I used Ms JDBC. I had to use Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"‌​); and CON=DriverManager.getConnection("jdbc:sqlserver://localhost;‌​user=root;password=1‌​23;");.. Hope it'll help someone.. (I found that these differ from the ones you should use with SQL server 2000)

[David Ferenczy Rogožan]

@HardikMishra Ohhh, so I didn't have the TCP/IP connections enabled. Visual Studio or LINQPad seem to not use the TCP/IP connection, because they were working fine. But I couldn't make it work in 0xDBE (from JetBrains). Your answer helped me a lot, thanks!