tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jason Cipriani" <jason.cipri...@gmail.com>
Subject Re: [6.0.16] JNDI, MSSQL, context.xml, server.xml, web.xml, common/lib, DataSources, exceptions, configuration hell, vague tutorials, hidden documentation, lions, tigers, bears, oh my...
Date Sun, 06 Jul 2008 06:39:49 GMT
On Sun, Jul 6, 2008 at 12:59 AM, Jason Cipriani
<jason.cipriani@gmail.com> wrote:
> I am now in hour 6 of attempting to configure a single JNDI DataSouce
> resouce to connect to an MSSQL database, with Tomcat 6.0.16 (jre
> 1.6.0_04, windows xp sp3).

I have finally gotten MS SQL 2005 Express working with Tomcat 6. If
anybody finds this some day, here is what I did. If anybody reading
this finds any mistakes, please correct them -- I apologize in advance
but I am not very familiar with Tomcat or MSSQL. I did manage to get
everything working, though. It's pretty straightforward even if it
does take the better part of a day to discover.

== STEP 1 ==

Get MS SQL JDBC driver:

  http://msdn.microsoft.com/en-us/data/aa937724.aspx

To install it just run the self-extracting EXE. The only thing you
need out of that is the .jar file (sqljdbc.jar). Take sqljdbc.jar and
place it in $CATALINA_HOME/lib. Incorrect information that you will
find on the internet that you want to watch out for:

  * Do not place this in your servlet's WEB-INF/lib. It must go in
$CATALINA_HOME/lib.
  * The "common/lib" directory does not exist with Tomcat 6.0. Use
"lib" instead.
  * You do not need to modify CLASSPATH or any other environment
variables if you do it this way.

== STEP 2 ==

Enable TCP connections in MS SQL server. This is very important.
Otherwise you will get "connection refused" errors when attempting to
connect. It is not enabled by default. The instructions for doing this
are here:

  http://kb.adobe.com/selfservice/viewContent.do?externalId=kb400255

   1. Launch the SQL Server Configuration Manager.
   2. Expand "Server Network Configuration".
   3. Expand Protocols for "SQLEXPRESS".
   4. Enable Named Pipes.
   5. Enable TCP.
   6. Select TCP/IP in the right pane.
   7. Select Properties > IP Addresses.
   8. Make "Enabled" for all applicable IPs = "Yes".
   9. Note the TCP port value or add if one does not exist for all
applicable IPs.
  10. Restart SQL Express.
  11. Configure a SQL Server JDBC data source in the ColdFusion
Administrator to listen on the port number from step 9 above.
  12. Complete the data source configuration connectivity and
authentication information.
  13. Saving the data source should verify without error

You can specify any port number you want. Note that you can specify
the port number in the "IPAll" section instead of the individual IP
address sections, and it will apply to every single one of them. To
restart the MSSQL server, do it through the windows administration
Services control panel.

== STEP 3 ==

Configure JNDI resource in Tomcat. Contrary to what the internet says,
with Tomcat 6 you do *NOT* need to modify Tomcat's server.xml file.
You can simply create a file named context.xml right in your servlet's
META-INF directory. If you do this I don't think you have precise
control over which Tomcat hosts the resource applies to -- I don't
know how to set that up. Also I'm not sure if this context.xml file
has to be well-formed XML or not, but making it a real XML document
worked for me. Here is an example context.xml:

<?xml version="1.0" encoding="UTF-8"?>
<Context path="/TestServlet" docBase="TestServlet" debug="5"
reloadable="true" crossContext="true">
	<Resource
   name="jdbc/mssql"
   auth="Container"
   type="javax.sql.DataSource"
   maxActive="100"
   maxIdle="30"
   maxWait="10000"
   username="username"
   password="password"
   driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
   url="jdbc:sqlserver://SERVER\INSTANCE:1433;databaseName=testdb"/>
</Context>

Note that you do not need to have a separate ResourceParams section.
You can do everything as attributes in the Resource tag. This is a
newer and preferred way of doing things. Replacements you will have to
make:

  TestServlet: replace with your servlet name
  username/password
  SERVER: replace with server name
  INSTANCE: replace with instance name
  1433: replace with port number you set up in step 2
  testdb: replace with database name
  "jdbc/mssql": replace with any resource name you want, although it
may have to start with "jdbc/", I don't know.

You may also want to tweak some of the other parameters too. I don't
actually know what any of the other stuff in there does, but I'm sure
all the server parameters are documented (you can find it all in the
JDBC driver archive you unzipped), as well as the other attribute tags
for Context and Resource. The SERVER\INSTANCE is the same thing that
shows up when you connect to the database using the SQL Server
Management Studio (such as "MYCOMPUTER\SQLEXPRESS"). If you are not
using named servers and instances, you can also just specify a host
name or IP address in the jdbc url:

   jdbc:sqlserver://localhost:1433;databaseName=testdb

This page contains some very brief, straightforward instructions and an example:

  http://www.fornewbie.com/java/step_step_tutorial_create_jndi_jdbc_datasource_tomcat.html

Things to watch out for:

  * The driver name is "com.microsoft.sqlserver.jdbc.SQLServerDriver"!
Some examples on the internet have the "sqlserver" and "jdbc" part
reversed! (That was a big problem in my last attempt -- it is NOT
com.microsoft.jdbc.sqlserver.SQLServerDriver).
  * I don't think it causes any problems but you do NOT need to use
"jdbc:microsoft:sqlserver", leaving out the "microsoft" works just
fine.
  * You do not need to touch your web.xml file at all. It is
sufficient to set up everything in context.xml.
  * Some instructions talk about putting some "WebApplication.xml"
file in the webapps root. I don't know what this is all about, but you
do not need to do that. Using META-INF/context.xml will work just
fine.
  * Some instructions reference the driver "com.inet.tds.TdsDriver"
and inetdae7 -- I don't know what this is but it's not for the JDBC
driver that you downloaded in step 1.
  * Some instructions have a parameter named "driverName". Don't worry
about this. Use "url".
  * Some instructions have a parameter named "factory", set to various
values. You can leave this out.

Note that because context.xml exists in your servlets META-INF
directory, it is also very convenient to edit the XML file if you are
using Eclipse. :-)

== STEP 4 ==

Test it out. Use the JSP page in the www.fornewbie.com link I just
gave above. One line to pay attention to is:

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

You'll have to change this to match the resource name you defined in
context.xml. So with the above example, where the name is
"jdbc/mssql", you'd change that line to:

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

I don't know what the "java:/comp/env/" is all about but I think it
has to be there. Also, you're also going to want to modify the SQL
query (and the results that it prints) to be something more
appropriate for your own database.

Some common errors you may receive:

Error:
  javax.naming.NameNotFoundException: Name <whatever> is not bound in
this Context
Possible Cause:
  The string you passed to ctx.lookup didn't match the resource name
in context.xml.

Error:
  org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create
PoolableConnectionFactory (The TCP/IP connection to the host has
failed. java.net.ConnectException: Connection refused: connect)
Possible Cause:
  You specified an incorrect port or host name.
  You didn't enable TCP connections on the appropriate IP addresses in
MSSQL express (or you did but forgot to restart the server).
  The connection is blocked by a firewall or something.

Error:
  org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot load JDBC
driver class 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
Possible Cause:
  You misspelled the driver class name.
  You did not place the JAR file from step 1 in $CATALINA_HOME/lib.
  You put the JAR in the correct location but did not restart Tomcat.

Error:
  Cannot create JDBC driver of class '' for connect URL 'null', cause:
No suitable driver
Possible Cause:
  You misspelled one of the tags in context.xml.
  You used the old ResourceParams tags (which, from what I understand,
isn't even supported in Tomcat 6 any more but I may be wrong) rather
than using attributes of the Resource tag as in the above examples.



This is all I can think of. Hopefully it saves somebody a day of
headache some day.

Jason


> I'll skip the gory details, the point I am at now:
>
>  1. I have META-INF/context.xml with the resource parameters (I've
> included this file at the end of this message).
>  2. I have Microsoft's MSSQL driver JAR, sqljdbc.jar, in WEB-INF/lib
> but I'm not sure where the correct location for it is.
>  3. I am receiving the following exception when attempting to open a
> connection:
>
> org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC
> driver of class '' for connect URL 'null'
>
> I have Googled a-plenty for this error and found many, many posts
> about it. Most of the "solutions" were not correct. The solutions that
> did work for the original posters have not been working for me. I am
> not sure what I am doing wrong.
>
> Here is a bit of the Java code I am using to test this (in a JSP page):
>
>   InitialContext ctx = new InitialContext();
>   DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/mssql");
>   Connection conn = ds.getConnection();
>   PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM usertest");
>
> It is ds.getConnection() that is throwing the exception. I know that
> my context.xml file is being loaded by Tomcat because ctx.lookup()
> succeeds (and fails if I modify context.xml to define a different
> resource name, which I did as a sanity check).
>
> One question I have is, where should the driver JAR go? I have seen
> multiple references to placing it in Tomcat's "common/lib" directory.
> I am not sure where that is. There is no directory named "common" in
> my Tomcat installation.
>
> Also, does documentation for this (setting up DataSources in general,
> and MSSQL specifically) exist? I mean good, concise documentation, not
> chapters upon chapters of generic JNDI setup information that, even
> when you've read through all of it, still leaves you hanging with
> questions like "great... so.... where do I specify the database host
> name?". Another example of frustrating documentation is the first
> Google result for "tomcat datasource tutorial" is this page:
>
>  http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html
>
> Which describes 5.5, not 6.0, and an MSSQL section is conspicuously absent.
>
> I'd really appreciate the help. I have some really aggressive
> deadlines coming up, and I've spent an unacceptable amount of time
> trying to do nothing more than configure a database connection, with
> nothing to show for it. It's getting pretty frustrating. I realize
> that this may be a question that is commonly asked by many people,
> based on what I've seen on the internet. I really have spent a lot of
> time searching for information before posting here, nothing seems to
> be working out.
>
> Here is my context.xml file, it is pieced together from various
> examples and information I've found on the internet, I have no idea if
> it's correct or not (apparently not, as it's not working). As far the
> actual values: I arbitrarily picked "url" instead of "driverName", but
> in many examples I saw "driverName" being used for the same thing --
> also, some examples have "jdbc:microsoft:sqlserver", others just have
> "jdbc:sqlserver":
>
> <?xml version="1.0" encoding="UTF-8"?>
> <Context path="/TestServlet" docBase="TestServlet" debug="5"
> reloadable="true" crossContext="true">
>        <Resource name="jdbc/mssql" auth="Container" type="javax.sql.DataSource"/>
>        <ResourceParams name="jdbc/mssql">
>                <parameter>
>                        <name>maxWait</name>
>                        <value>15000</value>
>                </parameter>
>                <parameter>
>                        <name>password</name>
>                        <value>*password*</value>
>                </parameter>
>                <parameter>
>                        <name>url</name>
>                        <value>jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=Cursor;DatabaseName=testdb</value>
>                </parameter>
>                <parameter>
>                        <name>driverClassName</name>
>                        <value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
>                </parameter>
>                <parameter>
>                        <name>maxIdle</name>
>                        <value>2</value>
>                </parameter>
>                <parameter>
>                        <name>username</name>
>                        <value>*username*</value>
>                </parameter>
>        </ResourceParams>
> </Context>
>
>
> Thanks a lot,
> Jason
>

---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org


Mime
View raw message