1. Verify that you can login to the SQL Server using 'SQL Server Authentication'. You may wish to change the 'Server Authentication mode' to 'SQL Server and Windows Authentication mode'. You may also wish to check that the particular user's status of Login is 'Enabled'.
2. Verify that 'Local and remote connections' is enabled.
Go to Microsoft SQL Server 2005>Configuration Tools>SQL Server Surface Area Configuration>Remote Connections: Enable TCP/IP
3. Restart the database
4. Download and drop the JDBC driver to tomcat_home/lib. You may find tomcat-dbcp.jar in the directory. This will make the driver available to both the Tomcat internal classes and the web application.
5. Change the context.xml, for example:
<?xml version="1.0" encoding="UTF-8"?>
<context path="/BFS2">
<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="sa" password="passw0rd" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;databaseName=TestOne" />
</context>
6. Change the web.xml, for example:
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xsi="http://www.w3.org/2001/XMLSchema-instance" schemalocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/TestDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
7. Write a testing JSP page like this:
<%@ page contentType="text/html;charset=UTF-8" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<HTML>
<HEAD>
<TITLE>JSP example</TITLE>
</HEAD>
<BODY>
<h1>Hello,test JNDI ! </h1>
<%
Context ctx = new InitialContext();
Context envctx = (Context) ctx.lookup("java:comp/env");
DataSource ds = (DataSource) envctx.lookup("jdbc/TestDB");
Connection conn=ds.getConnection();
Statement st=conn.createStatement();
String sql="select * from status";
ResultSet rs=st.executeQuery(sql);
while(rs.next()) {
%>
ID:<%=rs.getInt(1) %>
Value:<%=rs.getString(2) %>
<br>
<%
}
%>
Here is just JNDI datasource SQL Server 2005 + tomcat example
<%
rs.close();
st.close();
conn.close();
%>
</BODY>
</HTML>
That's it!
Thank you! That was a huge help:)
ReplyDeletethank you!
ReplyDeleteThank you sooo much! this is the only post worked for me, and very easy btw.
ReplyDeleteIt worked for me too! Really thanks!!
ReplyDelete--sal
Hey,
ReplyDeletethanks so so much... I have been looking for this long time...
thank you very much.
jimmygarita
Thanks a lot. You save me so much time on reading Apache docs.
ReplyDeleteWhat about this guys ?
ReplyDeletehttp://www.eclipse.org/articles/article.php?file=Article-EclipseDbWebapps/index.html
Anyway - this is perfect and wery much useful for me to understand how it working !!
It's good to configure the IDE to be aware of your database. However it's not absolutely necessary for most developers.
ReplyDeleteMay I ask you why is important step 6 ?? I tested web application without this step and it working fine ??!
ReplyDeleteWhy it it there ?
Thanks.
My server fails to start(on eclipse 3.4) I tried with the above code as well as my own code. Do i need to add any reference in server.xml?
ReplyDeleteAlso, can you throw some light on how to implement pooling for sql2005 datasources in tomcat6. Thanks.
Excellent! I got it to work after a day of trying other options. This was the best and easiest i found.
ReplyDeletethanks bro...
ReplyDeletedid not work
ReplyDeleteThanks and I have a swell offer: Whole House Renovation Checklist Pdf average home remodel cost
ReplyDelete