On Sun, Jul 6, 2008 at 12:59 AM, Jason Cipriani <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
