Mark
asked on
need step by step howto connecting jsp to SQL server
I have tomcat 6.0.14 running on Linux 2.4.49. I've just installed tomcat and it appears to run fine. I've created a few jsp programs to test, but now I need to get down to business. I need to connect to SQL Server 2005 running on a Windows Small Business Server 2003. I need step-by-step help on doing this, perhaps starting with what I need to set up on the Server (DSN, ports, ...?). I've done a bit of SQL Server configuration, but not much. I'll be glad to break this up into mulitple questions as I go. There are lots of things on EE which I'll browser while waiting, but all the answers appear very piecemeal so far.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nice tutorial, but it's for MySQL, not Windows SQL Server.
> Nice tutorial, but it's for MySQL, not Windows SQL Server.
How would java code be different?
How would java code be different?
The driver and database URL would be completely different. The rest of the Java code would be the same. Which is why I said that it's for MySQL, not SQL Server. It also includes steps for installing MySQL locally on the same machine as the Tomcat server, which again would not be possible for the configuration asked about here.
It also does not include any of the steps necessary for accessing a SQL Server db from a Unix machine, which has a number of configuration steps independent of the Java code.
It also does not include any of the steps necessary for accessing a SQL Server db from a Unix machine, which has a number of configuration steps independent of the Java code.
Do you see that the asker asked the question for jsp *not* for PHP?
>Do you see that the asker asked the question for jsp *not* for PHP?
Absolutely. The link I provided goes through, step by step, the process of setting up a Windows db connection from Linux to Microsoft SQL Server. The next question is whether the asker knows enough Java to make a database connection from their code, which I hoped would become clear as they responded to the first set of information.
The original question emphasizes the initial configuration problems in setting up the connection between the Linux box and the Windows box with SQL Server.
Absolutely. The link I provided goes through, step by step, the process of setting up a Windows db connection from Linux to Microsoft SQL Server. The next question is whether the asker knows enough Java to make a database connection from their code, which I hoped would become clear as they responded to the first set of information.
The original question emphasizes the initial configuration problems in setting up the connection between the Linux box and the Windows box with SQL Server.
ASKER
mrcoffee365 and Ajay-Singh, you are both right in your counter-critiques of each other's response to my original question. I am looking for jsp, not php and I am using SQL server, not mySql. But, I will go through each of your provided links and see what I can synthesis. Here's what I've done so far:
I've downloaded a "unix" jdbc driver from Microsoft. I got to it via the help on SQL Server Manager Studio 2005. The disclaimer gives some specifis OSes it supports including Red Hat, so I'm hoping that counts for slackware as well. If anyone can suggest a better driver, or knows that this one won't work, please let me know.
I'm going through the docs that came with the driver. My first order of business is the connection string URL. the basic format is:
jdbc:sqlserver://serverNam
So far I've contructed:
jdbc:sqlserver://server;tr
Lotta issues right off the bat. The sql server host is not local, there there has to be an http:// and IP or domain in there somewhere. How does that go?
Also, I'm familiar with the concept of "instance" from Oracle, but I'm not sure it's the same on SQL Server. In my Access connect string, the server is server\traverse105 and I connect to the OSH2007 database. So should my string be: jdbc:sqlserver://server\tr
Then there's this note in the MS documentation:
"If both a portNumber and instanceName are used, the portNumber will take precedence and the instanceName will be ignored."
That makes no sense to me. What does that mean? A different port is associated with each instance? I must not understand what instance means in this context.
Help!
muddlin' through (too bad there isn't a step-by-step for my configuration. It ought to b pretty common)
I've downloaded a "unix" jdbc driver from Microsoft. I got to it via the help on SQL Server Manager Studio 2005. The disclaimer gives some specifis OSes it supports including Red Hat, so I'm hoping that counts for slackware as well. If anyone can suggest a better driver, or knows that this one won't work, please let me know.
I'm going through the docs that came with the driver. My first order of business is the connection string URL. the basic format is:
jdbc:sqlserver://serverNam
So far I've contructed:
jdbc:sqlserver://server;tr
Lotta issues right off the bat. The sql server host is not local, there there has to be an http:// and IP or domain in there somewhere. How does that go?
Also, I'm familiar with the concept of "instance" from Oracle, but I'm not sure it's the same on SQL Server. In my Access connect string, the server is server\traverse105 and I connect to the OSH2007 database. So should my string be: jdbc:sqlserver://server\tr
Then there's this note in the MS documentation:
"If both a portNumber and instanceName are used, the portNumber will take precedence and the instanceName will be ignored."
That makes no sense to me. What does that mean? A different port is associated with each instance? I must not understand what instance means in this context.
Help!
muddlin' through (too bad there isn't a step-by-step for my configuration. It ought to b pretty common)
ASKER
OK guys, here's where I am. I think I have all the elements, but nothing specifically for jsp/SQL Server. I've used the fornewbie.com link and the MS documentation to get started. Here's my pertinent info:
I've put Microsoft's sqljdbc.jar in $CATALINA_HOME/common/lib
classname: com.microsoft.sqlserver.jd
datasource (?): com.microsoft.sqlserver.jd
url: "jdbc:sqlserver://myDomain
the fornewbie.com context.xml is this (for mySql):
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/jspTutorial" docBase="jspTutorial"
debug="5" reloadable="true" crossContext="true">
<Resource name="jdbc/jspTutorial" auth="Container" type="javax.sql.DataSource
maxActive="100" maxIdle="30" maxWait="10000"
username="root" password="" driverClassName="com.mysql
url="jdbc:mysql://localhos
</Context>
I've modified mine to:
<?xml version="1.0" encoding="ISO-8859-1"?>
<Context path="/ohprs" docBase="ohprs"
debug="5" reloadable="true" crossContext="true">
<Resource name="jdbc/ohprs" auth="Container" type="javax.sql.DataSource
maxActive="100" maxIdle="30" maxWait="10000"
username="mark" password="12345" driverClassName="com.micro
url="jdbc:sqlserver://serv
</Context>
</web-app>
I'm not sure that's right. In particular things like datasource and maxWait might be for mySql.
To kick-start me, can someone a) verify/improve the above context.xml and b) Help me write the jsp code for a simple query: "select firstName from tblPaEmpGenInfo where employeeId = '001642'"
The fornewbie.com example has lots of import and object clasess that I'm not quite sure I get. Plus, it might be more complex than I need to get started. Here's the fornewbie.com example using the mySql and the above context.xml. How would I do my simpler query using SQL server?
<%@ page import="javax.naming.Initi
<%@ page import="javax.sql.DataSour
<%@ page import="java.sql.Connectio
<%@ page import="java.sql.PreparedS
<%@ page import="java.sql.ResultSet
<%@ page import="javax.naming.Namin
<%@ page import="java.sql.SQLExcept
<%@ page contentType="text/html;cha
<%
ResultSet rs = null;
try {
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:/comp/env
Connection conn = ds.getConnection();
PreparedStatement pstmt = conn.prepareStatement("SEL
rs = pstmt.executeQuery();
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
%>
<html>
<head><title>JNDI Datasource tutorial</title></head>
<body>
<table border="1">
<tr><td>EmployeeID</td><td
<% while (rs.next()) { %>
<tr>
<td><%= rs.getString("EmployeeID")
<td><%= rs.getString("EmployeeName
<td><%= rs.getString("Address")%><
</tr>
<% } %>
</table>
</body>
</html>
what I've come up with so far for the context.xml:
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/jspTutorial" docBase="jspTutorial"
debug="5" reloadable="true" crossContext="true">
<Resource name="jdbc/jspTutorial" auth="Container" type="javax.sql.DataSource
maxActive="100" maxIdle="30" maxWait="10000"
username="root" password="" driverClassName="com.mysql
url="jdbc:mysql://localhos
</Context>
I've put Microsoft's sqljdbc.jar in $CATALINA_HOME/common/lib
classname: com.microsoft.sqlserver.jd
datasource (?): com.microsoft.sqlserver.jd
url: "jdbc:sqlserver://myDomain
the fornewbie.com context.xml is this (for mySql):
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/jspTutorial" docBase="jspTutorial"
debug="5" reloadable="true" crossContext="true">
<Resource name="jdbc/jspTutorial" auth="Container" type="javax.sql.DataSource
maxActive="100" maxIdle="30" maxWait="10000"
username="root" password="" driverClassName="com.mysql
url="jdbc:mysql://localhos
</Context>
I've modified mine to:
<?xml version="1.0" encoding="ISO-8859-1"?>
<Context path="/ohprs" docBase="ohprs"
debug="5" reloadable="true" crossContext="true">
<Resource name="jdbc/ohprs" auth="Container" type="javax.sql.DataSource
maxActive="100" maxIdle="30" maxWait="10000"
username="mark" password="12345" driverClassName="com.micro
url="jdbc:sqlserver://serv
</Context>
</web-app>
I'm not sure that's right. In particular things like datasource and maxWait might be for mySql.
To kick-start me, can someone a) verify/improve the above context.xml and b) Help me write the jsp code for a simple query: "select firstName from tblPaEmpGenInfo where employeeId = '001642'"
The fornewbie.com example has lots of import and object clasess that I'm not quite sure I get. Plus, it might be more complex than I need to get started. Here's the fornewbie.com example using the mySql and the above context.xml. How would I do my simpler query using SQL server?
<%@ page import="javax.naming.Initi
<%@ page import="javax.sql.DataSour
<%@ page import="java.sql.Connectio
<%@ page import="java.sql.PreparedS
<%@ page import="java.sql.ResultSet
<%@ page import="javax.naming.Namin
<%@ page import="java.sql.SQLExcept
<%@ page contentType="text/html;cha
<%
ResultSet rs = null;
try {
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:/comp/env
Connection conn = ds.getConnection();
PreparedStatement pstmt = conn.prepareStatement("SEL
rs = pstmt.executeQuery();
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
%>
<html>
<head><title>JNDI Datasource tutorial</title></head>
<body>
<table border="1">
<tr><td>EmployeeID</td><td
<% while (rs.next()) { %>
<tr>
<td><%= rs.getString("EmployeeID")
<td><%= rs.getString("EmployeeName
<td><%= rs.getString("Address")%><
</tr>
<% } %>
</table>
</body>
</html>
what I've come up with so far for the context.xml:
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/jspTutorial" docBase="jspTutorial"
debug="5" reloadable="true" crossContext="true">
<Resource name="jdbc/jspTutorial" auth="Container" type="javax.sql.DataSource
maxActive="100" maxIdle="30" maxWait="10000"
username="root" password="" driverClassName="com.mysql
url="jdbc:mysql://localhos
</Context>
You can get Microsoft's JDBC Type 4 driver here:
http://msdn2.microsoft.com/en-us/data/aa937724.aspx
This is Microsoft's documentation on making Java connections to SQL Server:
http://msdn2.microsoft.com/en-us/library/ms378428.aspx
If you followed the fornewbie instructions, then yes, you have configured your environment and your code for connecting with the MySQL db that you installed.
You'll have to change the database driver and connection strings to match SQL server. You'll have to make sure that you can connect to your SQL server db instance running on another machine (using the right URL, and making SQL server available from the Windows machine).
We use Java code like this to connect to our SQL Server db (without the context.xml info above, which is not needed):
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String dbDriver = "com.microsoft.sqlserver.j
String dbURL = "jdbc:sqlserver://[machine
String dbUser = "dbusername";
String dbPassword = "dbpassword";
try{
Class.forName( dbDriver );
con = DriverManager.getConnectio
stmt = con.createStatement();
rs = stmt.executeQuery("Select * from mytable");
while( rs.next() ) {
int id = rs.getInt("Id");
}
} catch ( SQLException sqe) {
// error handling here
}
You would need to fill in the right values for your environment.
http://msdn2.microsoft.com/en-us/data/aa937724.aspx
This is Microsoft's documentation on making Java connections to SQL Server:
http://msdn2.microsoft.com/en-us/library/ms378428.aspx
If you followed the fornewbie instructions, then yes, you have configured your environment and your code for connecting with the MySQL db that you installed.
You'll have to change the database driver and connection strings to match SQL server. You'll have to make sure that you can connect to your SQL server db instance running on another machine (using the right URL, and making SQL server available from the Windows machine).
We use Java code like this to connect to our SQL Server db (without the context.xml info above, which is not needed):
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String dbDriver = "com.microsoft.sqlserver.j
String dbURL = "jdbc:sqlserver://[machine
String dbUser = "dbusername";
String dbPassword = "dbpassword";
try{
Class.forName( dbDriver );
con = DriverManager.getConnectio
stmt = con.createStatement();
rs = stmt.executeQuery("Select * from mytable");
while( rs.next() ) {
int id = rs.getInt("Id");
}
} catch ( SQLException sqe) {
// error handling here
}
You would need to fill in the right values for your environment.
You are doing it right (provided you should close the PreparedStatments,
ResultSet, and Connection). Is there any specific exception you are getting
here. You may need to keep the driver's jar file in $tomcat.home/common/lib.
ResultSet, and Connection). Is there any specific exception you are getting
here. You may need to keep the driver's jar file in $tomcat.home/common/lib.
ASKER
mrcoffee365: I did get the drive and that documentation comes with it. I'm going the try your example. If I don't need the context.xml, I'm not going to mess with it right now. What is that anyway, like a .ini file for this app?
Ajay-Singh, do you mean do rs.close? What else? I have installed sqljdbc.jar in $CATALINA_HOME/common/lib
Can I use a domain name or IP where mrcoffee365 has [machineonnetwork]? My target machine is in another domain.
Ajay-Singh, do you mean do rs.close? What else? I have installed sqljdbc.jar in $CATALINA_HOME/common/lib
Can I use a domain name or IP where mrcoffee365 has [machineonnetwork]? My target machine is in another domain.
ASKER
OK, here's my first problem. I went to run the test jsp and got:
An error occurred at line: 17 in the jsp file: /trythis.jsp
Connection cannot be resolved to a type
14: <body style="font-family: Arial; font-size: 12pt">
15:
16: <%
17: Connection con = null;
18: Statement stmt = null;
19: ResultSet rs = null;
20: String dbDriver = "com.microsoft.sqlserver.j
I assume it's not finding the Connection class. I do have $CATALINA_HOME/common/lib/
An error occurred at line: 17 in the jsp file: /trythis.jsp
Connection cannot be resolved to a type
14: <body style="font-family: Arial; font-size: 12pt">
15:
16: <%
17: Connection con = null;
18: Statement stmt = null;
19: ResultSet rs = null;
20: String dbDriver = "com.microsoft.sqlserver.j
I assume it's not finding the Connection class. I do have $CATALINA_HOME/common/lib/
You should import java.sql.*
on the jsp imports
on the jsp imports
>>Can I use a domain name or IP where mrcoffee365 has [machineonnetwork]? My target machine is in another domain.
You identify the host of the SQL Server machine with the same designation you would use in your /etc/hosts file. You would not put http://, but you would put the host DNS identification.
So if the database machine is known as
db1.locallan.com
then put
jdbc:sqlserver://db1.local
as the server identification. If the machine is not found, you might try adding it to your hosts file, with the correct IP address.
Or you can put the IP address in:
jdbc:microsoft:sqlserver:/
You can get rid of most of the other imports you have from the fornewbie example if you use my example.
<%@ page import="java.sql.*"%>
You identify the host of the SQL Server machine with the same designation you would use in your /etc/hosts file. You would not put http://, but you would put the host DNS identification.
So if the database machine is known as
db1.locallan.com
then put
jdbc:sqlserver://db1.local
as the server identification. If the machine is not found, you might try adding it to your hosts file, with the correct IP address.
Or you can put the IP address in:
jdbc:microsoft:sqlserver:/
You can get rid of most of the other imports you have from the fornewbie example if you use my example.
<%@ page import="java.sql.*"%>
ASKER
That's exacty how I am trying the host, using the I.P. I'll try hostname later.
OK, getting closer. New error. btw - I am new at jsp too, so these are probably rookie programmer errors at this point. But if you can get me through I should be OK for a while. Do I need to import additional things? Is it not finding the MS sqljdbc.jar file? Here's the error:
org.apache.jasper.JasperEx
26:
27: try{
28:
29: Class.forName( dbDriver );
30: con = DriverManager.getConnectio
31: stmt = con.createStatement();
32:
root cause
javax.servlet.ServletExcep
org.apache.jasper.runtime.
org.apache.jasper.runtime.
org.apache.jsp.trythis_jsp
org.apache.jasper.runtime.
javax.servlet.http.HttpSer
org.apache.jasper.servlet.
org.apache.jasper.servlet.
org.apache.jasper.servlet.
javax.servlet.http.HttpSer
root cause
java.lang.ClassNotFoundExc
org.apache.catalina.loader
org.apache.catalina.loader
org.apache.jasper.servlet.
org.apache.jasper.servlet.
java.lang.ClassLoader.load
java.lang.Class.forName0(N
java.lang.Class.forName(Cl
org.apache.jsp.trythis_jsp
org.apache.jasper.runtime.
javax.servlet.http.HttpSer
org.apache.jasper.servlet.
org.apache.jasper.servlet.
org.apache.jasper.servlet.
javax.servlet.http.HttpSer
OK, getting closer. New error. btw - I am new at jsp too, so these are probably rookie programmer errors at this point. But if you can get me through I should be OK for a while. Do I need to import additional things? Is it not finding the MS sqljdbc.jar file? Here's the error:
org.apache.jasper.JasperEx
26:
27: try{
28:
29: Class.forName( dbDriver );
30: con = DriverManager.getConnectio
31: stmt = con.createStatement();
32:
root cause
javax.servlet.ServletExcep
org.apache.jasper.runtime.
org.apache.jasper.runtime.
org.apache.jsp.trythis_jsp
org.apache.jasper.runtime.
javax.servlet.http.HttpSer
org.apache.jasper.servlet.
org.apache.jasper.servlet.
org.apache.jasper.servlet.
javax.servlet.http.HttpSer
root cause
java.lang.ClassNotFoundExc
org.apache.catalina.loader
org.apache.catalina.loader
org.apache.jasper.servlet.
org.apache.jasper.servlet.
java.lang.ClassLoader.load
java.lang.Class.forName0(N
java.lang.Class.forName(Cl
org.apache.jsp.trythis_jsp
org.apache.jasper.runtime.
javax.servlet.http.HttpSer
org.apache.jasper.servlet.
org.apache.jasper.servlet.
org.apache.jasper.servlet.
javax.servlet.http.HttpSer
It's not finding the SQL Server driver, as you suspected.
Have you looked in your sqljdbc.jar file to make sure that the path exists? It changed between SQL Server 2000 and SQL Server 2005. If you are linking to SQL Server 2005, then you'll need that one, and your jar file should be sqljdbc.jar.
Which version of SQL Server are you connecting with, and what are the classpaths in your sqljdbc jar file?
Are you sure that the sqljdbc.jar file is in /common/lib? Did you restart Tomcat?
Are you using an IDE for your work? In that case, it might be that you have to add the sqljdbc.jar file to your project classpath.
Have you looked in your sqljdbc.jar file to make sure that the path exists? It changed between SQL Server 2000 and SQL Server 2005. If you are linking to SQL Server 2005, then you'll need that one, and your jar file should be sqljdbc.jar.
Which version of SQL Server are you connecting with, and what are the classpaths in your sqljdbc jar file?
Are you sure that the sqljdbc.jar file is in /common/lib? Did you restart Tomcat?
Are you using an IDE for your work? In that case, it might be that you have to add the sqljdbc.jar file to your project classpath.
ASKER
I am using SQL 2005 and I downloaded the jdbc 1.1 driver from Microsoft. I do not know how to look into the sqljdbc.jar file to see what classpaths are in there. I did put $CATALINA_HOME/common/lib/
I added ALL the import lines from the fornewbie example and commented out yours. It couldn't find the class for Statement. Then I commented out the fornewbie imports that were java.sql.... and re-inserted your java.sql*. Still blowing up on the same line: Class.forName( dbDriver ), but with a different error
exception
org.apache.jasper.JasperEx
34:
35: try{
36:
37: Class.forName( dbDriver );
38: con = DriverManager.getConnectio
39: stmt = con.createStatement();
40:
root cause
javax.servlet.ServletExcep
What is "Class.forName(dbDriver);"
String dbDriver = "com.microsoft.sqlserver.j
So, in any case I assume this means it can't find com.microsoft.sqlserver.jd
I added ALL the import lines from the fornewbie example and commented out yours. It couldn't find the class for Statement. Then I commented out the fornewbie imports that were java.sql.... and re-inserted your java.sql*. Still blowing up on the same line: Class.forName( dbDriver ), but with a different error
exception
org.apache.jasper.JasperEx
34:
35: try{
36:
37: Class.forName( dbDriver );
38: con = DriverManager.getConnectio
39: stmt = con.createStatement();
40:
root cause
javax.servlet.ServletExcep
What is "Class.forName(dbDriver);"
String dbDriver = "com.microsoft.sqlserver.j
So, in any case I assume this means it can't find com.microsoft.sqlserver.jd
ASKER
(actually, it's not a different error exception)
Basically, the problem is that Tomcat is not loading the sqljdbc.jar classes. Or, possibly, the sqljdbc.jar file you have has the classes named differently.
Please post your code where you set the values for the strings being passed to Class.forName() and getConnection(). Maybe there's a typo somewhere in the strings.
You can see the contents of a .jar file by using the jar command, something like this:
jar tf sqljdbc.jar
it should list the classes in the .jar file. Winzip will open .jar files, if you have a way to look at your .jar file on a Windows box. There are probably other utilities, as well.
Do you still have the MySQL jar from the fornewbie instructions? Tomcat might be loading the MySQL classes first, and not loading the SQL Server one. Get rid of the MySQL jar, and any other jdbc jars you have (or just move them somewhere else, if you think you'll want to use them later), then restart Tomcat.
The sqljdbc.jar should only have to go in Tomcat's common/lib directory, it should have to be in other locations on the machine.
Also -- did you leave the MySQL references in your server.xml? You might want to remove those, too, since you're taking out the jdbc driver for mysql.
Please post your code where you set the values for the strings being passed to Class.forName() and getConnection(). Maybe there's a typo somewhere in the strings.
You can see the contents of a .jar file by using the jar command, something like this:
jar tf sqljdbc.jar
it should list the classes in the .jar file. Winzip will open .jar files, if you have a way to look at your .jar file on a Windows box. There are probably other utilities, as well.
Do you still have the MySQL jar from the fornewbie instructions? Tomcat might be loading the MySQL classes first, and not loading the SQL Server one. Get rid of the MySQL jar, and any other jdbc jars you have (or just move them somewhere else, if you think you'll want to use them later), then restart Tomcat.
The sqljdbc.jar should only have to go in Tomcat's common/lib directory, it should have to be in other locations on the machine.
Also -- did you leave the MySQL references in your server.xml? You might want to remove those, too, since you're taking out the jdbc driver for mysql.
ASKER
Thanks for the 'jar tf' tip. I'm sure that in the docs and I'd get ot it eventually, but shortcuts help. Do you want me to post the jar tf results? It's pretty big?
OK, here's my code. Basically I use the exact same imports as the fornewbie example, but replaced all their java.sql imports with your one jdava.sql.* :
<html>
<head>
<title>TEST PROGRAM</title>
</head>
<body style="font-family: Arial; font-size: 12pt">
<%@ page import="javax.naming.Initi
<%@ page import="javax.sql.DataSour
<%--@ page import="java.sql.Connectio
<%--@ page import="java.sql.PreparedS
<%--@ page import="java.sql.ResultSet
<%@ page import="javax.naming.Namin
<%--@ page import="java.sql.SQLExcept
<%@ page contentType="text/html;cha
<%@ page import="java.sql.*" %>
<%
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String dbDriver = "com.microsoft.sqlserver.j
String dbURL = "jdbc:sqlserver://66.194.1
String dbUser = "mark";
String dbPassword = "M12345f";
try{
Class.forName( dbDriver );
con = DriverManager.getConnectio
stmt = con.createStatement();
rs = stmt.executeQuery("Select firstName from dbo.tblPaEmpGenInfo where employeeId = '001642'");
while( rs.next() ) {
int id = rs.getInt("Id");
}
} catch ( SQLException sqe) { %>
<%= "Error" %>
<%
}
%>
</body>
</html>
OK, here's my code. Basically I use the exact same imports as the fornewbie example, but replaced all their java.sql imports with your one jdava.sql.* :
<html>
<head>
<title>TEST PROGRAM</title>
</head>
<body style="font-family: Arial; font-size: 12pt">
<%@ page import="javax.naming.Initi
<%@ page import="javax.sql.DataSour
<%--@ page import="java.sql.Connectio
<%--@ page import="java.sql.PreparedS
<%--@ page import="java.sql.ResultSet
<%@ page import="javax.naming.Namin
<%--@ page import="java.sql.SQLExcept
<%@ page contentType="text/html;cha
<%@ page import="java.sql.*" %>
<%
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String dbDriver = "com.microsoft.sqlserver.j
String dbURL = "jdbc:sqlserver://66.194.1
String dbUser = "mark";
String dbPassword = "M12345f";
try{
Class.forName( dbDriver );
con = DriverManager.getConnectio
stmt = con.createStatement();
rs = stmt.executeQuery("Select firstName from dbo.tblPaEmpGenInfo where employeeId = '001642'");
while( rs.next() ) {
int id = rs.getInt("Id");
}
} catch ( SQLException sqe) { %>
<%= "Error" %>
<%
}
%>
</body>
</html>
Did you make sure to remove the MySQL references in your server.xml and context.xml files? That might cause Tomcat to load those jdbc drivers first, and might be interfering with your paths.
The code you posted works mostly "as is" in my environment, so you are identifying the driver class correctly.
I made the following changes (the key one is rs.getString):
%>
<table>
<%
try{
Class.forName( dbDriver );
con = DriverManager.getConnectio
stmt = con.createStatement();
rs = stmt.executeQuery("Select firstName from dbo.tblPaEmpGenInfo where employeeId = '001642'");
while( rs.next() ) {
String firstName = rs.getString("tblPaEmpGenI
%>
<tr><td>First Name:</td><td><%=firstName
<%
}
} catch ( SQLException sqe) { %>
<%= "Error" %>
<%
}
try{
if( rs != null ) rs.close(); if( con != null) con.close();
} catch(SQLException sqe2){}
%>
</table>
I have sqljdbc.jar in the [tomcat home]/common/lib directory, and nowhere else. I left the includes as you have them above. There is no jdbc jar in the [java home]/lib directory or any other jdbc jar in the Tomcat directories.
You should not have to put the sqljdbc.jar file explicitly in your Tomcat CLASSPATH, but you might try that.
If you are sure that you are not picking up the mysql jdbc jar, then the problem might be with loading the Microsoft .jar file.
You could try switching to the jdbc-odbc bridge for connecting to SQL server, which means you'll need an odbc driver for your linux machine. Then change the following:
dbURL: jdbc:odbc:ODBCOSH2007
dbDriver: sun.jdbc.odbc.JdbcOdbcDriv
where ODBCOSH2007 is the ODBC registered name which points to your remote SQL Server db.
Follow my original link for an example of how to do all of this with the EasySoft ODBC driver (ignore all of the PHP setup info). Or get the freetds driver, and use that one. The Sun jdbc-odbc driver can be found from this link:
http://java.sun.com/products/jdbc/download.html#spec
With some jdbc-odbc bridge info here:
http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/bridge.doc.html
Or there's a Sourceforge project with a driver for Microsoft products:
http://jtds.sourceforge.net/
It's a Type 4 driver, so you wouldn't have to also install an ODBC driver.
The code you posted works mostly "as is" in my environment, so you are identifying the driver class correctly.
I made the following changes (the key one is rs.getString):
%>
<table>
<%
try{
Class.forName( dbDriver );
con = DriverManager.getConnectio
stmt = con.createStatement();
rs = stmt.executeQuery("Select firstName from dbo.tblPaEmpGenInfo where employeeId = '001642'");
while( rs.next() ) {
String firstName = rs.getString("tblPaEmpGenI
%>
<tr><td>First Name:</td><td><%=firstName
<%
}
} catch ( SQLException sqe) { %>
<%= "Error" %>
<%
}
try{
if( rs != null ) rs.close(); if( con != null) con.close();
} catch(SQLException sqe2){}
%>
</table>
I have sqljdbc.jar in the [tomcat home]/common/lib directory, and nowhere else. I left the includes as you have them above. There is no jdbc jar in the [java home]/lib directory or any other jdbc jar in the Tomcat directories.
You should not have to put the sqljdbc.jar file explicitly in your Tomcat CLASSPATH, but you might try that.
If you are sure that you are not picking up the mysql jdbc jar, then the problem might be with loading the Microsoft .jar file.
You could try switching to the jdbc-odbc bridge for connecting to SQL server, which means you'll need an odbc driver for your linux machine. Then change the following:
dbURL: jdbc:odbc:ODBCOSH2007
dbDriver: sun.jdbc.odbc.JdbcOdbcDriv
where ODBCOSH2007 is the ODBC registered name which points to your remote SQL Server db.
Follow my original link for an example of how to do all of this with the EasySoft ODBC driver (ignore all of the PHP setup info). Or get the freetds driver, and use that one. The Sun jdbc-odbc driver can be found from this link:
http://java.sun.com/products/jdbc/download.html#spec
With some jdbc-odbc bridge info here:
http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/bridge.doc.html
Or there's a Sourceforge project with a driver for Microsoft products:
http://jtds.sourceforge.net/
It's a Type 4 driver, so you wouldn't have to also install an ODBC driver.
ASKER
Wow, lots of information. I think you are right in that it is not finding my sqljdbc.jar, but I am not sure why. Is there any system log or whatnot that would tell me what the problem might be? I hate to switch horses just yet and try the jdbc-odbc route. The error indicates that it's not even"compiling" properly, so I haven't yet begun to explore actual connection problems. This is Microsoft's offical sqljdbc, freshly downloaded and supposedly works for tophat, sun, etc. It *ought* to work OK if I can get tomcat to see it. It is only in ...tomcat/common/lib, nowhere else. In fact, there never has been a MySQL jar on this system. The sqljdbc.jar is in CLASSPATH
I don't have a server.xml. Should I? I have a web.xml and the newly created context.sql (below). I don't see any mySql references in there. You had suggested that I don't even need this. Should I remove it?
Other than switch to odbc-bridge, what is you next suggestion as to how I can get tomcat to find sqljdbc.jar?
My context.xml:
(note, I do see that the url is different. I'll change that, but since I'm using the one from the program, I don't think it will matter)
<?xml version="1.0" encoding="ISO-8859-1"?>
<Context path="/ohprs" docBase="ohprs"
debug="5" reloadable="true" crossContext="true">
<Resource name="jdbc/ohprs" auth="Container" type="javax.sql.DataSource
maxActive="100" maxIdle="30" maxWait="10000"
username="mark" password="12345" driverClassName="com.micro
url="jdbc:sqlserver://serv
</Context>
</web-app>
I don't have a server.xml. Should I? I have a web.xml and the newly created context.sql (below). I don't see any mySql references in there. You had suggested that I don't even need this. Should I remove it?
Other than switch to odbc-bridge, what is you next suggestion as to how I can get tomcat to find sqljdbc.jar?
My context.xml:
(note, I do see that the url is different. I'll change that, but since I'm using the one from the program, I don't think it will matter)
<?xml version="1.0" encoding="ISO-8859-1"?>
<Context path="/ohprs" docBase="ohprs"
debug="5" reloadable="true" crossContext="true">
<Resource name="jdbc/ohprs" auth="Container" type="javax.sql.DataSource
maxActive="100" maxIdle="30" maxWait="10000"
username="mark" password="12345" driverClassName="com.micro
url="jdbc:sqlserver://serv
</Context>
</web-app>
ASKER
If you go to http://www.novatec-inc.com:8081/ohprs/trythis.jsp you can see the whole stack trace, etc.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK, I'll try some of your suggestions and get back to you. I do have the Microsoft docs, but I'll check you link to verify they are the sam. Maybe there's some tips in there. No hesitation about getting rid of context.xml, I just thought that if I wasn't doing any ctx.lookup()'s it wouldn't matter. Consider it GONE!
The reason I think it would be good to get rid of context.xml with a db reference in it is that I'm worried that Tomcat might try to load classes at initialization, and that that class load is interfering with loading sqljdbc. Shouldn't really be a problem but there's something wrong with your class-loading environment, so it's better to get rid of everything that isn't needed.
Here is a good page explaining class loading in Tomcat:
http://tomcat.apache.org/tomcat-4.1-doc/class-loader-howto.html
It reminds me that maybe the right thing to do is put sqljdbc.jar in the WEB-INF/lib directory. That isn't where we keep it, because we use the database for Tomcat form-based login (so it has to be defined in server.xml), but different instances of Tomcat might do different things.
Here is a good page explaining class loading in Tomcat:
http://tomcat.apache.org/tomcat-4.1-doc/class-loader-howto.html
It reminds me that maybe the right thing to do is put sqljdbc.jar in the WEB-INF/lib directory. That isn't where we keep it, because we use the database for Tomcat form-based login (so it has to be defined in server.xml), but different instances of Tomcat might do different things.
ASKER
Eureka! Putting the sqljdbc.jar into the WEB-INF/lib for my application (context) worked! Now, when I say "worked", I mean I got no error, but I didn't get results. At this point I'm pretty sure I have to work on the connection with the DB server. Command-level test show that I cannot yet get to port 1433 from my host. Different problem; I'm pretty sure I can solve that one on my own (I am the DBA for the SQL Server database, after all).
For whatever reason, tomcat is not picking up the path to the jarfile from the CLASSPATH. I read the following in the tomcat 6.0.14 RELEASNOTES:
==========================
Symlinking static resources:
==========================
By default, Unix symlinks will not work when used in a web application to link
resources located outside the web application root directory.
This behavior is optional, and the "allowLinking" flag may be used to disable
the check.
The sqljdbc.jar *was* in a symlinked directory, so I changed my CLASSPATH to point to the true path. Still no go. I actually had to put it in my WEB-INF/lib. With respect to symlinks, I will probably be posting a question about the above. It says the allowLinking flag may be used, but it doesn't say where this flag is to be found. I guess I should "just know".
Anyway, thanks to both of you, that was brutal! I feel like I do need to give Ajay-Singh a few points since it was his fornewbie example we used; plus other tips. I'll be posting more questions very soon (including the symlink question). Keep your eyes peeled. mrcoffee365, you deserve an extra "gimme" on an easy question for all your help on this one.
For whatever reason, tomcat is not picking up the path to the jarfile from the CLASSPATH. I read the following in the tomcat 6.0.14 RELEASNOTES:
==========================
Symlinking static resources:
==========================
By default, Unix symlinks will not work when used in a web application to link
resources located outside the web application root directory.
This behavior is optional, and the "allowLinking" flag may be used to disable
the check.
The sqljdbc.jar *was* in a symlinked directory, so I changed my CLASSPATH to point to the true path. Still no go. I actually had to put it in my WEB-INF/lib. With respect to symlinks, I will probably be posting a question about the above. It says the allowLinking flag may be used, but it doesn't say where this flag is to be found. I guess I should "just know".
Anyway, thanks to both of you, that was brutal! I feel like I do need to give Ajay-Singh a few points since it was his fornewbie example we used; plus other tips. I'll be posting more questions very soon (including the symlink question). Keep your eyes peeled. mrcoffee365, you deserve an extra "gimme" on an easy question for all your help on this one.
Congrats on getting it working, and good luck.
http://www.easysoft.com/developer/languages/php/sql_server_unix_tutorial.html
You don't have to buy an ODBC driver, you can use a free package like freetds.
SQL Server usually connects on port 1433. If that is not available from your Windows box, then you'll have to make it available to your Linux machine, or make whatever port you configure for SQL server available to the Linux machine.
The Microsoft Type 4 JDBC driver for SQL Server is supposed to run on Linux, so you could also try that.