Shi Hao Wey

Greenhorn
+ Follow
since Mar 24, 2011
Merit badge: grant badges
For More
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Shi Hao Wey

Hi, guys!

Recently I'm trying to connection MS SQL Server 2005 from JSP, and I face a problem:
When I declare my connection in declaraction tag, the server will display ClassNotFoundException. But if I put my connection in script tag, it is working fine.

eg:
<%!
Driver driver = null;
Connection conn=null;
PreparedStatement = null;
ResultSet rs=null;

private Connection getConnect(){
if(driver==null){
driver = (Driver)Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
}
Connection cnn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;instanceName=instance1;databaseName=testDB;integratedSecurity=true","username","password");
return cnn;
}

private void displayData(){
conn=getConnect();
pstmt = conn.prepareStatement("SELECT * FROM users");
rs=pstmt.executeQuery();
while(rs.next()){
out.println(rs.getString(1));
}
rs.close();
pstmt.close();
conn.close();
}

%>
I used this method to do the connection for oracle and mysql, both are working fine. But when I connect to ms sql, it doesn't work. I have downloaded the latest sqljdbc4.jar, put it in tomcat/lib folder, and restart the tomcat.

However, when I tried to put the connection in jsp script tag, it works.
<%
Driver driver = null;
Connection conn=null;
PreparedStatement = null;
ResultSet rs=null;

if(driver==null){
driver = (Driver)Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
}
conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;instanceName=instance1;databaseName=testDB;integratedSecurity=true","username","password");


pstmt = conn.prepareStatement("SELECT * FROM users");
rs=pstmt.executeQuery();
while(rs.next()){
out.println(rs.getString(1));
}
rs.close();
pstmt.close();
conn.close();

%>

Anyone know what is the problem?? If put the connection in the script tag, it is quite troublesome for me as I have a few jsp pages need to connect to database. Or there is another way to do it?

Thank you...
actually I did put try-catch block at the beginning, but it still displays the same error. therefore, i simplified the code.
so, you means I should put the ojdbc14.jar under WEB-INF/lib folder?
but I'm not sure whether I should put it in the ROOT/WEB-INF/lib or ROOT/myWeb/WEB-INF/lib?
the JSP file is in the myWeb folder, and I need to create WEB-INF and lib folders inside?
Hi, guys...
I met a problem recently, that is, my JSP file cannot connect to my localhost oracle XE 10g db.
below is part of my code:

Driver driver=null;
public Connection getConnect(){
if(driver==null)
Class.forName("oracle.jdbc.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/XE","secret","secret");
return conn;
}

I started tomcat7.0.5, and then I tried to view that JSP file, but I get the problem.

An error occurred at line: 12 in the jsp file: /testJsp.jsp
Unhandled exception type ClassNotFoundException
9:
10: public Connection getConnect(){
11: if(driver==null)
12: Class.forName("oracle.jdbc.OracleDriver");
13: Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/XE","SYSTEM","z_blue1009");
14: return conn;


An error occurred at line: 13 in the jsp file: /testJsp.jsp
Unhandled exception type SQLException
10: public Connection getConnect(){
11: if(driver==null)
12: Class.forName("oracle.jdbc.OracleDriver");
13: Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/XE","SYSTEM","z_blue1009");
14: return conn;
15: }

I have tried many ways to solve the problem, yet I cannot solve the problem.
What I have tried:
1. Set the ClassPath (but my laptop is win7, and there is no ClassPath in the Environment Variables)
2. copy the ojdbc14.jar to WEB-INF/lib (but there is no lib folder in the WEB-INF, I create it and paste the jar file...not working)
3. copy the ojdbc14.jar to [JAVA-HOME]\jre\lib\ext\...not working
4. change the class path from oracle.jdbc.driver.OracleDriver to oracle.jdbc.OracleDriver...not working
5. change the URL from jdbc:oracle:thin:@localhost:1521:xe to jdbc:oracle:thin:@//localhost:1521/XE...not working

But I didn't meet such problem before when I'm using the older version of XAMPP.
What is the problem actually and how to solve it? Thanks