5 Replies Latest reply on Jul 17, 2003 5:19 AM by kar2000

    Connection to oracle 8

    amit_danwar Newbie

      Dear All,
      I am new user of JBoss,i am able to call a java class file from .jsp page. Now i want to make database connection with oracle. Can anyone pl guide me,how to make database connection with oracle through a java class.
      Please help me.
      Regards
      Amit.

        • 1. Re: Connection to oracle 8
          Larry Newbie

          Amit,

          Your question is not a Jboss question. It is a standard Java (J2SE) question. Since you don't know that , here is an example taken from a servlet that illustrates the technique. Please refer to a standard Java reference such as "Beginning Java 2" by Ivor Horton.


          import java.sql.*;
          ...
          Object[][] values=new Object[][];
          ...
          try {
          Class.forName("oracle.jdbc.driver.OracleDriver");
          Connection con = DriverManager.getConnection("jdbc:oracle:thin:ACCOUNT/PASSWORD@localhost:1521:ORCL");
          PreparedStatement prepStmt = con.prepareStatement(selectString);
          ResultSet rs = prepStmt.executeQuery();
          int i=0;
          while(rs.next()) {
          for (int j=0;j<4;j++) values[j]=rs.getString(j+1);
          i++;
          }
          } catch (Exception ex) {
          System.out.println("Unable to execute query " +
          ex.getMessage());
          }

          Hope this helps.
          Larry

          • 2. Re: Connection to oracle 8
            amit_danwar Newbie

            Larry thanks a lot for your reply,
            Do i need to write any descriptor and if yes then can you pl help me out in writing a descriptor.

            Best Regards
            Amit.

            • 3. Re: Connection to oracle 8 - OracleDS not bound
              amit_danwar Newbie

              Hi All,

              I am using the following files

              1. JSP file
              In this .jsp file i am callling method of a java file
              2. Java Class file
              I am using the following code
              try{
              InitialContext ctx = new InitialContext();
              javax.sql.DataSource ds= (javax.sql.DataSource) ctx.lookup("java:/OracleDS");
              conn = ds.getConnection();
              }

              3. oracel-ds.xml

              <?xml version="1.0" encoding="UTF-8"?>

              <!-- ===================================================================== -->
              <!-- -->
              <!-- JBoss Server Configuration -->
              <!-- -->
              <!-- ===================================================================== -->

              <!-- $Id: oracle-ds.xml,v 1.1.2.2 2003/04/01 04:51:12 d_jencks Exp $ -->
              <!-- ==================================================================== -->
              <!-- Datasource config for Oracle originally from Steven Coy -->
              <!-- ==================================================================== -->



              <local-tx-datasource>
              <jndi-name>OracleDS</jndi-name>
              <connection-url>jdbc:oracle:thin:@youroraclehost:1521:yoursid</connection-url>

              <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
              <user-name>x</user-name>
              y
              <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
              </local-tx-datasource>



              This is givin me the error OracleDS not found...
              Pl suggest if i am missing something.
              Please Help
              Regards
              Amit

              • 4. Re: Connection to oracle 8 - OracleDS not bound
                Larry Newbie

                The example I gave earlier works without a descriptor since all the connection info is in the code. Indeed, it doesn't even need JBoss.

                The method you proposed will also work. I think your lookup has the wrong context however. It should be "java:comp/env/jdbc/OracleDS".

                Also, I use an application descriptor to connect the jndi name to the oracle-ds. (Since you are mapping a jndi to a resource of the same name, I think JBoss can make the connection automatically. Maybe someone else knows the answer to that.) For example:

                ---Source code---

                private String dbName = "java:comp/env/jdbc/OracleDS";
                ...
                InitialContext ic = new InitialContext();
                DataSource ds = (DataSource) ic.lookup(dbName);
                con = ds.getConnection();
                ...

                ---descriptor---

                <resource-ref>
                <res-ref-name>jdbc/OracleDS</res-ref-name>
                <resource-name>jdbc/OracleDS</resource-name>
                </resource-ref>
                ...

                ---oracle-ds.xml

                What you have looks good.

                • 5. Re: Connection to oracle 8 - OracleDS not bound
                  kar2000 Newbie

                  Well, I have a doubt in this regard - is there any means of finding in the jboss 3.0.7 server side and not in the Oracle 8 database server side,

                  how many connections are there in the pool,

                  how many connections are in use and

                  how many connections are being returned at a given point of time?