"Learning As Much As I Can" <jctown_at_nb.sympatico.ca> wrote in
news:1153621746.216168.131020_at_m79g2000cwm.googlegroups.com:
> 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?
>
>
From a command window (cmd) similar to when you invoked sqlplus...
lsnrctl status
& paste the results back here.
Ideally this will show both the SID & the port being used.
Received on Sat Jul 22 2006 - 21:57:27 CDT
