Re: Ports

"Learning As Much As I Can" <jctown_at_nb.sympatico.ca> wrote in message news:1153670310.067963.179990_at_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).
>

Please remember that the SID of an XE database is ALWAYS XE !!!!!!!!!!! Received on Sun Jul 23 2006 - 15:24:30 CDT