• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Paul Clapham
  • Bear Bibeault
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Jj Roberts
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • salvin francis
  • Scott Selikoff
  • fred rosenberger

JSP MSSQL - Connection Problem

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...
 
author
Posts: 4226
33
jQuery Eclipse IDE Java
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
Acetylsalicylic acid is aspirin. This could be handy too:
the value of filler advertising in 2020
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic