This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes JSP MSSQL - Connection Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JSP MSSQL - Connection Problem" Watch "JSP MSSQL - Connection Problem" New topic
Author

JSP MSSQL - Connection Problem

Shi Hao Wey
Greenhorn

Joined: Mar 24, 2011
Posts: 3
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...
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

Not on topic I know, but JDBC inside of JSP is really bad, especially since you mention you have multiple pages that need to use the database. You should put it in a reusable class file.

As for your issue "<%!" is reserved for class declarations, not body code, therefore "<%" should be used.


My Blog: Down Home Country Coding with Scott Selikoff
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JSP MSSQL - Connection Problem
 
Similar Threads
Can we use two quries using prepared statement in one method
ejbLoad is not getting called
Strange behaviour of ResultSet next method
Exception
Can we use two quries using prepared statement in one method