5 Replies Latest reply on Jul 15, 2002 1:16 AM by Camus

    Sample files for Oracle Connection

    Camus Newbie

      Hi ALL,

      Environment:
      JBoss 3.0
      Oracle8i
      JDK 1.3

      Could someone help post sample files (like standardjboss.xml, standardjbosscmp-jdbc.xml, login-config.xml, oracle-service.xml, ...etc.) for connection to Oracle8i and using datasource, connection pooling features ?

      Many thanks,
      Camus

        • 1. Re: Sample files for Oracle Connection
          forest&bird Newbie

          1. modified %jboss_dist%/server/default/conf/login-config.xml, add a "application-policy" node such as this:

          <application-policy name = "OracleDbRealm">

          <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule"
          flag = "required">
          <module-option name="dsJndiName">java:/OracleDS</module-option>
          <module-option name = "principal">scott</module-option>
          <module-option name = "userName">scott</module-option>
          <module-option name = "password">tiger</module-option>
          <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=OracleDS</module-option>
          </login-module>

          </application-policy>


          2.copy oracle-service.xml from %jboss_dist%/docs\examples/jca to %jboss_dist%/server/default/deploy and edit some place:
          <?xml version="1.0" encoding="UTF-8"?>

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



          <!-- ==================================================================== -->
          <!-- ConnectionManager setup for Oracle dbs -->
          <!-- Build jmx-api (build/build.sh all) and view for config documentation -->
          <!-- Thanks to Steven Coy -->
          <!-- ==================================================================== -->




          <!-- Include a login module configuration named OracleDbRealm.
          Update your login-conf.xml, here is an example for a
          ConfiguredIdentityLoginModule:

          <application-policy name = "OracleDbRealm">

          <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule" flag = "required">
          <module-option name = "principal">scott</module-option>
          <module-option name = "userName">scott</module-option>
          <module-option name = "password">tiger</module-option>
          <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=OracleDS</module-option>
          </login-module>

          </application-policy>

          NOTE: the application-policy name attribute must match SecurityDomainJndiName, and the
          module-option name = "managedConnectionFactoryName"
          must match the object name of the ConnectionManager you are configuring here.
          -->
          <!--comment out this line if you want component managed security or want
          to use the default values in the ManagedConnectionFactoryProperties -->
          OracleDbRealm

          <depends optional-attribute-name="ManagedConnectionFactoryName">
          <!--embedded mbean-->


          OracleDS



          <config-property name="ConnectionURL" type="java.lang.String">jdbc:oracle:thin:@ps-sv047:1521:orcl</config-property>
          <config-property name="DriverClass" type="java.lang.String">oracle.jdbc.driver.OracleDriver</config-property>
          <!--set these only if you want only default logins, not through JAAS-->
          <config-property name="UserName" type="java.lang.String">scott</config-property>
          <config-property name="Password" type="java.lang.String">tiger</config-property>
          <!-- -->




          <!--Below here are advanced properties -->
          <!--hack-->
          <depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper



          <depends optional-attribute-name="ManagedConnectionPool">
          <!--embedded mbean-->


          4
          250
          5000
          15
          <!--criteria indicates if Subject (from security domain) or app supplied
          parameters (such as from getConnection(user, pw)) are used to distinguish
          connections in the pool. Choices are
          ByContainerAndApplication (use both),
          ByContainer (use Subject),
          ByApplication (use app supplied params only),
          ByNothing (all connections are equivalent, usually if adapter supports
          reauthentication)-->
          ByContainer



          <depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager

          <depends optional-attribute-name="JaasSecurityManagerService">jboss.security:name=JaasSecurityManager

          java:/TransactionManager

          <!--make the rar deploy! hack till better deployment-->
          jboss.jca:service=RARDeployer







          3. I think it is no necessary to modify the web.xml or the jboss-web.xml for a war file. however the content of jboss-web.xml will be as this:

          <?xml version="1.0" encoding="UTF-8"?>
          <!DOCTYPE jboss-web
          PUBLIC "-//JBoss//DTD Web Application 2.3//EN"
          "http://www.jboss.org/j2ee/dtds/jboss-web_3_0.dtd">

          <jboss-web>

          <resource-ref>
          <res-ref-name>jdbc/OracleDS</res-ref-name>
          <jndi-name>java:/OracleDS</jndi-name>
          </resource-ref>

          </jboss-web>


          4. the test code shoud like this:

          Context ctx = null;

          try {
          ctx = new InitialContext();
          Context aJNDIContext = new InitialContext();
          DataSource ds = (DataSource) aJNDIContext.lookup( "java:/OracleDS" );

          Connection conn = ds.getConnection();
          Statement stmt = conn.createStatement ();
          ResultSet rset = stmt.executeQuery ("select ename from emp");
          while (rset.next ())
          out.println (rset.getString (1));
          conn.close();
          } catch (NamingException e) {
          out.println("Couldn't build an initial context : " + e);
          return;
          } catch (SQLException e) {
          out.println("DB Access failed : " + e);
          }


          that's all, good luck!!!!!!!!

          • 2. Re: Sample files for Oracle Connection
            forest&bird Newbie

            if the code to get dataSource from jndi like this:
            ---
            DataSource ds = (DataSource) aJNDIContext.lookup( "java:comp/env/jdbc/OracleDS" );

            --

            the boss-web.xml and web.xml are all neet to reference the resource.

            sorry for forget that, Have Fun!

            • 3. Re: Sample files for Oracle Connection
              forest&bird Newbie

              don't forget copy the oracle jdbc driver classes12.zip to default/lib folder

              • 4. Re: Sample files for Oracle Connection
                Camus Newbie

                Thanks for your codes.

                I am able to lookup and get the datasource java:/OracleDS and get the connection using:
                InitialContext iCtx = new InitialContext();
                Context me = (Context) iCtx.lookup("java:comp/env");
                DataSource ds = (DataSource)iCtx.lookup ("java:/OracleDS");
                con = ds.getConnection();

                And I can see that there is a connection binded to Oracle server. However, whenever I use this con instance to do any operation, like:
                con.setAutoCommit(true);
                OR
                con.prepareStatement("select user_name from user_master") ;

                It gives me exception:
                2002-07-15 12:06:40,875 ERROR [STDERR] java.sql.SQLException: Connection handle is not currently associated with a ManagedConnection

                Please HELP !!! Thanks.

                • 5. Re: Sample files for Oracle Connection
                  Camus Newbie

                  Sorry, it should be this:

                  I am able to lookup and get the datasource java:/OracleDS and get the connection using:
                  InitialContext iCtx = new InitialContext();
                  DataSource ds = (DataSource)iCtx.lookup ("java:/OracleDS");
                  con = ds.getConnection();

                  And I can see that there is a connection binded to Oracle server. However, whenever I use this con instance to do any operation, like:
                  con.setAutoCommit(true);
                  OR
                  con.prepareStatement("select user_name from user_master") ;

                  It gives me exception:
                  2002-07-15 12:06:40,875 ERROR [STDERR] java.sql.SQLException: Connection handle is not currently associated with a ManagedConnection

                  Thanks again for help !!!