• Post Reply Bookmark Topic Watch Topic
  • New Topic

using JSP to read SQL data  RSS feed

 
Geoff Garcia
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Forgive my noob question.
I've used the search tool but haven't found the info I need.

I'd like to connect from jsp to SQL and do a simple query.
I've come up with this amount of code so far, but it fails left right and center...

I'm using MS SQL Server 2000 and Tomcat, jdk is 1.4.1_06

I can't even get this line to run:
<%@ page language="java" import="java.sql.*" %>
<%
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
%>

the error I get is:
javax.servlet.ServletException: com.microsoft.jdbc.sqlserver.SQLServerDriver
at org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:536)
at org.apache.jsp.test_jsp._jspService(test_jsp.java:59)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)

any ideas to help guide me along?

I downloaded and installed teh "SQL Server 2000 Driver for JDBC Service Pack 2" from the microsoft site and I set the PATH variable, but do I need to modify the server.xml and web.xml files?
[ September 20, 2004: Message edited by: Geoff Garcia ]
 
Neeraj Vij
Ranch Hand
Posts: 315
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Try by adding driver installation location to CLASSPATH variable also...


rgds,
neeraj.
 
Ramaswamy Srinivasan
Ranch Hand
Posts: 295
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I am also new to the JSP & Servlet technologies.

What i learn from my peers is that we should use a try catch block, if we are making any thing like connection, etc.

This code is working fine for me with Oracle...

Try this....as i don't have SQL server, i can't provide that code. Check it out. Still, you find it not to be working, there shud be some problem reg the installation or something....

Check it out.







Any comments?

Cheers,
Swamy
 
Ramaswamy Srinivasan
Ranch Hand
Posts: 295
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Geoff Gracia,

Also one more of my opinion is that we need not use JSP to connect to the DB, when i comes to the practice.

It should be ur servlet or a bean, that should connect to the DB and return the values to the JSP. JSP is meant for view purposes. If this is for the sake of own practice, not an issue.

Cheers,
Swamy
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
garcia and swamy just to suugest you people one thing as you people mentioned yourself new to JSP. do not do database stuff in your JSP.

I think your JSP page is unable to find the driver. check your directory structure and make sure it is right.
 
Ramaswamy Srinivasan
Ranch Hand
Posts: 295
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Adeel,

Nice meeting you again man,

Was i right by making my second post on the same page. I was suggesting that we should not use JSP for DB things.

Just need your opinion.

Comments are welcome.

Cheers,
Swamy
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yeah we both just posted our posts a bit simultaneouly. thats why when i got the page back i just gave a smile
 
Ramaswamy Srinivasan
Ranch Hand
Posts: 295
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Adeel,

That was nice....man....i too thought that.

Then...c ya next time, with some que...

Bye till then....

Cheers,
Swamy
 
Geoff Garcia
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Neeraj Vij:
Hi,Try by adding driver installation location to CLASSPATH variable also...
rgds,
neeraj.

Thanks, Neeraj, I adjusted the classpath to now be:

CLASSPATH: D:\Verity\Webtop\Server\WEB-INF\lib\xt.jar;D:\Verity\Webtop\Server\WEB-INF\lib\xp.jar;D:\Verity\Webtop\Server\WEB-INF\lib\sax.jar;D:\Program Files\Apache Group\Tomcat 4.1\common\lib\mail.jar;D:\Program Files\Apache Group\Tomcat 4.1\common\lib\servlet.jar;D:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar;D:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver.jar;D:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar;


This did the trick and now my jsp loads correctly and performs as expected!
yay!
Thanks for all the replies so far!
Geoff
[ September 28, 2004: Message edited by: Geoff Garcia ]
 
Geoff Garcia
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried adding this code to a class file but am getting an error.
The code is short and sweet, here it is:
import java.sql.*;
public class sqlTest{
public static void main(String[] args) {
try{
Connection con;
Statement stmt;
ResultSet rslt;

con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433/", "tacit", "tacit");
stmt = con.createStatement();
rslt = stmt.executeQuery("SELECT * FROM TKS_ACCOUNT");

while(rslt.next()){
System.out.println("Result"+rslt.getInt(1));
}
con.close();

}catch(Exception e){
System.out.println("Exception:" + e);
}
}
}

The exception states:
Exception:java.sql.SQLException: No suitable driver

any idea what this is? and how to get around it? I've been searching on google but no luck finding anything out...
[ September 28, 2004: Message edited by: Geoff Garcia ]
 
Neeraj Vij
Ranch Hand
Posts: 315
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No probs Geoff.


I think you have missed registering the driver partin your next problem.
use 'Class.forName' to register you driver as you have done in your JSP.

Regards,
Neeraj.
 
Geoff Garcia
Greenhorn
Posts: 22
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Awesome! that solved the problem! thanks a ton!
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!