Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

how to retrieve database records from oracle

hi, i made a jsp page which retreieve's some records from a DB.
this is my prog:
______________________
<%@ page session="false" %>
<%@ page import="java.sql.*" %>
<%
try
{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
out.println("jdbc driver loaded");
}
catch(ClassNotFoundException e)
{
}
%>
<HTML>
<HEAD>
<TITLE>display all users</TITLE>
</HEAD>
<BODY>
<CENTER>
<BR><H2>displaying all users</H2>
<BR>
<BR>
<TABLE>
<TR>
<TH>first name</TH>
<TH>SSN</TH>
</TR>
<%
String s="select fname,ssn"+"from empl";
try
{
Connection c=DriverManager.getConnection("jdbc:odbc:shilpi","scott","tiger");

Statement st=c.createStatement();
ResultSet rs=st.executeQuery(s);
while(rs.next())
{out.println("t2");
out.println("<TR>");
out.println("<TD>" + rs.getString(1) +"</TD>");
out.println("<TD>" + rs.getString(2) +"</TD>");
out.println("</TR>");
}
rs.close();
st.close();
c.close();
}
catch(SQLException e){out.println("t1");}
catch(Exception e){out.println("t12");}
%>
</TABLE>
</CENTER>
</BODY>
</HTML>
------------------------------------------------------------

the table's name is employee
and it contains 2 fields; fname and ssn both varchar2

when i run this jsp file ..
i get this output;

                                displaying all users

                                  first name       SSN

but the records arent displayed ..
i'm using tomcat 5.5
and oracle 9.0.01.
how can i get the records to get displayed
0
shilpi84
Asked:
shilpi84
  • 15
  • 9
  • 7
  • +2
1 Solution
 
jaggernatCommented:
Run the query

"select fname,ssn"+"from empl";

 in TOAD or in the database side and see if it is returning any records.

0
 
shilpi84Author Commented:
yes i already did that in sql-plus(oracle) .. it shows the records
0
 
jaggernatCommented:
i think you have to give something like

String s = rs.getString()

see this

http://java.sun.com/docs/books/tutorial/jdbc/basics/updating.html
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
suprapto45Commented:
Not sure but

>>String s="select fname,ssn"+"from empl";
Shouldn't that have some extra space as
either String s="select fname,ssn "+"from empl";
or      String s="select fname,ssn from empl";
0
 
harshgroverCommented:
i think suprapto45 got the right answer...another thing that you might wanna try would be to replace the following

out.println("t1");  
out.println("t2")

with a e.getMessage();

this would print the exception that is being thrown while executing the query.

Gary
0
 
shilpi84Author Commented:
i did as suprapto45 said....
it's still displaying just this:
_____________________________
jdbc driver loaded

displaying all users


t1
first name       SSN
_____________________________

*note the t1 here. i've removed out out.print("t1"); from the code and it still shows.......?
i've pasted the code at the bottom of this comment.


but when i keep refreshing it some displays:




_________________________________
HTTP Status 500 -

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception
org.apache.jasper.JasperException: Unable to compile class for JSP

An error occurred at line: 28 in the jsp file: /e.jsp
Generated servlet error:
Syntax error, insert ")" to complete MethodInvocation

An error occurred at line: 28 in the jsp file: /e.jsp
Generated servlet error:
Syntax error, insert ";" to complete LocalVariableDeclarationStatement


      org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:512)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
      org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
      org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

root cause
org.apache.jasper.JasperException: Unable to compile class for JSP

An error occurred at line: 28 in the jsp file: /e.jsp
Generated servlet error:
Syntax error, insert ")" to complete MethodInvocation

An error occurred at line: 28 in the jsp file: /e.jsp
Generated servlet error:
Syntax error, insert ";" to complete LocalVariableDeclarationStatement


      org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:84)
      org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:328)
      org.apache.jasper.compiler.JDTCompiler.generateClass(JDTCompiler.java:414)
      org.apache.jasper.compiler.Compiler.compile(Compiler.java:297)
      org.apache.jasper.compiler.Compiler.compile(Compiler.java:276)
      org.apache.jasper.compiler.Compiler.compile(Compiler.java:264)
      org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:563)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:305)
      org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
      org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

note The full stack trace of the root cause is available in the Apache Tomcat/5.5.20 logs.
Apache Tomcat/5.5.20
-----------------------------------------------------------------------------------------------

this is the latest code:
___________________________________________________________________
<%@ page session="false" %>
<%@ page import="java.sql.*" %>
<%
try
{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
out.println("jdbc driver loaded");
}
catch(ClassNotFoundException e)
{
}
%>
<HTML>
<HEAD>
<TITLE>display all users</TITLE>
</HEAD>
<BODY>
<CENTER>
<BR><H2>displaying all users</H2>
<BR>
<BR>
<TABLE>
<TR>
<TH>first name</TH>
<TH>SSN</TH>
</TR>
<%
String s="SELECT fname, ssn FROM empl";
try
{
String url="jdbc:odbc:SHILPI";
Connection c=DriverManager.getconnection(url,"scott,"tiger");
Statement st=c.createStatement();
ResultSet rs=st.executeQuery(s);
while(rs.next())
{
out.println("<TR>");
out.println("<TD>" + rs.getString(1) +"</TD>");
out.println("<TD>" + rs.getString(2) +"</TD>");
out.println("</TR>");
}
rs.close();
st.close();
c.close();
}
catch(SQLException e){e.getMessage();}
catch(Exception e){e.getMessage();}
%>
</TABLE>
</CENTER>
</BODY>
</HTML>
___________________________________________________________________
0
 
harshgroverCommented:
my bad. i should have seen that earlier. you cannot define two variables with the same name.

replace the following line

catch(Exception e){e.getMessage();}

with this

catch(Exception e1){e1.printStackTrace();}


Also, change the following line
catch(SQLException e){e.getMessage();}

with
catch(SQLException e){e.printStackTrace();}

Thanks
Gary

0
 
shilpi84Author Commented:
i did that harsh but no luck...
this is the output in IE:

jdbc driver loaded

                                    displaying all users


                                     first name       SSN
0
 
harshgroverCommented:
hey Shilpi,

you might wanna clean out the temporary ._jsp files that get created at compile time for every jsp.

there should definitely be either an exception, or some data being displayed.

try switching between out.println(e.getMessage() ), &  e.printStackTrace(); and see if you get any exceptions printed on the page.

~Harsh
0
 
shilpi84Author Commented:
______________________________________________
<%@ page session="false" %>
<%@ page import="java.sql.*" %>
<%
try{
try
{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
out.println("jdbc driver loaded");
}
catch(ClassNotFoundException e)
{
  out.print("nope get lost");
}
%>
<HTML>
<HEAD>
<TITLE>display all users</TITLE>
</HEAD>
<BODY>
<CENTER>
<BR><H2>displaying all users</H2>
<BR>
<BR>
<TABLE>
<TR>
<TH>first name</TH>
<TH>SSN</TH>
</TR>
<%
String s="SELECT fname, ssn FROM empl";
try
{
String url="jdbc:odbc:SHILPI";

Connection c=DriverManager.getConnection(url,"scott","tiger");


out.print("test4");
Statement st=c.createStatement();
ResultSet rs=st.executeQuery(s);
while(rs.next())
{
out.println("<TR>");
out.println("<TD>" + rs.getString(1) +"</TD>");
out.println("<TD>" + rs.getString(2) +"</TD>");
out.println("</TR>");
}
rs.close();
st.close();
c.close();
}
catch(SQLException e){out.print("error1");}
//catch(Exception e){e.getMessage();}

}catch(Exception e){
    System.err.println("Ex Msg: " + e.getMessage());
     e.printStackStrace();
}
%>
</TABLE>
</CENTER>
</BODY>
</HTML>
_________________________________________________

but sometimes when i keep refreshing ...
besides the output i've told u ...
sometimes this shows up:
_________________________________________________
HTTP Status 500 -

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.apache.jasper.JasperException: Unable to compile class for JSP

An error occurred at line: 31 in the jsp file: /e.jsp
Generated servlet error:
The method printStackStrace() is undefined for the type Exception


      org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:512)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
      org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
      org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

root cause

org.apache.jasper.JasperException: Unable to compile class for JSP

An error occurred at line: 31 in the jsp file: /e.jsp
Generated servlet error:
The method printStackStrace() is undefined for the type Exception


      org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:84)
      org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:328)
      org.apache.jasper.compiler.JDTCompiler.generateClass(JDTCompiler.java:414)
      org.apache.jasper.compiler.Compiler.compile(Compiler.java:297)
      org.apache.jasper.compiler.Compiler.compile(Compiler.java:276)
      org.apache.jasper.compiler.Compiler.compile(Compiler.java:264)
      org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:563)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:305)
      org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
      org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

note The full stack trace of the root cause is available in the Apache Tomcat/5.5.20 logs.
Apache Tomcat/5.5.20
_____________________________________________________


0
 
harshgroverCommented:
if you look at the logs, this is what shows up

The method printStackStrace() is undefined for the type Exception

looks like the jsp page does not like the e.printStackTrace() method :(...

i see that you have the e.getMessage() now. do you still get the above exception? if you are still getting it, then it could be a cashing issue

clear out your browser cache, then delete the compiled JSP files, open a new browser window, and check again

~Harsh
0
 
shilpi84Author Commented:
in which log file did you see that .. all my log files are empty .. except localhost.2006-10-21.txt contains this:


_____________________________________________________
Oct 21, 2006 12:08:22 AM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet jsp threw exception
org.apache.jasper.JasperException: Unable to compile class for JSP

An error occurred at line: 31 in the jsp file: /e.jsp
Generated servlet error:
The method printStackStrace() is undefined for the type Exception


      at org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:84)
      at org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:328)
      at org.apache.jasper.compiler.JDTCompiler.generateClass(JDTCompiler.java:414)
      at org.apache.jasper.compiler.Compiler.compile(Compiler.java:297)
      at org.apache.jasper.compiler.Compiler.compile(Compiler.java:276)
      at org.apache.jasper.compiler.Compiler.compile(Compiler.java:264)
      at org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:563)
      at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:305)
      at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
      at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
      at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:833)
      at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:639)
      at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1285)
      at java.lang.Thread.run(Unknown Source)
___________________________________________
0
 
shilpi84Author Commented:
i made a console JAVA program just to check whether oracle is running properly and i have full administrative rights.
and it works properly .. it's displaying the records
program
__________________________________________
ok... this is my latest code... and it works!

import java.sql.*;

class A1
{
public static void main(String args[])
{
try
{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");


Connection c=DriverManager.getConnection("jdbc:odbc:shilpi","scott","tiger");

System.out.println("got connection");

Statement stmt=c.createStatement();

String sq="select fname,ssn from empl";

//stmt.executeUpdate(sq);


ResultSet rs=stmt.executeQuery(sq);
System.out.println("hi");

while(rs.next())
{
System.out.println(rs.getString("fname") +" " +rs.getString("ssn"));
}
rs.close();
stmt.close();
c.close();
}
catch(ClassNotFoundException e1)
{}
catch(SQLException e2)
{}
catch(Exception e3)
{}
}
}
__________________________________________________

if this is working why isnt JSP progream working .... is there a problem with tomcat5.5
0
 
harshgroverCommented:
i dont have access to any of your log files ;)...i saw it in the stacktrace that you attached... :).
0
 
shilpi84Author Commented:
oh.. no i thought u tried my prog on ur system... sorry.. misunderstood
0
 
harshgroverCommented:
no worries...i was just pullin ur leg...i cant code on my machine yet...i would only be able to do it later in the evening :(...that's the reason i am not being able to help enough :(
0
 
shilpi84Author Commented:
i understand...)
0
 
harshgroverCommented:
this might help...

replace your getConnection with this

Connection c=DriverManager.getConnection(url,"scott","tiger");

you seem to be missing the closing quote for scott, and the C for the connection is also in lower case

hope this helps

Harsh
0
 
shilpi84Author Commented:
i'd corrected the typo error before ... didnt help
0
 
shilpi84Author Commented:
i've now lost all hopes ...
it's because of you guys i kept trying with your help for the past 2 days..
i now give up..
i'm switching over to something else... maybe MSAccess.... any suggestions.
0
 
jaggernatCommented:
if you follow programming standards, you can get rid of your problem.
First of all, you should not have any database logic in your jsp. thats a very big flaw!

your jsp should have only the fields and the submit button,
all your database logic to connect to database and retrieve data should be in a java class. when you submit your jsp , call that java class which will fetch the records.


0
 
shilpi84Author Commented:
ok... i made a servlet :


import java.sql.*;
 
public class T {
      static String sql = "select * from empl where column1 = ?";
 
      static String userId = "scott";
      static String password = "tiger";
      static String dbServer = "shilpi1";
      static String dbName = "shilpi";
      
      public static void main(String[] args) throws Exception {
            testMSDriver();
      }
 
      private static void testMSDriver() throws Exception {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://"+ dbServer +";SendStringParametersAsUnicode=false;dataSourceName=SQL2000JDBC;databaseName="+ dbName +";serverName="+ dbServer +";selectMethod=cursor", userId, password);
 
            long start = System.currentTimeMillis();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, "string1");
            ResultSet rs = stmt.executeQuery();
 
            while (rs.next()) {
                   // System.out.println("Result: " + rs.getString(1));
            }
            conn.close();
            System.out.println("Elapsed time for MS driver: " +
                                    (System.currentTimeMillis() - start));
      }
}

upon executing i get the following error in the console:
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\karwals>cd\

C:\>java T
Exception in thread "main" java.lang.ClassNotFoundException: com.microsoft.jdbc.
sqlserver.SQLServerDriver
        at java.net.URLClassLoader$1.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        at java.lang.ClassLoader.loadClassInternal(Unknown Source)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Unknown Source)
        at T.testMSDriver(T.java:18)
        at T.main(T.java:14)

C:\>

nothing works..
JSP Servlet..
but Plain Java works with jdbc odbc.

is it a problem with tomcat?
0
 
jaggernatCommented:
To debug your code , put System.out.println("..") statements in your java class and see if you are able to retrieve the  database fields which you are getting from database.

0
 
jaggernatCommented:
how are you calling the servlet?
0
 
jaggernatCommented:
0
 
shilpi84Author Commented:
i call it as
http://localhost:8080/D/DB
the browser displays
vf S[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
0
 
shilpi84Author Commented:
and when i used this:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");


Connection c=DriverManager.getConnection("jdbc:odbc:shilpi","scott","tiger");

i got the same error
0
 
shilpi84Author Commented:
thanks jaggernat .........i'll try that and get back soon
0
 
suprapto45Commented:
You should not give up that quick. You can only get this *special* experiences by solving your problem.

i may not be able to post any message as I will be away so hopefully others can help you here
0
 
shilpi84Author Commented:
i found out i didnt register oracle in ODBC properly
i registered it in the UserDSN instead of the systemDSN

can anyone please tell me what shoul i fill in the following four fields of the DSN configuration:

Data Source Name:
Description:
User Name:
Server:

the tnsnames.ora file's contents are as follows:
__________________
# TNSNAMES.ORA Network Configuration File: D:\oracle\ora90\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

SHILPI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = shilpi1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = shilpi)
    )
  )

INST1_HTTP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = shilpi1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = MODOSE)
      (PRESENTATION = http://HRService)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

____________________________
0
 
CEHJCommented:
ODBC is unnecessary, will provide greater complexity and inferior performance - leave it alone
0
 
CEHJCommented:
:-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 15
  • 9
  • 7
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now