tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Johnny Kewl" <j...@kewlstuff.co.za>
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 16:48:04 GMT
Thanks Jason...

Did you know that its sitting right there inside you tomcats help files...
http://localhost:8080/docs/jndi-datasource-examples-howto.html
;)

When you type... http://localhost:8080 it will give you the intro page, 
worth a few clicks ;)

-------------------


----- Original Message ----- 
From: "Jason Cipriani" <jason.cipriani@gmail.com>
To: <users@tomcat.apache.org>
Sent: Sunday, July 06, 2008 8:39 AM
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...


> 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
>
---------------------------------------------------------------------------
HARBOR : http://www.kewlstuff.co.za/index.htm
The most powerful application server on earth.
The only real POJO Application Server.
See it in Action : http://www.kewlstuff.co.za/cd_tut_swf/whatisejb1.htm
--------------------------------------------------------------------------- 


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