Improve company productivity with a Business Account.Sign Up

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

JSP program not retrieving 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?????????

help...
0
shilpi84
Asked:
shilpi84
  • 42
  • 18
  • 10
  • +2
6 Solutions
 
CEHJCommented:
Check your logs for exceptions and make sure there are rows found
0
 
shilpi84Author 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?
0
 
Tomas Helgi JohannssonCommented:
  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
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Tomas Helgi JohannssonCommented:
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
0
 
shilpi84Author Commented:
i checked there . the logs are empty
0
 
Tomas Helgi JohannssonCommented:
Here is the JDBC:ODBC guide : http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/bridge.html

Regards,
  Tomas Helgi
0
 
shilpi84Author Commented:
how can i use classes12.jar??
0
 
shilpi84Author 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........
0
 
Tomas Helgi JohannssonCommented:
Make the classes12.jar available through your CLASS_PATH or the web-app lib environment.

Regards,
  Tomas Helgi
0
 
shilpi84Author Commented:
nothing didnt help i appenend the full path to classes12.jar in the CLASSPATH variable... but still exactly same no output
0
 
CEHJCommented:
Put the jar in WEB-INF/lib
0
 
shilpi84Author Commented:
* appended
0
 
Tomas Helgi JohannssonCommented:
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
0
 
shilpi84Author 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.
0
 
shilpi84Author 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");
0
 
CEHJCommented:
You don't want to be using that driver. Did you put classes12.jar in the directory i mentioned?
0
 
shilpi84Author Commented:
yes yes CEHJ i did i u said but nothing happened
0
 
shilpi84Author Commented:
:(
0
 
Tomas Helgi JohannssonCommented:
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
0
 
Tomas Helgi JohannssonCommented:
I recommend that you use the thin client connection to oracle instead of the odbc connection.

Regards,
  Tomas Helgi
0
 
shilpi84Author 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..
0
 
Tomas Helgi JohannssonCommented:
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
0
 
shilpi84Author Commented:
ok
0
 
shilpi84Author 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
________________________________
0
 
jpolin1Commented:
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.
0
 
Tomas Helgi JohannssonCommented:
Print out the e.getMessage() also.

Regards,
  Tomas Helgi
0
 
CEHJCommented:
Make sure your connection string is correct, and the db is listening on that port
0
 
shilpi84Author 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
0
 
shilpi84Author Commented:
ok Tomas i'm trying that try -- catch thing
0
 
jpolin1Commented:
Just move

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

to the very end of the jsp.
0
 
shilpi84Author 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?
0
 
shilpi84Author Commented:
i tried jpolin1 it didnt work i dont get an error message
0
 
jpolin1Commented:
probably not something with oracle...is the user you are using setup with permissions to read/write to the table?
0
 
shilpi84Author Commented:
yes there's only one user on my computer and it has administrative rights
0
 
jpolin1Commented:
you mean on oracle right? Not your windows account or whatever you do to logon to your computer.
0
 
shilpi84Author Commented:
i meant windows... but when i installed oracle i did set up an account. only one account no other.
0
 
shilpi84Author Commented:
and i left the password field blank while i was installing
0
 
jpolin1Commented:
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.
0
 
CEHJCommented:
See if you can use those same credentials in SQLPlus, with the same query
0
 
shilpi84Author 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??
0
 
CEHJCommented:
Start SQLPlus and log on with that username and password
0
 
shilpi84Author Commented:
i used scott, tiger only....
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
 
CEHJCommented:
Don't test it with that driver - you wouldn't use it
0
 
shilpi84Author Commented:
then which driver should i use?
0
 
CEHJCommented:
The Oracle one
0
 
shilpi84Author 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");
0
 
CEHJCommented:
Yes, use that one
0
 
shilpi84Author Commented:
i tried that ... the driver doesnt load using Class.forName("oracle.jdbc.OracleDriver");
0
 
hoomanvCommented:
Do you get any exception ?
What about  oracle.jdbc.driver.OracleDriver
0
 
shilpi84Author Commented:
no execution when i use oracle.jdbc.driver.OracleDriver
but when using this sun.jdbc.odbc.JdbcOdbcDriver ... only the driver loads
0
 
CEHJCommented:
>>the driver doesnt load using Class.forName("oracle.jdbc.OracleDriver");

Suggests the jar is not in the classpath
0
 
shilpi84Author 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.
0
 
shilpi84Author 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
0
 
shilpi84Author 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
__________________________________________
0
 
shilpi84Author 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?

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
 
CEHJCommented:
>>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
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
 
Tomas Helgi JohannssonCommented:
Data Source Name: shilpi
Description: "Something that describes this connection"
User Name: scott
Server: shilpi1

Regards,
   Tomas Helgi
0
 
shilpi84Author 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
0
 
CEHJCommented:
You don't need to configure ODBC
0
 
shilpi84Author Commented:
what do i need to do then?
0
 
CEHJCommented:
Just use the correct Oracle driver in its normal mode
0
 
shilpi84Author Commented:
i m not getting ur point cehj.please elaborate.
0
 
CEHJCommented:
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
0
 
shilpi84Author Commented:
how do i use Oracle Driver?
0
 
CEHJCommented:
Just load it with Class.forName
0
 
shilpi84Author 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
______________________

0
 
CEHJCommented:
As i just mentioned, ODBC is unnecessary, will provide greater complexity and inferior performance - leave it alone
0
 
shilpi84Author 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?
0
 
CEHJCommented:
Yes - that's an entirely different question
0
 
shilpi84Author 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.
0
 
shilpi84Author Commented:
its the same question
0
 
shilpi84Author Commented:
thanks
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 42
  • 18
  • 10
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now