Solved

how to retrieve database records from oracle

Posted on 2006-10-19
35
381 Views
Last Modified: 2010-04-01
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
Comment
Question by:shilpi84
  • 15
  • 9
  • 7
  • +2
35 Comments
 
LVL 10

Expert Comment

by:jaggernat
ID: 17769866
Run the query

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

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

0
 

Author Comment

by:shilpi84
ID: 17769877
yes i already did that in sql-plus(oracle) .. it shows the records
0
 
LVL 10

Expert Comment

by:jaggernat
ID: 17769955
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
 
LVL 16

Expert Comment

by:suprapto45
ID: 17770881
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
 
LVL 2

Expert Comment

by:harshgrover
ID: 17771966
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
 

Author Comment

by:shilpi84
ID: 17772749
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
 
LVL 2

Expert Comment

by:harshgrover
ID: 17774969
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
 

Author Comment

by:shilpi84
ID: 17775481
i did that harsh but no luck...
this is the output in IE:

jdbc driver loaded

                                    displaying all users


                                     first name       SSN
0
 
LVL 2

Expert Comment

by:harshgrover
ID: 17775686
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
 

Author Comment

by:shilpi84
ID: 17775857
______________________________________________
<%@ 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
 
LVL 2

Expert Comment

by:harshgrover
ID: 17775897
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
 

Author Comment

by:shilpi84
ID: 17776043
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
 

Author Comment

by:shilpi84
ID: 17776141
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
 
LVL 2

Expert Comment

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

Author Comment

by:shilpi84
ID: 17776193
oh.. no i thought u tried my prog on ur system... sorry.. misunderstood
0
 
LVL 2

Expert Comment

by:harshgrover
ID: 17776276
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
 

Author Comment

by:shilpi84
ID: 17776320
i understand...)
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 2

Expert Comment

by:harshgrover
ID: 17776913
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
 

Author Comment

by:shilpi84
ID: 17777228
i'd corrected the typo error before ... didnt help
0
 

Author Comment

by:shilpi84
ID: 17778653
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
 
LVL 10

Expert Comment

by:jaggernat
ID: 17778791
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
 

Author Comment

by:shilpi84
ID: 17778799
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
 
LVL 10

Expert Comment

by:jaggernat
ID: 17778800
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
 
LVL 10

Expert Comment

by:jaggernat
ID: 17778809
how are you calling the servlet?
0
 
LVL 10

Expert Comment

by:jaggernat
ID: 17778814
0
 
LVL 10

Expert Comment

by:jaggernat
ID: 17778826
0
 

Author Comment

by:shilpi84
ID: 17778844
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
 

Author Comment

by:shilpi84
ID: 17778845
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
 
LVL 10

Expert Comment

by:jaggernat
ID: 17778867
0
 

Author Comment

by:shilpi84
ID: 17778893
thanks jaggernat .........i'll try that and get back soon
0
 
LVL 16

Expert Comment

by:suprapto45
ID: 17779133
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
 

Author Comment

by:shilpi84
ID: 17781587
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
 
LVL 10

Expert Comment

by:jaggernat
ID: 17782234
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 500 total points
ID: 17783174
ODBC is unnecessary, will provide greater complexity and inferior performance - leave it alone
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 17783329
:-)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Owning a franchise can be the dream of a lifetime. It provides a chance for economic growth. You can be as successful as you want.  To make your franchise successful, you need to market it successfully. Here are six of the best marketing strategies …
Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now