Start Free Trial

asked on

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

Run the query

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

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

ASKER

yes i already did that in sql-plus(oracle) .. it shows the records

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

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";

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

ASKER

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>
___________________________________________________________________

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

ASKER

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

jdbc driver loaded

displaying all users

first name SSN

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

ASKER

______________________________________________
<%@ 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
_____________________________________________________

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

ASKER

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)
___________________________________________

ASKER

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

i dont have access to any of your log files ;)...i saw it in the stacktrace that you attached... :).

ASKER

oh.. no i thought u tried my prog on ur system... sorry.. misunderstood

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 :(

ASKER

i understand...)

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

ASKER

i'd corrected the typo error before ... didnt help

ASKER

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.

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.

ASKER

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?

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.

how are you calling the servlet?

http://forum.java.sun.com/thread.jspa?threadID=551594&messageID=2696420

also see this

http://www.codecomments.com/archive352-2005-5-494620.html

ASKER

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

ASKER

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

http://forum.java.sun.com/thread.jspa?threadID=585803&messageID=3012726

ASKER

thanks jaggernat .........i'll try that and get back soon

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

ASKER

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)
)
)

____________________________

http://www.openlinksw.com/info/docs/litedoc41/datasource.html#datasourceodbcsetupconfigdsn

http://www.databasejournal.com/features/oracle/article.php/3442661

http://www.wisc.edu/drmt/configureODBC.html

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

:-)