?
Solved

Connecing MSSQl with Java

Posted on 2009-02-16
36
Medium Priority
?
575 Views
Last Modified: 2012-05-06
Hi,

I am working on java after a long time

I need to connect MSSQl with java version "1.6.0_11"

I am making standalone application . I t is not web application

Can you please help me what jar file I need.

Also I need connection string to connect  MSSQL 2005 with windows authentication and without windows authentication

Thanks
0
Comment
Question by:tia_kamakshi
  • 19
  • 14
  • 2
  • +1
36 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 23648565
You should probably try to use the JDBC driver that comes with the DB.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23648584
As for authentication, you can use a Trusted Connection for Win auth. See

http://www.datadirect.com/developer/jdbc/topics/winauth/index.ssp
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:tia_kamakshi
ID: 23649026
Hi,

I copied sqljdbc.jar file in directory C:\Program Files\Java\jdk1.6.0_11\lib

and in classpath I wrote

%classpath%;C:\Program Files\Java\jdk1.6.0_11\lib\sqljdbc.jar

In connection string I wrote

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

I got an exception

Got an exception!
com.microsoft.sqlserver.jdbc
Press any key to continue . . .


When I write

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

I get an error

Got an exception!
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Press any key to continue . . .

My servers details are below

host = 172.17.1.65
DBServer = ABCD-PBHA23
database = Workflow
user = "sa"
password = "sa"

I need windows authentication connection string

Please guide

Thanks
//  Establish a connection to an Interbase database using JDBC.
import java.sql.*;
 
class JdbcTest1 {
 
    public static void main (String[] args) {
        try {
 
            // Step 1: Load the JDBC driver.
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            //Class.forName("com.microsoft.sqlserver.jdbc");
 
            // Step 2: Establish the connection to the database.
            String url = "jdbc:odbc:contact_mgr";
            //jdbc:msql://www.myserver.com:1114/contact_mgr
            Connection conn = DriverManager.getConnection(url,"user1","password");
 
        } catch (Exception e) {
            System.err.println("Got an exception! ");
            System.err.println(e.getMessage());
        }
    }
}

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23649058
Try

String url = "jdbc:odbc:ABCD-PBHA23/Workflow";
...


Connection conn = DriverManager.getConnection(url,"sa","sa");
0
 

Author Comment

by:tia_kamakshi
ID: 23649152
But i need to give server address where sql server is running

Its on 172.17.1.65

And I need to use windows authentication.

I dont want to use sql authetication connection

Thanks
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23649158
>>I copied sqljdbc.jar file in directory C:\Program Files\Java\jdk1.6.0_11\lib


Put that in

C:\Program Files\Java\jdk1.6.0_11\jre\lib\ext
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23649174
>>Its on 172.17.1.65

Doesn't ABCD-PBHA23 resolve to that?
0
 

Author Comment

by:tia_kamakshi
ID: 23649179
Getting the same exception

Got an exception!
[Microsoft][ODBC Driver Manager] Data source name not found and no default drive
r specified
Press any key to continue . . .


My host is at 172.17.1.65
//  Establish a connection to an Interbase database using JDBC.
import java.sql.*;
 
class JdbcTest1 {
 
    public static void main (String[] args) {
        try {
 
            // Step 1: Load the JDBC driver.
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            //Class.forName("com.microsoft.sqlserver.jdbc");
 
            // Step 2: Establish the connection to the database.
            String url = "jdbc:odbc:ABCD-PBHA23/Workflow";
            //jdbc:msql://www.myserver.com:1114/contact_mgr
            Connection conn = DriverManager.getConnection(url,"user1","password");
 
        } catch (Exception e) {
            System.err.println("Got an exception! ");
            System.err.println(e.getMessage());
        }
    }
}

Open in new window

0
 

Author Comment

by:tia_kamakshi
ID: 23649184
No
>>Its on 172.17.1.65

It doesn't ABCD-PBHA23 resolve to 172.17.1.65
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23649228
Try

jdbc:odbc:127.17.1.65/Workflow

in that case
0
 

Author Comment

by:tia_kamakshi
ID: 23649284
Same error

Connection conn = DriverManager.getConnection(url,"user1","password");

Also, it is using windows authentication, so there will be no username and password

Anyway, I am still getting the same error

Thanks for your co-operation
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23649296
But you should really be using the JDBC driver, so the connection string should be

final String CONNECT_STRING = "dbc:sqlserver://172.17.1.65\\Workflow";
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23649320
For Windows authentication, that should be

final String CONNECT_STRING = "dbc:sqlserver://172.17.1.65\\Workflow;integratedSecurity=true";
0
 

Author Comment

by:tia_kamakshi
ID: 23649349
Thanks then where should I use CONNECT_STRING in my above code

Also, I think I need JDBC jar file to do so.

If yes, I need to download that as well.
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 1000 total points
ID: 23649537
This should get you the latest driver
http://www.microsoft.com/downloads/details.aspx?FamilyId=C47053EB-3B64-4794-950D-81E1EC91C1BA&displaylang=en

The connection string should appear just before you call getConnection
0
 

Author Comment

by:tia_kamakshi
ID: 23650189
I have downloaded file sqljdbc_2.0.1607.1_enu.exe from the given URL

When I extracted the files, the jar file has the following packages

com.microsoft.sqlserver.jdbc


Classpath added
C:\me\Pers\Java\JDBC\Windows\sqljdbc_2.0.1607.1_enu\sqljdbc_2.0\enu.jar;C:\me\Pers\Java\JDBC\Windows\sqljdbc_2.0.1607.1_enu\sqljdbc_2.0\enu\sqljdbc4.jar

In the
dbc:sqlserver://172.17.1.65\\Workflow;integratedSecurity=true

Where is the DBServer name PBMS-PBHA23. This is the DB server which has the database in it

Below code gives same error

Got an exception!
com.microsoft.sqlserver.jdbc
Press any key to continue . . .

import java.sql.*;
 
class JdbcTest1 {
 
    public static void main (String[] args) {
        try {
 
            // Step 1: Load the JDBC driver.
            //Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Class.forName("com.microsoft.sqlserver.jdbc");
 
            // Step 2: Establish the connection to the database.
            //String url = "jdbc:odbc:PBMS-PBHA23/DocumationWorkflow";
            //String url = "jdbc:odbc:172.17.1.65/DocumationWorkflow";
            String CONNECT_STRING = "dbc:sqlserver://172.17.1.65\\Workflow;integratedSecurity=true";
 
            //jdbc:msql://www.myserver.com:1114/contact_mgr
            //Connection conn = DriverManager.getConnection(url,"user1","password");
            Connection conn = DriverManager.getConnection(CONNECT_STRING);
 
        } catch (Exception e) {
            System.err.println("Got an exception! ");
            System.err.println(e.getMessage());
        }
    }
}

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23650240
>>String CONNECT_STRING = "dbc:sqlserver://172.17.1.65\\Workflow;integratedSecurity=true";

should be

String CONNECT_STRING = "jdbc:sqlserver://172.17.1.65\\Workflow;integratedSecurity=true";
0
 
LVL 2

Expert Comment

by:mandar_udg80
ID: 23650396
Assuming that you dont require windows authentication, use the following code,-
You havent mentioned any port in the connection url.

try following.

try{
java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433;DatabaseName=mydb;user=testadmin;password=admin");
}catch(Exception ex){
//exception here
}

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23650625
Merging those, try


String CONNECT_STRING = "jdbc:sqlserver://172.17.1.65:1433;DatabaseName=Workflow;integratedSecurity=true";
0
 

Author Comment

by:tia_kamakshi
ID: 23650842
Got an exception!
The TCP/IP connection to the host  has failed. java.net.ConnectException: Connec
tion refused: connect
Press any key to continue . . .

If I ping 172.17.1.65

Pinging 172.17.1.65 with 32 bytes of data:

Reply from 172.17.1.65: bytes=32 time=20ms TTL=128
Reply from 172.17.1.65: bytes=32 time=23ms TTL=128
Reply from 172.17.1.65: bytes=32 time=19ms TTL=128
Reply from 172.17.1.65: bytes=32 time=19ms TTL=128

Ping statistics for 172.17.1.65:
    Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 19ms, Maximum = 23ms, Average = 20ms
//  Establish a connection to an Interbase database using JDBC.
import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;
import com.microsoft.sqlserver.*;
 
class JdbcTest2 {
 
    public static void main (String[] args) {
 
 
		try{
		java.lang.Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		//Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433;DatabaseName=DocumationWorkflow;integratedSecurity=true");
		Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433;DatabaseName=DocumationWorkflow;user=test;password=test");
		}catch(Exception e){
			System.err.println("Got an exception! ");
            System.err.println(e.getMessage());
		}
 
    }
}

Open in new window

0
 

Author Comment

by:tia_kamakshi
ID: 23650860
Also, we have not added dbserver name

DBServer = ABCD-PBHA23

I think we require that as well some where

Thanks for your co-operation
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23650922
Pinging it won't tell you much. You need to know:

a. that the MSSQL server is running on that host
b. what port it's running on

Check your server config
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23650934
>>DBServer = ABCD-PBHA23

Are you sure that's not the hostname? What does the following do?


ping ABCD-PBHA23

Open in new window

0
 

Author Comment

by:tia_kamakshi
ID: 23651101
IP: 172.17.1.65 --> It is the computer IP where MSSQL server is hosted

DBServer = ABCD-PBHA23 --> When you click on SQL management studio, MSSQl first asks for screen where you put your user name password or do windows authenitication. This is that screen. Please see screen attached



dbscreen.bmp
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23651200
That could be the 'instance name' then, so try

String CONNECT_STRING = "jdbc:sqlserver://172.17.1.65:1433\\ABCD-PBHA23;DatabaseName=Workflow;integratedSecurity=true";
0
 

Author Comment

by:tia_kamakshi
ID: 23651264
Error message changed to

Got an exception!
Invalid integratedSecurity property value:true
Press any key to continue . . .
0
 

Author Comment

by:tia_kamakshi
ID: 23651365
When I use my connection string like this

Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433\\ABCD-PBHA23;DatabaseName=DocumationWorkflow;user=test;password=test");

I get the error

Got an exception!
The port number 1433\PBMS-PBHA23 is not valid.
Press any key to continue . . .
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23651679
You must ascertain what port it's running on, or try leaving the port out of the string
0
 

Author Comment

by:tia_kamakshi
ID: 23651872
I just came to know that PBMS-PBHA23 is the 172.17.1.65 itself

Also, I have not registered sqljdbc_xa.dll file which come with sqljdbc_1.0.809.102_enu.exe

Do I need to register this??

Also here is the current error


Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433;DatabaseName=DocumationWorkflow;integratedSecurity=true");

Got an exception!
Invalid integratedSecurity property value:true
Press any key to continue . . .


Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433;DatabaseName=DocumationWorkflow;user=test;password=test");

Got an exception!
The TCP/IP connection to the host  has failed. java.net.ConnectException: Connec
tion refused: connect
Press any key to continue . . .


Thanks for all your co-operation
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23652796
You need to make sure you've installed the driver properly. Read it's help files and see the following:

http://www.devx.com/dbzone/Article/33681
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23652955
Once you're sure you've got it installed properly, the following shows how to make the connection string:

http://msdn.microsoft.com/en-us/library/ms378428.aspx
0
 
LVL 92

Assisted Solution

by:objects
objects earned 1000 total points
ID: 23654116
> Do I need to register this??

no

> Also here is the current error

try:

Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433;DatabaseName=DocumationWorkflow;integratedSecurity=true;");

0
 

Author Closing Comment

by:tia_kamakshi
ID: 31547264
Many Many Thanks.

Connection c = java.sql.DriverManager.getConnection("jdbc:sqlserver://172.17.1.65:1433;DatabaseName=DocumationWorkflow;integratedSecurity=true;");

It works
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 23654906
:-)
0
 
LVL 92

Expert Comment

by:objects
ID: 23654914
let me know if you have any more questions, I should be able to save you having to guess what the right answer is.

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Suggested Courses
Course of the Month17 days, 9 hours left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question