Apache / Tomcat – Microsoft SQL Server – Sample Database Query


As part of my attempt to work with my brother to dig a bit deeper into a Java based application running on Tomcat, I chose to write a simple HelloDB application in Java-JSP-JSTL.


Microsoft SQL Server JDBC Driver

Download Location

The latest Microsoft SQL Server JDBC Driver is v4.0.  And, the URL is  http://www.microsoft.com/en-us/download/details.aspx?id=11774


Extract files

We need a couple of Jar files from the compressed (*.tar.gz) file.  On Linux we will use tar to extract the needed jar files.


tar -zxvf <compressed-file>  *.jar


tar -zxvf /tmp/sqljdbc_4.0.2206.100_enu.tar.gz *.jar




Where is $CATALINA_HOME/lib

We installed Apache Tomcat version 6 using yum, and our install location is /usr/share/tomcat6.

Underneath /usr/share/tomcat there is a lib folder; which is actually a symbolic link to /usr/share/java/tomcat6.


usr-share-tomcat6 folder listing



Copy JDBC Jar files into $CATALINA_HOME/lib

We need to copy sqljdbc4.jar unto the lib folder.

Copy Files


sudo cp <jar-files> <tomcat>/lib


sudo cp sqljdbc_4.0/enu/*.jar /usr/share/tomcat6/lib






Download Location

Download javax.servlet.jsp.jstl-1.2.1.jar from http://search.maven.org/#browse|-1002239620


Code – WAR File

File Contents

Here are the regular files that we will be including in our Jar file:

  • WEB-INF/web.xml
  • WEB-INF/lib
  • WEB-INF/classes
  • META-INF/context.xml
  • html files
  • image files
  • jsp files
  • 3rd Party Jar files – jstl files


Here are the files that we will not be including:

  • Notice that you do not to indicate the manifest file, as that file is auto created


WAR File – Folder Structure


Folder Folder/Sub-file What? Sample
WEB-INF Web Information
web.xml Web Configuration File
 lib Jar files 3rd party and application jar files (jstl*.jar)
 classes Compiled java files
META-INF Meta Information about the Jar file



Source Code Location

On our system, we will place our source files in /home/dadeniji/development/apache.org/tomcat/MSAdventureWorksDWDB



Create empty file structure

Create empty file structure

if [[!-e WEB-INF ]]; then
   sudo mkdir -p WEB-INF

if [[!-e WEB-INF/lib ]]; then
   sudo mkdir -p WEB-INF/lib

if [[!-e WEB-INF/classes ]]; then
   sudo mkdir -p WEB-INF/classes

if [[!-e META-INF ]]; then
   sudo mkdir -p META-INF




Create WEB-INF/web.xml

Here is the contents of our web xml file:

<?xml version="1.0" encoding="ISO-8859-1"?>

<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xsi="http://www.w3.org/2001/XMLSchema-instance" schemalocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">

        <description>DB  Connection</description>



There are a couple of areas to note:

  • Our app version is targeting Servlet/JSP API version 2.5
  • For our DB, we are defining a resource self-named msadworks and its type is javax.sql.DataSource




Create META-INF/context.xml

<xml version="1.0" encoding="UTF-8"?>

There are a couple of areas to note:

  • We are further defining our database resource, msadworks
  • Its type is javax.sql.DataSource
  • The username & password that is predefined on the DB are noted
  • The Driver Class Name for MS SQL Server JDBC 4.0 is indicated as com.microsoft.sqlserver.jdbc.SQLServerDriver
  • The Url is “jdbc:sqlserver://<sqlmachinename>;databaseName=dbName
  • For the Connection factor we are hard-coding org.apache.commons.dbcp.BasicDataSourceFactory
  • And, logging validation errors via set setting logValidationErrors to true


Copy jstl jar files into WEB-INF lib

Here are the steps to copy the jstl files under WEB-INF/lib:


if [[ !-e WEB-INF/lib ]]; then
   sudo mkdir -p WEB-INF/lib

sudo cp --update /tmp/jstl/javax.servlet.jsp.jstl-1.2.1.jar  WEB-INF/lib

sudo cp --update /tmp/jstl/javax.servlet.jsp.jstl-api-1.2.1.jar  WEB-INF/lib

Source Code :- jspSimple.jsp

Here is a sample source code that connects to the hard coded sql server and relies on JDBC APIs to query and return the recordset from a table.

 <%@ page import="java.sql.*" %>
 <% String connectionUrl = "jdbc:sqlserver://hrdb:1433;databasename=AdventureWorksDW2008R2;user=AdWorksDW;password=sleeper"; String strSQLStatement = "select top 3 FirstName, LastName, EmailAddress from dbo.DimCustomer"; Connection con = null; Statement statement = null; ResultSet resultset = null; Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); con = DriverManager.getConnection(connectionUrl); statement = con.createStatement() ; resultset = statement.executeQuery(strSQLStatement); %>
<th><b>First Name</b></th>

<th><b>Last Name </b></th>

<td><b>Email Address</b> </th>


<% while (resultset.next()) { %>


<td><%= resultset.getString("FirstName") %></td>

<td><%= resultset.getString("LastName") %></td>

<td><%= resultset.getString("EmailAddress") %></td>


<% } %> 





Source Code :- explicit.jsp

The code below relies on the Context (part of javax.naming.) and DataSource (part of javax.sql).

Obviously, javax.naming.Context allows us to hide the server name and authentication credentials in xml files outside of the Source Code.

<%@ page contentType="text/html;charset=UTF-8" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<TITLE>JSP example</TITLE>
<h1>Hello,test JNDI ! </h1>

 <% Context ctx = new InitialContext(); Context envctx = (Context) ctx.lookup("java:comp/env"); DataSource ds = (DataSource) envctx.lookup("msadworks"); //DataSource ds = (DataSource) ctx.lookup("msadworks"); Connection conn=ds.getConnection(); Statement st=conn.createStatement(); String sql="select top 3 FirstName, LastName, EmailAddress" + " from dbo.DimCustomer"; ResultSet rs=st.executeQuery(sql); while(rs.next()) { %>
    FirstName:<%=rs.getString(1) %>
    LastName:<%=rs.getString(2) %>
    EmailAddress:<%=rs.getString(3) %>


 <% } %>

 <% rs.close(); st.close(); conn.close(); %>


Source Code :- queryDB.jsp

The code below uses JSTL tags and allows us closer to HTML/Code Fusion type programming.

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %>

<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn" %>

<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>

<sql:query var="rs" dataSource="msadworks" >
   select top 3 FirstName, LastName, EmailAddress 
   from dbo.DimCustomer


 <title>DB Test</title>



<table border="1" align="center" valign="center">



 <c:forEach var="rs" items="${rs.rows}">
<td><c:out value="${rs.FirstName}"/>&amp;nbsp;</td>

<td><c:out value="${rs.LastName}"/>&amp;nbsp;</td>

<td><c:out value="${rs.EmailAddress}"/>&amp;nbsp;</td>








Create Jar file

Here are the steps to create a jar file


jar cvf &lt;war-file&gt; *.jsp *.html images WEB-INF META-INF


jar cvf MSAdventureWorksDWDB.war *.jsp *.html images WEB-INF META-INF

View Jar file Contents

View Jar file contents


jar tf &lt;jar-file&gt;


jar tf MSAdventureWorksDWDB.war


jar file listing




Deploy – Manual

Manually deploy jar file by copying the war file into your webapps folder

There is a very noteworthy coverage of manually deploying WAR file available @



#un-deploy war
if [[ -f "/usr/share/tomcat6/webapps/MSAdventureWorksDWDB.war" ]]; then
 echo "Removing file /usr/share/tomcat6/webapps/MSAdventureWorksDWDB.war ..."
 sudo rm "/usr/share/tomcat6/webapps/MSAdventureWorksDWDB.war"
 echo "Removed file /usr/share/tomcat6/webapps/MSAdventureWorksDWDB.war"

#deploy war
echo "Deploying file /usr/share/tomcat6/webapps/MSAdventureWorksDWDB.war ..."

sudo cp MSAdventureWorksDWDB.war /usr/share/tomcat6/webapps/MSAdventureWorksDWDB.war

echo "Deployed file /usr/share/tomcat6/webapps/MSAdventureWorksDWDB.war"



Deploy – Verification – CATALINA_HOME/conf/Catalina/localhost/<APP-NAME>.XML


File Name:

Review CATALINA_HOME/conf/Catalina/localhost and look for a configuration file (XML) bearing your Application Name.

In our case, our folder’s name is /usr/share/tomcat/conf/Catalina/localhost

folder usr-share-tomcat6-conf-catalina-localhost

And, the file we are looking for is MSAdventureWorksDWDB.xml


File Contents:






TroubleShooting – Log files

As Apache Tomcat is a server side architecture one needs to pay attention to its log files to see how things are going.

On Apache Tomcat 6, the log files are placed in /var/log/tomcat6






Diagnostic Steps

Use lsof

Use lsof to determine where sqljdbc4.jar is being loaded from:

[dadeniji@adelia MSAdventureWorksDWDB]$ sudo lsof | grep sqljdbc
java 13196 tomcat mem REG 253,0 584207 522998 /usr/share/java/tomcat6/sqljdbc4.jar
java 13196 tomcat 25r REG 253,0 584207 522998 /usr/share/java/tomcat6/sqljdbc4.jar
[dadeniji@adelia MSAdventureWorksDWDB]$


Stolen Code from dinesh

Here is a piece of code stolen from Iscocra Consulting @ http://isocra.com/2007/10/jndi-problems-with-tomcat-5515/

<%@ page contentType="text/html;charset=UTF-8" %> 
<%@ page import="java.sql.*" %> 
<%@ page import="javax.sql.*" %> 
<%@ page import="javax.naming.*" %> 


 Context initContext = new InitialContext();
 out.println("1. Got the initial context"+ "\n 
 String envContextName = "java:/comp/env";
 Context envContext = (Context)initContext.lookup(envContextName);
 out.println("2. Got the context: "+ envContextName + "\n 
 String jndiName = "msadworks";
 DataSource ds = (DataSource)envContext.lookup(jndiName);
 out.println("3. Got the datasource: "+ds.getClass().getName()
                  +" for context "+jndiName + "\n 
 Connection conn = ds.getConnection();
 out.println("4. Got the connection: "+conn);



Run-Time Error Messages

Wrong Folder Group

SEVERE: Error starting static Resources
java.lang.IllegalArgumentException: Document base /webapps/MSAdventureWorksDWDB does not exist or is not a readable directory
at org.apache.naming.resources.FileDirContext.setDocBase(FileDirContext.java:142)



References – Name is not bound in this context

18:     //Context envctx =  (Context) ctx.lookup("java:comp/env"); 
19:     //DataSource ds =  (DataSource) envctx.lookup("jdbc/MSAdventureWorksDWDB");  
21:     DataSource ds = (DataSource) ctx.lookup("jdbc/msadworks");
23:     Connection  conn=ds.getConnection();   
24:     Statement  st=conn.createStatement();

root cause

   javax.servlet.ServletException: javax.naming.NameNotFoundException: 
   Name jdbc is not bound in this Context

Does not work:

//Context envctx = (Context) ctx.lookup("java:comp/env");
//DataSource ds = (DataSource) envctx.lookup("jdbc/msadworks");

DataSource ds = (DataSource) ctx.lookup("msadworks");


Context envctx = (Context) ctx.lookup("java:comp/env");
DataSource ds = (DataSource) envctx.lookup("jdbc/msadworks");

//DataSource ds = (DataSource) ctx.lookup("msadworks");




Actual SQL Database Connectivity Error – SQL Instance Connectivity


javax.servlet.ServletException: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'sa'. 

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'sa'. 

note The full stack trace of the root cause is available in the Apache Tomcat/6.0.24 logs.

Actual SQL Database Connectivity Error – SQL Instance \ Database Connectivity


com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "AdventureWorkDWs2008R2" requested by the login.

note The full stack trace of the root cause is available in the Apache Tomcat/6.0.24 logs.



Actual SQL Database Connectivity Error – SQL Instance Connectivity – Wrong User/Password

javax.servlet.ServletException: javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Login failed for user 'AdventureWorksDW_v2'. ClientConnectionId:cd487d05-422d-4dcd-9cdd-f26d236428dd)"

javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Login failed for user 'AdventureWorksDW_v2'.

In our case, we had the wrong password


Actual SQL Database Connectivity Error – SQL Instance \ Database Connectivity

In the /var/log/tomcat6/catalina.<YYYY-MM-DD>.log, you might see warnings about a missing dll sqljdbc_auth.dll.

The warning speaks about the aforementioned file missing from the java.library.path.

Apr 24, 2014 11:13:15 AM com.microsoft.sqlserver.jdbc.AuthenticationJNI 
WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path
Apr 24, 2014 11:13:15 AM org.apache.coyote.http11.Http11Protocol destroy

If your environment/OS is not Microsoft Windows, then you are OK.

It simply means you will not be able to use trusted authentication.


Actual SQL Database Connectivity Error – Cannot create JDBC Driver of class ” for connect URL ‘null’


org.apache.jasper.JasperException: javax.servlet.ServletException: 
javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'"

javax.servlet.ServletException: javax.servlet.jsp.JspException: Unable to get connection, DataSource invalid: "org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'"

javax.servlet.jsp.JspException: Unable to get connection, DataSource 
invalid: "org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC 
driver of class '' for connect URL 'null'"


The fix here could be so many things.  Here are some things to try out.

  • If you are manually modifying  files in your your WAR directory, you might run into trouble with proper creation\update of your xml config file that is housed in /usr/share/tomcat/conf/Catalina/localhost.When an application is properly deployed, the system does an excellent job maintaining this file.


I stumbled quite a bit with this simple Application.  And really, because I was a bit stuck with an unhelpful error message “Cannot create JDBC driver of class ” for connect URL ‘null'””, I dug and tried my best to have a clean room environment where I could really say this is the most straightforward way to fix that error.

But, it wasn’t to be.  And, so though my little app is working, I do not have comprehensive data to share as to what the best path to remedify is.

And, so you get to have Joe Nichols as my Listening song!



Listening to Joe Nichols – I wish that wasn’t all:-

  1. mp3lemon.org
    • Joe Nichols – I Wish that wasn’t all
      Demoted On:- 2020-December-10th
  2. YouTube
    • I wish that wasn’t all


References – JSTL – Blog


References – JSTL – Jar Files Repository

  1. maven.org


References – JSTL – QA

  1. jstl – info
  2. Getting jstl to run window tomcat and eclipse
  3. jstl-1-2-the-absolute-uri-http-java-sun-com-jstl-core-cannot-be-resolved


References – Java Development – Jar

References – Java Development – War

  1. Java War Files


References – Java Development – Directory Structure

  1. Tomcat Directory Structure

References – Java Development – Naming Convention

  1. What is web-inf used in a java web application
  2. Why Is Meta-Inf called Meta-Inf

References – Apache Tomcat – Installation

References – Apache Tomcat Installation on RedHat, CentOS, and Clones

  1. Apache Tomcat Installation on Linux (RHEL and clones)


References – Apache Tomcat – Deploy

  1. Portofino / Portofino 3 (older versions) / 3.1.x / Installation guide / Deploying on tomcat
  2. Apache Tomcat Deploy Procedures


References – Apache Tomcat – Deployment on MS Windows 2008/IIS v7

  1. Win2008-IIS7


References – JDBC

References – JDBC – Connection Pooling – Concurrency

  1. Configuring JDBC Pool High Concurrency

References – JDBC – Connection Pooling –

  1. java.boot.by – Interact with connection pools to obtain and release connections
  2. Unshareable and shareable connections


References – JDBC – Connection Pooling – Standalone

  1. Using tomcat jdbc connection pool as standalone instantiated bean


References – SQL Jar File Location

  1. Installing > Alternative installation methods > Installing using your own components
    Apache Tomcat installation and configuration


References – Sample Code

  1. Tomcat – Oracle – JDBC
    Demoted On:- 2020-December-10th
  2. Demoted On:- 2020-December-10th


References – Sample Code – JSTL

  1. metawerx – Accessing a JNDI DataSource with JSTL from JSP
  2. jsql SQL Demo
  3. JSTL to achieve paging
  4. Database Skill – jsp SQL Tags


References – QA – sql:setDataSource

  1. sql:setDataSource – “java.sql.SQLException: No suitable driver” Again 😦



References – Tomcat – Data Sources

  1. Using DataSources
  2. Configure Tomcat 6 Data Source Using SQL



References – Tomcat – Data Sources – External Context file

  1. Management Console in Tomcat > Deploying into Tomcat
  2. Cannot create jdbc driver for connect URL http://blogs.agilefaqs.com/2009/11/23/cannot-create-jdbc-driver-of-class-for-connect-url-null/

References – Tomcat – Data Sources – Q/A

  1. Cannot create jdbc driver of class for connect url null
  2. TOMCAT6.0 connection pool sqlserver (solve Cannot create JDBC driver of class for connect URL’null



References – Tomcat – JNDI Resources – Q/A

  1. http://stackoverflow.com/questions/6654132/how-to-set-up-a-resource-in-tomcat-7-so-that-i-dont-need-to-use-java-comp-e
  2. Resource Name


References – Vendor – jasig.org – uPortal 4.0

  1. uPortal 4.0 – MS SQL Server and MS JDBC Driver (recommended)


References – Error – Error Message – “WARNING: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path”

  1. No sqljdbc auth in Java Library Path


References – Error – Error Message – “Programmer’s Town » Java » tomcat 6 and No suitable driver found”


References – Error – “Ubuntu Tomcat7 java.lang.ClassNotFoundException: org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory”


  1. Ubuntu Tomcat7 java.lang.ClassNotFoundException: org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory

    • The critical element is the “factory” declaration, which overrides the built-in default.On our production machines, the resource is defined in the GlobalNamingResources element of the server.xml file. Specifying the factory is only needed on the Ubuntu systems.
    • Sameeh Harfoush [ Link ]
      • I had the same problem on CentOS. I got around this by downloading a fresh copy of tomcat from site and uploaded tomcat-dbcp.jar to my online server lib, restart server 🙂

References – Problems – JNDI Lookup problem

  1. JNDI problems with tomcat 5.5


References – Linux – Command – File Management – Mkdir

  1. How to mkdir only if a dir does not already exist
  2. How to check if a directory exists in Shell Script
  3. Test Constructs

References – Linux – Command – File Management – Folder Copy

  1. Folder Copy Command


4 thoughts on “Apache / Tomcat – Microsoft SQL Server – Sample Database Query

  1. I thoroughly enjoyed reading this … Thanks Dani.
    How about another one for connecting to other datasources besides MS SQL?

    • Certainly will so. Is there a particular database (Oracle, DB/2, MySQL, PostgresSQL, MongoDB) that we should address initially.

      Thanks for posting a comment!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s