File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes connection to mssql server database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "connection to mssql server database" Watch "connection to mssql server database" New topic
Author

connection to mssql server database

praveen shinde
Ranch Hand

Joined: Jun 03, 2009
Posts: 63
Hello friends,

I am trying to connect to a ms sql server database throught jdbc from a java program. The database is on another machine.
my question is :
Do i have to install a slq server client software in my machine to access the remote sql server database through java(jdbc).

regards,
Praveen
sourabh girdhar
Ranch Hand

Joined: Feb 10, 2010
Posts: 71

Hi Praveen,

You don't need any client on your machine. As this is JDBC connection only thing you need is Microsoft SQL server driver class.

Following is the name of driver class for SQL server -

com.microsoft.jdbc.sqlserver.SQLServerDriver




SCJP SCWCD AIX SOA
The significant problems we face cannot be solved by the same level of thinking that created them -- Albert Einstein

sourabh girdhar
Ranch Hand

Joined: Feb 10, 2010
Posts: 71

Just don't forget to include the required jar file in classpath while making connections.
praveen shinde
Ranch Hand

Joined: Jun 03, 2009
Posts: 63
Hi sourabh,
Thanks for the information.
I am using the jtds driver in my program.
So which driver is better . microsoft or the jtds.
regards,
Praveen

praveen shinde
Ranch Hand

Joined: Jun 03, 2009
Posts: 63
I have used netbeans.
And added the jar file by using the add jar utility in netbeans. So it is including the jar in the classpath right?
regards,
praveen.
sourabh girdhar
Ranch Hand

Joined: Feb 10, 2010
Posts: 71


Its your choice to choose driver . You can learn about different type of JDBC Drivers.
JTDS is type 4 driver , pure java. It should work fine.

When you add jar in netbeans it gets added to classpath specific to netbeans. If you run program through netbeans, it should work fine. If you try to run program from command prompt or some ant script, you need to add jar to classpath explicitly.
praveen shinde
Ranch Hand

Joined: Jun 03, 2009
Posts: 63
thanks for the help.
Praveen
praveen shinde
Ranch Hand

Joined: Jun 03, 2009
Posts: 63
Hello Saurabh,
I am getting the following error while connecting to database.
"
The TCP/IP connection to the host 10.237.162.49, port 1433 has failed. Error: Connection refused: connect. Please verify the connection properties and check that a SQL Server instance is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.
"
my connection url is
"jdbc:sqlserver://11.111.111.11\\APPLICATION\\TMS:1433;dataBaseName=SCBIVR;user=qq;password=qq";

any help is welcome
rgds,
Praveen
praveen shinde
Ranch Hand

Joined: Jun 03, 2009
Posts: 63
"jdbc:sqlserver://10.237.162.49\\APPLICATION\\TMS:1433;dataBaseName=xyz;user=sa;password=sa";

here the instance name = APPLICATION\TMS
Shahriar Khondokar
Greenhorn

Joined: Jun 11, 2010
Posts: 2
To connect to SQL Server from a Java application, you need to use the JDBC API. The JDBC API provides classes and methods that connect to the database, load the appropriate driver, send SQL queries, retrieve results etc.

HOW TO CONNECT TO THE DATABASE
A ‘Connection’ object represents a connection with a database.
To establish the connection, use the method ‘DriverManager.getConnection’. This method takes a string containing a URL which represents the database we are trying to connect to.
Below is the sample code for establishing a connection:
private String DATABASE_URL = "jdbcdbc:embedded_sql_app";
// establish connection to database
Connection connection = DriverManager.getConnection( DATABASE_URL,"sa","123" );
Detailed discussion about the Database URL and how to create it can be found in the resource provided at the end of this post.

QUERYING THE DATABASE
The JDBC API provides three interfaces for sending SQL statements to the database, and corresponding methods in the ‘Connection’ interface create instances of them.
1. Statement - created by the ‘Connection.createStatement’ methods. A ‘Statement’ object is used for sending SQL statements with no parameters.
2. PreparedStatement - created by the ‘Connection.prepareStatement methods’. A ‘PreparedStatement’ object is used for precompiled SQL statements. These can take one or more parameters as input arguments (IN parameters).
3. CallableStatement - created by the ‘Connection.prepareCall’ methods. ‘CallableStatement’ objects are used to execute SQL stored procedures from Java database applications.

RETRIEVING THE RESULT
A ‘ResultSet ‘is a Java object that contains the results of executing a SQL query. The data stored in a ‘ResultSet’ object is retrieved through a set of get methods that allows access to the various columns of the current row. The ‘ResultSet.next’ method is used to move to the next row of the ‘ResultSet’, making it the current row.
The following code fragment executes a query that returns a collection of rows, with column ‘a’ as an ‘int’, column ‘b’ as a ‘String’, and column ‘c’ as a ‘float’:
java.sql.Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
while (rs.next()) {
// retrieve and print the values for the current row
int i = rs.getInt("a");
String s = rs.getString("b");
float f = rs.getFloat("c");
System.out.println("ROW = " + i + " " + s + " " + f);
}

This is just a brief introduction on how to interact with a database from Java. For more details on the items discussed above as well as information on passing parameters, executing stored procedures etc. please refer to the following resource: ( http://www.shahriarnk.com/Shahriar-N-K-Research-Embedding-SQL-in-C-Sharp-Java.html#Shahriar_N_K_Embedding_SQL_In_C_Sharp_Calling_Stored_Procedure )
Here, you will also find information on how to interact with a database programmatically; i.e. without using SQL.
Hope you find this useful.
Anthony Kafiti
Greenhorn

Joined: Mar 30, 2011
Posts: 1
I apoligize if you may have answered my question in one of your above posts, but I'm really not good with java or database connections. Here's what I have. I'm trying to connect a java application to a database. I need to provide the setup with the following:

JDBC Driver Class:
Database URL:

The database is on the same machine with the application. I believe I have the Database URL correct of:

jdbc:jtds:sqlserver://111.111.120.15/Openfire;appName=jive

However, I can't seem to get past the JDBC Driver Class part. The generic template they provided was:

net.sourceforge.jtds.jdbc.Driver

However, I have gone out and downloaded Microsoft SQL Server JDBC Driver 3.0

The error I'm getting is:

"Unable to load the specified JDBC driver. Please verify the name of the driver is correct and that the driver is in the classpath of this server (usually the 'lib' directory). If you add a driver to your classpath you will neeed to restart the server. "

What would I put in place for the JDBC Driver Class?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Have you read this? The URL looks OK, so I assume you don't have jTDS on your classpath.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: connection to mssql server database