Re: Ports

From: Learning As Much As I Can <jctown_at_nb.sympatico.ca>
Date: 23 Jul 2006 08:58:30 -0700
Message-ID: <1153670310.067963.179990@h48g2000cwc.googlegroups.com>

Rauf Sarwar wrote:
> Learning As Much As I Can wrote:
> > ianal Vista wrote:
> > > "Learning As Much As I Can" <jctown_at_nb.sympatico.ca> wrote in
> > > news:1153610211.028681.65390_at_s13g2000cwa.googlegroups.com:
> > >
> > > > I downloaded and installed the Oracle 10G Express Version on my
> > > Windows
> > > > XP Home PC. I can use sqlplus to connect to my database and query it.
> > > > But I am trying to do the same thing using a JSP.
> > > >
> > > > Without trying to make my first post totally useless, I will try to
> > > > provide some details... for those who have experience in these areas
> > > > (JSP and Oracle).
> > > >
> > > > Here is a portion of my jsp
> > > > --------------------------------------------------
> > > > String queryString = "describe accounting_logs";
> > > > // ---- configure this for your site
> > > > String username = "system";
> > > > String password = "xxxyyy";
> > > > String url = "jdbc:oracle:thin:@localhost:8080:ORCL"; <---- is
> > > > this wrong?
> > > > %>
> > > > <% // --------------- code for the service method --------------
> > > > // Let's see if we got a request
> > > > queryString = request.getParameter ("QUERYSTRING");
> > > > if ((queryString != "") && (queryString != null)) {
> > > >
> > > > try {
> > > > Class.forName("oracle.jdbc.driver.OracleDriver"); <--- or is
> > > > this the problem?
> > > > // Establish Connection to the database at URL with usename and
> > > > password
> > > > con = DriverManager.getConnection(url, username, password);
> > > > // out.println ("Ok, connection to the DB is working.");
> > > > } catch (Exception e) // (ClassNotFoundException and SQLException)
> > > > {
> > > > throw(new UnavailableException(this, "Sorry! The Database didn't
> > > > load!"));
> > > > }
> > > > --------------------------------------
> > > >
> > > > When I execute the JSP (launched under Apache), I get an error message
> > > > triggered by the UnavailableException. What I am thinking is that
> > > > either my Class.forName is wrong or my url is wrong (or both). I
> > > think
> > > > that my PORT should be 1521 but I don't know what the database should
> > > > be... and I can't seem to locate the name for it anywhere.
> > > >
> > > > I am only starting to learn this stuff... the reason I went to Oracle
> > > > was because that's what we use at work and I wanted to be able to talk
> > > > the same language.
> > > >
> > > >
> > > > If anyone can help me I would appreciate it.
> > > >
> > > >
> > >
> > > I am NOT a JAVA person, but I am a DBA who supports Java coders.
> > > IMO, what you have is NOT a JSP.
> > > A JSP is similar to an ASP or a CGI.
> > >
> > > What specifically do you "execute the JSP (launched under Apache)"?
> > > What is the URL?
> > > How did you "register" the JSP with Apache?
> > >
> > > It appears you are simply trying to use Java as client s/w to query
> > > Oracle. In that case you need to use port 1521 or what ever port the
> > > Oracle listener is configured to use.
> > >
> > > >I can use sqlplus to connect to my database
> > > Please cut & paste EXACTLY how you invoked sqlplus, the query & results.
> >
> > Invocation of sqlpus from the command line
> > A query and results
> > J:\>sqlplus
> >
> > SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jul 22 23:21:08 2006
> >
> > Copyright (c) 1982, 2005, Oracle. All rights reserved.
> >
> > Enter user-name: bcjtown
> > Enter password:
> >
> > Connected to:
> > Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
> >
> > SQL> describe accounting_logs
> > Name Null? Type
> > ----------------------------------------- --------
> > ----------------------------
> >
> > DESTINATION NOT NULL VARCHAR2(30)
> > OWNER NOT NULL VARCHAR2(50)
> > ID NOT NULL VARCHAR2(20)
> > SUBMISSION_DATE NOT NULL DATE
> > COMPLETION_DATE NOT NULL DATE
> > PAGES_COMPLETE NOT NULL NUMBER
> > DATA_TYPE NOT NULL VARCHAR2(10)
> > FORMAT NOT NULL VARCHAR2(50)
> > NAME VARCHAR2(200)
> >
> > -----------------------------------
> > Here is the full code for the jsp
> >
> > <%@ page errorPage="error.jsp" import="java.sql.*" %>
> > <%! // --------------- inits for the servlet --------------
> > // The database connection
> > Connection con;
> > // The statement
> > Statement stmt;
> > // The queryString
> > String queryString = "describe accounting_logs";
> > // ---- configure this for your site
> > String username = "######";
> > String password = "######";
> > String url = "jdbc:oracle:thin:@localhost:8080:ORCL";
> > %>
> > <% // --------------- code for the service method --------------
> > // Let's see if we got a request
> > queryString = request.getParameter ("QUERYSTRING");
> > if ((queryString != "") && (queryString != null)) {
> >
> > try {
> > Class.forName("oracle.jdbc.driver.OracleDriver");
> > // Establish Connection to the database at URL with usename and
> > password
> > con = DriverManager.getConnection(url, username, password);
> > // out.println ("Ok, connection to the DB is working.");
> > } catch (Exception e) // (ClassNotFoundException and SQLException)
> > {
> > throw(new UnavailableException(this, "Sorry! The Database didn't
> > load!"));
> > }
> >
> >
> > try {
> > out.println ( "Query: " + queryString + "<BR>" );
> > out.println("<h3>Query Result</h3>");
> > out.println("<table border>");
> > stmt = con.createStatement();
> > ResultSet rs = stmt.executeQuery(queryString);
> > ResultSetMetaData rsMeta = rs.getMetaData();
> > // Get the N of Cols in the ResultSet
> > int noCols = rsMeta.getColumnCount();
> > out.println("<tr>");
> > for (int c=1; c<=noCols; c++) {
> > String el = rsMeta.getColumnLabel(c);
> > out.println("<th> " + el + " </th>");
> > }
> > out.println("</tr>");
> > int numObs = 0;
> > while (rs.next()) {
> > out.println("<tr>");
> > for (int c=1; c<=noCols; c++) {
> > String el = rs.getString(c);
> > out.println("<td> " + el + " </td>");
> >
> > }
> > out.println("</tr>");
> > numObs++;
> > }
> > out.println("</table>");
> > // out.println("<h2>Total results: </h2>" + numObs + "<BR>");
> > }
> >
> > catch (SQLException ex ) {
> > out.println ( "<P><PRE>" );
> > while (ex != null) {
> > out.println("Message: " + ex.getMessage ());
> > out.println("SQLState: " + ex.getSQLState ());
> > out.println("ErrorCode: " + ex.getErrorCode ());
> > ex = ex.getNextException();
> > out.println("");
> > }
> > out.println ( "</PRE><P>" );
> > }
> > }
> > %>
> >
> > <hr>
> > <FORM METHOD=POST ACTION="acc_logs_enter_query.jsp">
> > Enter Query Here: <INPUT TYPE=TEXT SIZE=80 NAME="QUERYSTRING">
> > <INPUT TYPE=SUBMIT VALUE="Submit">
> > </FORM>
> > <hr><pre>Query Examples:
> >
> > SELECT * FROM accounting_logs
> > SELECT id, destination, name, submission_date, completion_date from
> > accounting_logs
> > SELECT id, destination, name submission_date, completion_date from
> > accounting_logs where submission_date >= '2006-03-01 22:00:00'
> > </pre>
> >
> > ----------------------------------
> > The JSP was registered with TomCat by dropping it in the ROOT
> > directory...
> > I launch it by typing the following address into IE
> > http://localhost:7070/acc_logs_enter_query.jsp
> >
> > As I recall, when I installed Oracle, it said that the default port,
> > 1521, was in use so it picked another one... I don't remember what that
> > was... is there some way to find out?

>

> I have not used XE but if it's similar kind of install as SE or EE then
> most likely the problem is with,

>

> "jdbc:oracle:thin:@localhost:8080:ORCL";

>

> After @ it should be HOST:PORT:SID i.e. Hostname (not
> localhost):Listener Port (Typically 1521):Oracle database SID

>

> Replace localhost with actual hostname and 8080 with 1521 and try
> again. Also do as IANAL mentioned in the last post about lsnrctl
> status. This will show you exactly if the Oracle listener is running
> and which port it's listening on and which databases are registered
> with it.

>

> Regards
> /Rauf

I found a free program called JSPMaker and asked it to build me a JSP to do what I wanted to do based on the Oracle table. When it was done, I looked at the JSP code it produced and took the pieces I needed and plugged them back into my original JSP. Here's what I ended up with

---

    // ---- configure this for your site     String username = "user";
    String password = "password";
    String url = "jdbc:oracle:thin:@192.168.0.101:1522:XE";     %>
    <% // --------------- code for the service method --------------     // Let's see if we got a request
    queryString = request.getParameter ("QUERYSTRING");     if ((queryString != "") && (queryString != null)) {

    try {
   Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();     // Establish Connection to the database at URL with usename and password

    con = DriverManager.getConnection(url, username, password); // out.println ("Ok, connection to the DB is working.");

    } catch (Exception e) // (ClassNotFoundException and SQLException)     {
    throw(new UnavailableException(this, "Sorry! The Database didn't load!"));

    }

---

When I dropped this into the ROOT directory of my Tomcat server directory and made sure that the jdbc jar file was in common/lib, it worked perfectly... now that I have a connection that works, I can customize the JSP to be a bit more dynamic.

Thanks for all your help (everyone). Received on Sun Jul 23 2006 - 10:58:30 CDT