aspose file tools*
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
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
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: JSP MSSQL - Connection Problem