JSP program not retrieving records from oracle

shilpi84
shilpi84 used Ask the Experts™
on

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

help...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Check your logs for exceptions and make sure there are rows found

Author

Commented:
upon debugging i found out that anything after this line:
                          Connection c=DriverManager.getConnection("jdbc:odbc:shilpi","scott","tiger");

is not working ....
why is the connection not getting established?

i checked the services and the following oracle services are runnin:
OracleOraHome90Agent
OracleOraHome90HTTPServer
OracleOraHome90TNSListner
OracleServiceSHILPI

when i posted the query: "SELECT * from empl;" in SQL-PLUS
i got the 4 rows i had entered.

then why is;nt the connection being made?
Database Administrator / Software Engineer
Commented:
  Hi!

Try this

Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@<hostname>:1521:<database name>", "scott", "tiger");

Make sure that your Web-App is using classes12.jar.
You can find OracleDriver in classes12.jar (<ORA_HOME>/jdbc).

Check your tnsnames.ora file for <hostname> and <database name>. For example, if you had a following entry in tnsnames.ora file:

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SOMEHOST)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

you would write:

Connection con = DriverManager.getConnection("jdbc:oracle:thin:@SOMEHOST:1521:XE", "scott", "tiger");

Regards,
  Tomas Helgi
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
To use JDBC:ODBC driver you should write

 // Load the JDBC-ODBC bridge driver
       Class.forName(sun.jdbc.odbc.JdbcOdbcDriver) ;

before createing the connection

Regards,
  Tomas Helgi

Author

Commented:
i checked there . the logs are empty
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
Commented:
Here is the JDBC:ODBC guide : http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/bridge.html

Regards,
  Tomas Helgi

Author

Commented:
how can i use classes12.jar??

Author

Commented:
when i did this

Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@<hostname>:1521:<database name>", "scott", "tiger");


the driver doesnt load anymore........
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Make the classes12.jar available through your CLASS_PATH or the web-app lib environment.

Regards,
  Tomas Helgi

Author

Commented:
nothing didnt help i appenend the full path to classes12.jar in the CLASSPATH variable... but still exactly same no output
Top Expert 2016
Commented:
Put the jar in WEB-INF/lib

Author

Commented:
* appended
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
How does your tnsnames.ora file look like ?
And how did you write the line below ?
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@<hostname>:1521:<database name>", "scott", "tiger");

Regards,
  Tomas Helgi

Author

Commented:
nothing .. but the driver gets loaded if i use
     
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection c=DriverManager.getConnection("jdbc:odbc:shilpi","scott","tiger");

..
the output never comes up .. i even made a program
and used executeUpdate("CREATE TABLE XYZ(name VARCHAR2(30), ssn INTEGER)";
and insert values by using  sql= "INSERT INTO E VALUES('p','1');
      s.executeUpdate(sql);

in that too only the driver loads and when i extract records using  
sql = "SELECT fname, ssn FROM empl";
      ResultSet rs=st.executeQuery(sql);
<TR>
        <TD><% out.print(rs.getString(1)); %></TD>
</TR>

nothing shows up in the browser..
and i checked by using SQL-PLUS
the TABLE XYZ never got created.

Author

Commented:
from tnsnames.ora:
___________________
SHILPI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = shilpi1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = shilpi)
    )
  )
___________________________

Connection c = DriverManager.getConnection("jdbc:oracle:thin:@shilpi1:1521:shilpi", "scott", "tiger");
Top Expert 2016

Commented:
You don't want to be using that driver. Did you put classes12.jar in the directory i mentioned?

Author

Commented:
yes yes CEHJ i did i u said but nothing happened

Author

Commented:
:(
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
Commented:
Thats because you tell the code to load the JDBCodbc driver before creating the connection which
actually loads the driver and connects to the DB.
Did you try to connect directly to the oracle db by using

Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@<hostname>:1521:<database name>", "scott", "tiger");

?
IF not try that and if that works then the ODBC setup which you call shilpi is not setup correctly.
Most likely it is there you should look.

Regards,
 Tomas
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
Commented:
I recommend that you use the thin client connection to oracle instead of the odbc connection.

Regards,
  Tomas Helgi

Author

Commented:
yes... i did try to connect using this:

Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@<hostname>:1521:<database name>", "scott", "tiger");


but when i do this the oracle driver doesnt load..
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
put a try catch statement around the
  Class.forname...
  Connection con...

and print out the error message.
If you get the Driver not found error then the oracle12.jar library
is not in path.

Regards,
  Tomas Helgi

Author

Commented:
ok

Author

Commented:
ok.. i did what u said Tomas:
__________________________________________________
<%@ page session="false" %>
<%@ page import="java.sql.*" %>
<%
try
{

//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Class.forName("oracle.jdbc.OracleDriver");
out.println("jdbc driver loaded");
}
catch(ClassNotFoundException e)
{
  out.print("driver didnt load.....");
}
%>
<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");
Connection c = DriverManager.getConnection("jdbc:oracle:thin:@shilpi1:1521:SHILPI", "scott", "tiger");

out.print("test line");
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("this proves an error while establishing a connection");}
//catch(Exception e){e.getMessage();}
%>
</TABLE>
</CENTER>
</BODY>
</HTML>


_______________________________

now this is what my browser displays:
________________________________
\jdbc driver loaded

displaying all users


this proves an error while establishing a connection
first name       SSN
________________________________

Commented:
put the try-catch around the entire jsp, and in the catch do a e.printStackTrace() and then look at your logs to see the exception.
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Print out the e.getMessage() also.

Regards,
  Tomas Helgi
Top Expert 2016

Commented:
Make sure your connection string is correct, and the db is listening on that port

Author

Commented:
how do i check whether oracle is listening to the port or not?

theses are the 4 services whose start-up type is automatic:
OracleOraHome90Agent
OracleOraHome90HTTPServer
OracleOraHome90TNSListner
OracleServiceSHILPI

but the OracleOraHome90Agent automatically stops even if i start it.
and a message pops up : some services automatically stop when not needed

Author

Commented:
ok Tomas i'm trying that try -- catch thing

Commented:
Just move

}catch(Exception e){
    System.err.println("Ex Msg: " + e.getMessage());
     e.printStackStrace();
}

to the very end of the jsp.

Author

Commented:
nothing ... no matter what i do ... i still get the same output.
i made a console program it writes records to the table in the oracle DB, but     ResultSet rs=st.executeQuery(s);
                                                                                                                    while(rs.next())
....
never executes.
both JSP and console Java program can write but not read from the DB.
and the same problem is on my friend's computer which has exactly the same softwares as mine.

i'm suspecting there's something wrong with oracle?
is it possible?

Author

Commented:
i tried jpolin1 it didnt work i dont get an error message

Commented:
probably not something with oracle...is the user you are using setup with permissions to read/write to the table?

Author

Commented:
yes there's only one user on my computer and it has administrative rights

Commented:
you mean on oracle right? Not your windows account or whatever you do to logon to your computer.

Author

Commented:
i meant windows... but when i installed oracle i did set up an account. only one account no other.

Author

Commented:
and i left the password field blank while i was installing

Commented:
bring up your admin console and make sure that user has full privlidges on the table you created.
Remember that access controls can be controlled on a per-table basis.

Sometimes an admin account can create/drop tables but cannot actually modify data in those tables.
Top Expert 2016

Commented:
See if you can use those same credentials in SQLPlus, with the same query

Author

Commented:
>>
>>bring up your admin console and make sure that user has full privlidges on the table you created.
>>Remember that access controls can be controlled on a per-table basis.
>>
>>Sometimes an admin account can create/drop tables but cannot actually modify data in those tables.
>>

jpolin1: how can i bring up the admin console?


>>See if you can use those same credentials in SQLPlus, with the same query
CEHJ: how can i see that??
Top Expert 2016

Commented:
Start SQLPlus and log on with that username and password

Author

Commented:
i used scott, tiger only....

Author

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
Top Expert 2016

Commented:
Don't test it with that driver - you wouldn't use it

Author

Commented:
then which driver should i use?
Top Expert 2016

Commented:
The Oracle one

Author

Commented:
what statements do i have to use for it ...
hey CEHJ are you talking about this one?

Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@<hostname>:1521:<database name>", "scott", "tiger");
Top Expert 2016

Commented:
Yes, use that one

Author

Commented:
i tried that ... the driver doesnt load using Class.forName("oracle.jdbc.OracleDriver");
Top Expert 2006

Commented:
Do you get any exception ?
What about  oracle.jdbc.driver.OracleDriver

Author

Commented:
no execution when i use oracle.jdbc.driver.OracleDriver
but when using this sun.jdbc.odbc.JdbcOdbcDriver ... only the driver loads
Top Expert 2016

Commented:
>>the driver doesnt load using Class.forName("oracle.jdbc.OracleDriver");

Suggests the jar is not in the classpath

Author

Commented:
ok this is my classpath:
.;C:\Program Files\Java\jdk1.5.0_07\lib\classes;c:\tomcat\common\lib\servlet-api.jar;C:\Tomcat\webapps\abc\WEB-INF\lib\classes12.jar

it is in the classpath but still the driver doesnt load.

Author

Commented:
CEHJ, the oracle driver loaded:) ..
i used this
  Class.forName("oracle.jdbc.OracleDriver");
but the connection is not getting established;
i've put the connection statement:
  Connection c = DriverManager.getConnection("jdbc:oracle:thin:@shilpi1:1521:shilpi", "scott", "tiger");
in a try block.
and it's Exception always gets thrown.
when i put e.printStackTrace() nothing happens.

something weird
this is my catch block for try block of connection stmt.
_________
catch(SQLException e){
    e.printStackTrace();
    //out.print("error in conn.");
}
___________
i commented the "error in conn."
but still whenever i type
http://localhost:8080/abc/e.jsp
i get this output
_________________
jdbc driver loaded

displaying all users


error in conn.
first name       SSN
__________________

i cleared the browser cache tried it in firefox, IE, Opera, restarted tomcat but still the older code gets executed

Author

Commented:
i now copied my e.jsp file and the WEB-INF folder which contains classes\lib\classes12.jar
into a new dir "e" renamed e.jsp to ee.jsp

i now get this error in browser:
____________________________________
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 load class for JSP
      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 load class for JSP
      org.apache.jasper.JspCompilationContext.load(JspCompilationContext.java:598)
      org.apache.jasper.servlet.JspServletWrapper.getServlet(JspServletWrapper.java:147)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:317)
      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

java.lang.ClassNotFoundException: org.apache.jsp.ee_jsp
      java.net.URLClassLoader$1.run(Unknown Source)
      java.security.AccessController.doPrivileged(Native Method)
      java.net.URLClassLoader.findClass(Unknown Source)
      org.apache.jasper.servlet.JasperLoader.loadClass(JasperLoader.java:133)
      org.apache.jasper.servlet.JasperLoader.loadClass(JasperLoader.java:65)
      org.apache.jasper.JspCompilationContext.load(JspCompilationContext.java:596)
      org.apache.jasper.servlet.JspServletWrapper.getServlet(JspServletWrapper.java:147)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:317)
      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
__________________________________________

Author

Commented:
but when i kept refreshing the page an output came:
____________________________
jdbc driver loaded

displaying all users


first name       SSN
___________________________




i changed the catch block to
___________________

catch(SQLException e){
out.print("connection error");    
e.printStackTrace();
   
}________

and now this is the output:
----------------
no driver loaded

displaying all users


connection error
first name       SSN
_____________

sometimes the driver loads sometimes it doesnt what is that about?

Author

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.
Top Expert 2016

Commented:
>>maybe MSAccess.

Definitely not. If you have Oracle, then use it. Start by commenting most of the code out to see if the JSP's running

Author

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

____________________________
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
Commented:
Data Source Name: shilpi
Description: "Something that describes this connection"
User Name: scott
Server: shilpi1

Regards,
   Tomas Helgi

Author

Commented:
it didnt work tomas..
i'm sure there's some problem in setting up the DSN only as this JSP runs on my friends PC(after DSN registration was corrected)

tomas can you please tell me the procedure from scratch.

and when i saw my friend's tnsnames.ora
the host name was "compaq9110" and service name was "xyz"
and he  registered a microsoft oracle driver in the system DSN tab
and these were the configuration settings:
Data Source Name: xyz1
Description:
User Name: scott
Server: xyz

and the classes12.jar was placed in web-inf/lib as you said.

but when i do this on my system with the configuration you told me the connection still doesnt get established
Top Expert 2016

Commented:
You don't need to configure ODBC

Author

Commented:
what do i need to do then?
Top Expert 2016

Commented:
Just use the correct Oracle driver in its normal mode

Author

Commented:
i m not getting ur point cehj.please elaborate.
Top Expert 2016

Commented:
Forget Microsoft - all you need is the Oracle driver working in its normal TCP/IP mode. Make sure that you can use all relevant parts of that connection string outside Java before you use them inside it

Author

Commented:
how do i use Oracle Driver?
Top Expert 2016

Commented:
Just load it with Class.forName

Author

Commented:
ok CEHJ...
i just opened the ODBC from administrative tools and under SYSTEM DSN tab.
i clicked Add>Oracle in OraHome
an Oracle ODBC Driver Configuration dialog opened up.
i put shilpi2 as my DSN and
SHILPI as my TNS Service Name from the drop down list.
and scott as my userID
when i clicked Test Connection
 i got an error dialog box :

_______________________
Unable to connect
SQLState=S1000
[Oracle][ODBC][ORA]ORA-12451: TNS:no listener
______________________

Top Expert 2016

Commented:
As i just mentioned, ODBC is unnecessary, will provide greater complexity and inferior performance - leave it alone

Author

Commented:
done.. :)

i just copied the folder containing the JSP page(which was in tomcat/webapps) into D:\oracle\ora90\Apache\Apache\htdocs
and in the browser i typed : http://localhost/we/e2.jsp

..
but when i type http://localhost:8080/we/e2.jsp i iget a file not found(404) error.
this means tomcat problem?
Top Expert 2016

Commented:
Yes - that's an entirely different question

Author

Commented:
CEHJ reply on this question http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_22031109.html

i'll give u 500...
because you and tomas helgi both helped me solve this and you deserve at least 500.

Author

Commented:
its the same question

Author

Commented:
thanks
Top Expert 2016

Commented:
:-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial