Apache / Tomcat – Microsoft SQL Server – Sample Database Query

Daniel Adeniji Apache, Technical, Tomcat

Introduction

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.

Syntax:

tar -zxvf <compressed-file>  *.jar

Sample:

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

 Output:

extractJarFiles

 

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

Syntax:

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

Sample:

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

 

 Output:

usr-share-tomcat6-lib-sql

 

JSTL

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
 context.xml

 

 

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

 

 

WEB-INF/web.xml

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">

    <resource-ref>
        <description>DB  Connection</description>
        <res-ref-name>msadworks</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    	<res-sharing-scope>Shareable</res-sharing-scope>
    </resource-ref>

</web-app>

 

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

 

 

META-INF/context.xml

Create META-INF/context.xml

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

<Context>

 <Resource
  name="msadworks"
  auth="Container"
  type="javax.sql.DataSource"
  username="ADWorksDW"
  password="sleeper"
  driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
  url="jdbc:sqlserver://hrdb;databaseName=AdventureWorksDW2008R2;"
  maxActive="1000"
  maxIdle="30"
  maxWait="10000"
  factory="org.apache.commons.dbcp.BasicDataSourceFactory" 
  logValidationErrors="true" 
 >
 </Resource>


</Context> 
 

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.

<html>
<head>jspSimple.jsp</head>
<body>
 <%@ 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);
 %>
 
 <table> 
 <tr>
 <th><b>First Name</b></th>
 <th><b>Last Name </b></th>
 <td><b>Email Address</b> </th>
 </tr>

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

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

</tr>

<% 
} 
%> 

</table>
</body> 
</html>

 

 

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.*" %> 
<HTML>
<HEAD>
<TITLE>JSP example</TITLE>
</HEAD>
<BODY>
 <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) %> 

  <br>

 <%
 }
 %> 

 <%
 rs.close(); 
 st.close(); 
 conn.close(); 
 %> 
</BODY>
</HTML> 



 

 

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

</sql:query>



 <head>
 <title>DB Test</title>
 </head>

 <body>

 <h2>Results</h2>


 <table border="1" align="center" valign="center">
 <th>Id</th>
 <th>Name</th>
 <th>Address</th>
 <c:forEach var="rs" items="${rs.rows}">
 <tr>
 <td><c:out value="${rs.FirstName}"/>&nbsp;</td>
 <td><c:out value="${rs.LastName}"/>&nbsp;</td>
 <td><c:out value="${rs.EmailAddress}"/>&nbsp;</td>
 </tr>
 </c:forEach>
 </table>
 

 </body>

</html>




 

 

 

Create Jar file

Here are the steps to create a jar file

Syntax:

jar cvf <war-file> *.jsp *.html images WEB-INF META-INF

 

Sample:

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

 

View Jar file Contents

View Jar file contents

Syntax:

jar tf <jar-file>

Sample:


jar tf MSAdventureWorksDWDB.war

Output:

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 @

http://www.manydesigns.com/en/portofino/portofino3/3_1_x/installation-guide/deploying-on-tomcat/#TOC-Manually-deploying-and-configuring

 


#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"
fi


#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:

catalina--localhost--app-config--contents

 

 

 

TroubleShooting

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

 

folder-list--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 <br/>");
 String envContextName = "java:/comp/env";
 Context envContext = (Context)initContext.lookup(envContextName);
 out.println("2. Got the context: "+ envContextName + "\n <br/>");
 String jndiName = "msadworks";
 DataSource ds = (DataSource)envContext.lookup(jndiName);
 out.println("3. Got the datasource: "+ds.getClass().getName()
                  +" for context "+jndiName + "\n <br/>");
 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)

 

webapp_wrong_group_root_shouldBe_tomcat

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");  
20: 
21:     DataSource ds = (DataSource) ctx.lookup("jdbc/msadworks");
22: 
23:     Connection  conn=ds.getConnection();   
24:     Statement  st=conn.createStatement();


Stacktrace:
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");

 
Works:


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



Stacktrace:
	org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:510)

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


Stacktrace:
	org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:510)
	org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:401)
	org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
	org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
	javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

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.

Conclusion

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

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

http://mp3lemon.org/song/74220/Joe_Nichols_-_04_-_I_Wish_That_Wasnt_All

References

References – JSTL – Blog

 

References – JSTL – Jar Files Repository

 

References – JSTL – QA

 

References – Java Development – Jar

References – Java Development – War

 

References – Java Development – Directory Structure

References – Java Development – Naming Convention

References – Apache Tomcat – Installation

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

 

References – Apache Tomcat – Deploy

 

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

 

References – JDBC

References – JDBC – Connection Pooling – Concurrency

References – JDBC – Connection Pooling –

 

References – JDBC – Connection Pooling – Standalone

 

References – SQL Jar File Location

 

References – Sample Code

 

References – Sample Code – JSTL

 

References – QA – sql:setDataSource

 

 

References – Tomcat – Data Sources

 

 

References – Tomcat – Data Sources – External Context file

References – Tomcat – Data Sources – Q/A

 

 

References – Tomcat – JNDI Resources – Q/A

 

References – Vendor – jasig.org – uPortal 4.0

 

References – Error – Error Message – “WARNING: Failed to load the sqljdbc_auth.dll cause : 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”

 

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
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

 

References – Linux – Command – File Management – Mkdir

References – Linux – Command – File Management – Folder Copy

 

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?

    Reply

Leave a Reply

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

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Cancel

Connecting to %s