JDBC not connecting to DB within webservice

Question

I have been working with JDBC. Below is the code I use to obtain connection.

JDBC code:

    Connection con = null;
    Statement st = null;
    final String DB_URL = "jdbc:jtds:sqlserver://00.00.00.00:0000/DB";
    // Database credentials
    final String USER = "usrname";
    final String PASS = "pw";

    try {   
       Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
       con = DriverManager.getConnection(DB_URL, USER, PASS);
       st = con.createStatement();

       System.out.println("successfully connected!");
    } catch (Exception err) {
       System.out.println(" " + err.getMessage ());
    }
    finally {
       try {
          con.close();
       } catch (Exception e) { /* ignored */ }
          try { 
             st.close(); 
          } catch (Exception e) { 
             /* ignored */ 
          }
       }
    }
}

I have generated a Webservice off of a WSDL which contains getters and setters. While it is in the same package of the Webservice (its own class) I can simply run the class and it will generate "Successfully connected". (keep in mind that the build is the same since its in the same package) Now when I insert the code into a method on the Webservice and call it using insert();. From here I invoke the Webservice with the generated client. When looking back at the console I obtain the "com.microsoft.sqlserver.jdbc.SQLServerDriver" error. Which is generally given when paths are not built correct.

It works outside of the getter and setter class, but why not inside?

What is insert()? Do you deploy the webservice to a server? Can you post a stack trace for the problem? — Greycon, Aug 01 '14 at 15:53
insert() is the method I have created within the getter and setter class file. (to call the jdbc under a condition). For example I had insert(); which printed hello, I replaced the print statement with the jdbc content. The only thing that appears in the stack trace is "com.microsoft.sqlserver.jdbc.SQLServerDriver". Ive ran across that when packages are not enabled correctly. But considering it is in the same package, I'm clueless. — , Aug 01 '14 at 16:07
Could you post the code for whatever getter or setter method you are using to call insert? — Greycon, Aug 01 '14 at 16:09
@Greycon I am using the code above. I'm not inserting anything at this time(using the getters or setters). I'm only trying to gain a connection. But it is not printing the statement "aka not obtaining a connection". Gaining a connection outside of the Web-Service class, but within the package works. — , Aug 01 '14 at 16:15
You are question clarity and information is not sufficient, I guess someone will be better able to help you if you can provide your code snippets for your so called getter and setter methods (if you are saying tht you are getting issue while simply running the JDBC code then I am guessing tht you are missing SQL db server driver JAR in your classpath), and also complete stack trace .. — hagrawal7777, Oct 10 '16 at 19:50

score 3 · Answer 1 · edited May 23 '17 at 12:23

I have tried to sync a full documentation. It will cover

Various ways of database connection
Sample code for testing connection
Common errors and how we can solve those issues
Define JRE support
How to set the CLASSPATH variable?
How to register the Driver?
How to pass the Connection URL?
Choosing the Right JAR file
Making a Simple Connection to a Database

After installing the Microsoft SQL Server 2000 driver for JDBC, database connection may be done by two ways:

1) with a connection URL, or

2) with a JNDI data source

Sample Code to Test the Connection

The following sample code tries to connect to the database and displays the database name, the version, and the available catalogs. Replace the server properties with the values for your server:

import java.*;
public class Connect{
     private java.sql.Connection  con = null;
     private final String url = "jdbc:microsoft:sqlserver://";
     private final String serverName= "localhost";
     private final String portNumber = "1433";
     private final String databaseName= "pubs";
     private final String userName = "user";
     private final String password = "password";
     // Informs the driver to use server a side-cursor, 
     // which permits more than one active statement 
     // on a connection.
     private final String selectMethod = "cursor"; 

     // Constructor
     public Connect(){}

     private String getConnectionUrl(){
          return url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
     }

     private java.sql.Connection getConnection(){
          try{
               Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); 
               con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
               if(con!=null) System.out.println("Connection Successful!");
          }catch(Exception e){
               e.printStackTrace();
               System.out.println("Error Trace in getConnection() : " + e.getMessage());
         }
          return con;
      }

     /*
          Display the driver properties, database details 
     */ 

     public void displayDbProperties(){
          java.sql.DatabaseMetaData dm = null;
          java.sql.ResultSet rs = null;
          try{
               con= this.getConnection();
               if(con!=null){
                    dm = con.getMetaData();
                    System.out.println("Driver Information");
                    System.out.println("\tDriver Name: "+ dm.getDriverName());
                    System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
                    System.out.println("\nDatabase Information ");
                    System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
                    System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
                    System.out.println("Avalilable Catalogs ");
                    rs = dm.getCatalogs();
                    while(rs.next()){
                         System.out.println("\tcatalog: "+ rs.getString(1));
                    } 
                    rs.close();
                    rs = null;
                    closeConnection();
               }else System.out.println("Error: No active Connection");
          }catch(Exception e){
               e.printStackTrace();
          }
          dm=null;
     }     

     private void closeConnection(){
          try{
               if(con!=null)
                    con.close();
               con=null;
          }catch(Exception e){
               e.printStackTrace();
          }
     }
     public static void main(String[] args) throws Exception
       {
          Connect myDbTest = new Connect();
          myDbTest.displayDbProperties();
       }
}

If this code is successful, the output is similar to the following:

Connection Successful! Driver Information
        Driver Name: SQLServer
        Driver Version: 2.2.0022

Database Information
        Database Name: Microsoft SQL Server
        Database Version: Microsoft SQL Server  2000 - 8.00.384 (Intel X86)
        May 23 2001 00:02:52
        Copyright (c) 1988-2000 Microsoft Corporation
        Desktop Engine on Windows NT 5.1 (Build 2600: )

Avalilable Catalogs
        catalog: master
        catalog: msdb
        catalog: pubs
        catalog: tempdb

Basic Connectivity Troubleshooting

Error-1

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Login failed for user 'user'. Reason: Not associated with a trusted SQL Server connection.

Ans:

This error message occurs if the SQL Server 2000 authentication mode is set to Windows Authentication mode. The Microsoft SQL Server 2000 driver for JDBC does not support connecting by using Windows NT authentication. You must set the authentication mode of your SQL Server to Mixed mode, which permits both Windows Authentication and SQL Server Authentication.

Error-2

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]This version of the JDBC driver only supports Microsoft SQL Server 2000. You can either upgrade to SQL Server 2000 or possibly locate another version of the driver.

Ans:

This error message occurs you try to connect to a SQL Server version earlier than SQL Server 2000. The Microsoft SQL Server 2000 driver for JDBC supports connectivity only with SQL Server 2000.

Java Runtime Environment Requirements

Starting with the Microsoft JDBC Driver 4.2 for SQL Server, Sun Java SE Development Kit (JDK) 8.0 and Java Runtime Environment (JRE) 8.0 are supported. Support for Java Database Connectivity (JDBC) Spec API has been extended to include the JDBC 4.1 and 4.2 API.
Starting with the Microsoft JDBC Driver 4.1 for SQL Server, Sun Java SE Development Kit (JDK) 7.0 and Java Runtime Environment (JRE) 7.0 are supported.
Starting with the Microsoft JDBC Driver 4.0 for SQL Server, the JDBC driver support for Java Database Connectivity (JDBC) Spec API has been extended to include the JDBC 4.0 API. The JDBC 4.0 API was introduced as part of the Sun Java SE Development Kit (JDK) 6.0 and Java Runtime Environment (JRE) 6.0. JDBC 4.0 is a superset of the JDBC 3.0 API.

For more: System Requirements for the JDBC Driver

To Set the CLASSPATH Variable

The Microsoft SQL Server 2000 driver for JDBC .jar files must be listed in your CLASSPATH variable. The CLASSPATH variable is the search string that Java Virtual Machine (JVM) uses to locate the JDBC drivers on your computer. If the drivers are not listed in your CLASSPATH variable, you receive the following error message when you try to load the driver:

java.lang.ClassNotFoundException: com/microsoft/jdbc/sqlserver/SQLServerDriver

Set your system CLASSPATH variable to include the following entries:

\Your installation path\Lib\Msbase.jar

\Your installation path\Lib\Msutil.jar

\Your installationpath\Lib\Mssqlserver.jar

This is an example of a configured CLASSPATH variable:

CLASSPATH=.;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver.jar

To Register the Driver

Registering the driver instructs JDBC Driver Manager which driver to load. When you load a driver by using the class.forName function, you must specify the name of the driver. This is the driver name for Microsoft SQL Server 2000 Driver for JDBC:

com.microsoft.jdbc.sqlserver.SQLServerDriver

The following sample code demonstrates how to register the driver:

Driver d = (Driver)Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();

To Pass the Connection URL

You must pass your database connection information in the form of a connection URL. This is a template URL for Microsoft SQL Server 2000 Driver for JDBC. Substitute the values for your database:

jdbc:microsoft:sqlserver://servername:1433

The following sample code demonstrates how to specify a connection URL:

con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433", "userName", "password");

Choosing the Right JAR file

The Microsoft JDBC Drivers 6.0 and 4.2 for SQL Server provide sqljdbc.jar, sqljdbc4.jar, sqljdbc41, and sqljdbc42.jar class library files to be used depending on your preferred Java Runtime Environment (JRE) settings.
The Microsoft JDBC Driver 4.1 for SQL Server provides sqljdbc.jar, sqljdbc4.jar, and sqljdbc41.jar class library files to be used depending on your preferred Java Runtime Environment (JRE) settings.
The Microsoft JDBC Driver for SQL Server 4.0 provides sqljdbc.jar and sqljdbc4.jar class library files to be used depending on your preferred Java Runtime Environment (JRE) settings.

Making a Simple Connection to a Database

Using the sqljdbc.jar class library, applications must first register the driver as follows:

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

When the driver is loaded, you can establish a connection by using a connection URL and the getConnection method of the DriverManager class:

String connectionUrl = "jdbc:sqlserver://localhost:1433;" +  
   "databaseName=AdventureWorks;user=MyUserName;password=*****;";  
Connection con = DriverManager.getConnection(connectionUrl);

In the JDBC API 4.0, the DriverManager.getConnection method is enhanced to load JDBC drivers automatically. Therefore, applications do not need to call the Class.forName method to register or load the driver when using the sqljdbc4.jar, sqljdbc41.jar, or sqljdbc42.jar class library. When the getConnection method of the DriverManager class is called, an appropriate driver is located from the set of registered JDBC drivers. sqljdbc4.jar, sqljdbc41.jar, or sqljdbc42.jar file includes "META-INF/services/java.sql.Driver" file, which contains the com.microsoft.sqlserver.jdbc.SQLServerDriver as a registered driver. The existing applications, which currently load the drivers by using the Class.forName method, will continue to work without modification.

Resource Link:

All data's are taken from following-

score 1 · Answer 2 · answered Oct 07 '16 at 17:49

The CLASSPATH variable is the search string that Java Virtual Machine (JVM) uses to locate the JDBC drivers on your computer. If the drivers are not listed in your CLASSPATH variable, you receive the following error message when you try to load the driver: java.lang.ClassNotFoundException: com/microsoft/jdbc/sqlserver/SQLServerDriver

Check your class path and let me know if that was the issue.

score 1 · Answer 3 · answered Oct 07 '16 at 18:39

You dont need both jTDS and JDBC in your classpath. Any one is required. You'll most likley need only sqljdbc.jar. Be sure to place it in a psychical location like lib within WEB-INF directory of your project instead of adding it into your classpath via IDE. Reset your service, and trying again. It should fix it self.

Jar file can be found here: www.java2s.com/Code/JarDownload/sqlserverjdbc/sqlserverjdbc.jar.zip