This week's book giveaway is in the OCPJP forum. We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line! See this thread for details.
I am working with an older MS-SQLserver, it is 2000. This is a production server and the SQLversion cannot be upgraded.
I cannot go to JTDS 1.26 because the server is too old. I cannot use Microsoft JDBC because it has been removed from circulation and does not support Java 6. I am using Tomcat 7.
I have set up a data connection for Tomcat using JTDS 1.25 in both Netbeans and Eclipse. I can see the server and the schema in the Eclipse data server panel and the Netbeans server panel. My problem is that the connection will only show the database tables under the .dbo account. I have confirmed that the user I am connecting with has full permissions on all the tables. The user account works as a login. With that account I can see the schemes but when I get to the table folder in the scheme there are no tables. I know the driver works because I have logged on to the server directly from a servlet and run queries against multiple data tables. I know that logging on from servlets is BAD so I won't build a project like that. When the data connection is used for queries the same user login that I forced in the servlets is denied in the data connection.
The databases must be used for authentication, too.
How do I get a data connection working with the JTDS driver so the connection accesses the other user accounts on the SQLserver instead of it only showing/using tables with the .dbo account?
If the JTDS will not work as a Tomcat data connection is there another way to make a data connection and use data pooling?
Took six seven months but I finally figured out a solution. Here is my environment:
Desktop: Virtual Windows XP 32 bit
Tomcat7 on Ubuntu Server 10.4
database: Microsoft SQL server 2000 , no longer supported by MS
database server: Windows 2000, no longer supported by MS
additional jars: jstl.jar, standard.jar
The problem was I had an older servlet application generously posted on the web by Marty Hall and Larry Brown with source code at coreservlets.com . They had built a Northwind servelt that accessed the Northwind database that comes with Microsoft Access and queried the database. The same Northwind database comes on all installations of Microsoft SQLserver. By putting my sqlserver's url into this old servlet and by adding jtds-1.2.5.jar to the project I was able to query my tables.
Then I wanted to modernize, take the scriplets out of the JSP, hide the user login that in the old code was part of the html form submitting the query and could be seen by the user if they looked at the pagesource. And I could never get the connection to the sqlserver to work. The new jtds compiled fine and it is a jdbc4 driver but it never worked.
Another contributing factor was the IDE's. I started in Netbeans because I was using a textbook with source code and that is what all the projects were built in. Then I moved to Eclipse because there were IDE issues in Netbeans that Eclipse seemed to work with. Eventually I moved the project into a Tomcat source code layout ( thank-you Bear! ) and learned Ant. With some help from the ANT forums and some experience from the Cattle Drive servlet projects I was able to find the problems more quickly.
The .dbo issue I mentioned in the original post was probably a red herring and the real issue all along was not being able to make the connections.
Much of the code below is adapted from projects in the Murachs Servlets and JSP book 2nd edition. I recommend it and appreciate that the code for full projects is part of the book. Any errors or bad practices are mine.
Here is what I found. Even though this is a jdbc 4 driver and the Class.forName(driver) doesn't have to be used according to all the examples of servlets with this generation of driver that I have found, I have to use it to connect to my sqlserver. Doesn't matter what generation of driver, it has to be used. Maybe because the sqlserver is so old? I don't know. Because I have to use the Class.forName(driver) I have to have extra catch statements to handle it . Once these issues were resolved I was able to make a very simple search program that runs. The code for my servlet and JSP is below. I hope this helps anyone else out there that has dealt with these issues.
Below is the servlet. It is not the code for Northwind but for a similar customer table.