6 Replies Latest reply on Aug 16, 2002 3:22 AM by kent.lu

    Accessing Oracle without EJB

    Elana Newbie

      Hello, all

      Is it possible to access oracle database without the use of EJB? In other words, if I have JBoss, what/how do I need to configure WITHOUT deplying EJB so that I can run the following code:

      import java.sql.*;
      import javax.naming.*;
      import javax.sql.DataSource;
      import java.util .*;

      public class JDBCTest
      {
      public JDBCTest (){
      InitialContext context = null;
      try {
      context = new InitialContext();
      System.out.println("Created Initial Context "+context);
      for (Enumeration e = context.list(context.getNameInNamespace()); e.hasMoreElements (); ){
      System.out.println(e.nextElement ().toString ());
      }
      DataSource ds = (DataSource)context.lookup("OracleDS");
      System.out.println("Created DataSource "+ds);
      Connection c = ds.getConnection();
      System.out.println("Created Connection "+c);
      String sql = "SELECT * FROM FX_TRADE";
      PreparedStatement ps = c.prepareStatement(sql);
      System.out.println("Created PreparedStatement "+ps);
      ResultSet rs = ps.executeQuery ();
      System.out.println("Executed Query "+rs);
      } catch (Exception e){
      e.printStackTrace ();
      }
      }

      public static void main (String [] args ){
      new JDBCTest ();
      }
      }

      I have the following jndi.properties in the same directory as this class file:

      java.naming.factory.initial=org.jnp.interfaces.NamingContextFactory
      java.naming.provider.url=localhost:1099
      java.naming.factory.url.pkgs=org.jboss.naming:org.jnp.interfaces
      jnp.socketFactory=org.jnp.interfaces.TimedSocketFactory


      As of now I am getting the following output:

      Created Initial Context javax.naming.InitialContext@47858e
      XAConnectionFactory: org.jboss.mq.SpyXAConnectionFactory
      UserTransactionSessionFactory: org.jboss.tm.usertx.server.UserTransactionSession
      FactoryImpl
      RMIXAConnectionFactory: org.jboss.mq.SpyXAConnectionFactory
      topic: org.jnp.interfaces.NamingContext
      queue: org.jnp.interfaces.NamingContext
      jmx:ELANA:rmi: org.jboss.jmx.adaptor.rmi.RMIAdaptorImpl
      ConnectionFactory: org.jboss.mq.SpyConnectionFactory
      RMIConnectionFactory: org.jboss.mq.SpyConnectionFactory
      UserTransaction: org.jboss.tm.usertx.client.ClientUserTransaction
      ejb: org.jnp.interfaces.NamingContext
      invokers: org.jnp.interfaces.NamingContext
      UILXAConnectionFactory: org.jboss.mq.SpyXAConnectionFactory
      UILConnectionFactory: org.jboss.mq.SpyConnectionFactory
      javax.naming.NameNotFoundException: OracleDS not bound
      at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(Stream
      RemoteCall.java:245)
      at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:
      220)
      at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:122)
      at org.jnp.server.NamingServer_Stub.lookup(Unknown Source)
      at org.jnp.interfaces.NamingContext.lookup(NamingContext.java:445)
      at org.jnp.interfaces.NamingContext.lookup(NamingContext.java:429)
      at javax.naming.InitialContext.lookup(InitialContext.java:350)
      at JDBCTest.(JDBCTest.java:16)
      at JDBCTest.main(JDBCTest.java:31)

      Please, any help will be appreciated

      Thank you,
      Elana

        • 1. Re: Accessing Oracle without EJB
          Steve Wink Newbie

          Looking at the error, this suggests that you don't have the Oracle datasource correctly setup inside JBoss. Have you deployed an oracle-service.xml file ( or something similar ) that follows the same sort of lines as the other database-service.xml files, except for Oracle specific stuff. Heres mine, as an example using Oracle 8 with local transactions on JBoss 3.0.1:

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

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



          <!-- ==================================================================== -->
          <!-- New ConnectionManager setup for oracle, based on hsaldb one. -->
          <!-- Build jmx-api (build/build.sh all) and view for config documentation -->
          <!-- ==================================================================== -->



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


          OracleDS


          <config-property name="ConnectionURL" type="java.lang.String">jdbc:oracle:thin:@HOSTNAME:1521:DATABASENAME</config-property>
          <config-property name="DriverClass" type="java.lang.String">oracle.jdbc.driver.OracleDriver</config-property>
          <config-property name="UserName" type="java.lang.String">USERNAME</config-property>
          <config-property name="Password" type="java.lang.String">PASSWORD</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-->


          0
          50
          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)-->
          ByApplication


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

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

          java:/TransactionManager

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



          • 2. Re: Accessing Oracle without EJB
            Elana Newbie

            Hi, Here is my oracle-service.xml file. It is located in the server/default/deploy directory. Our files look identical...

            <?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">yourprincipal</module-option>
            <module-option name = "userName">yourusername</module-option>
            <module-option name = "password">yourpassword</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.
            -->

            <!--uncomment out this line if you are using the OracleDbRealm above -->
            OracleDbRealm


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


            OracleDS



            <config-property name="ConnectionURL" type="java.lang.String">jdbc:oracle:thin:@URL:1521:SID</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">rb</config-property>
            <config-property name="Password" type="java.lang.String">rb</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-->


            0
            50
            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:service=JaasSecurityManager

            java:/TransactionManager

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






            I still haven't been able to connect without an EJB.
            Elana

            • 3. Re: Accessing Oracle without EJB
              Tung Dang Novice

              I guess you can only access on your db without EJB, when your client (servlets, jsp) is deployed on JBoss. This means, first you deploy your oracle-service.xml, and second your client on JBoss and this client can access on your db.

              Look at http://localhost:8082/ (JBoss 3.0) or http://localhost:8080/jmx-console/ (JBoss 3.0.1) for jndi name for your db.

              Hope it helps.

              • 4. Re: Accessing Oracle without EJB
                Steve Wink Newbie

                It looks like your code is looking in the global JNDI namespace, whereas your datasource is probably in the java: namespace. If you have jboss 3.0.1 look at the service=JNDIView MBean in the jboss domain, and have a look at the entire JNDI context to see where your datasource is bound ( and to double check that it did get bound.)

                • 5. Re: Accessing Oracle without EJB
                  Elana Newbie

                  hi,

                  My datasource is bound locally in the java: namespace. I tried accessing it through java:/OracleDS and OracleDS - neither work. I assume that java:/OracleDS does not work because I'm trying to access it from another JVM, and OracleDS does not work because the db is bound locally. So the question following from this is: How do you bind a datasource globally??

                  Thank you
                  Elana

                  • 6. u can accessing the global by this description
                    kent.lu Newbie

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

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




                    <!-- ==================================================================== -->
                    <!-- ConnectionManager setup for xa oracle dbs -->
                    <!-- Thanks to igor fedorenko <ifedorenko@thinkdynamics.com> -->
                    <!-- Build jmx-api (build/build.sh all) and view for config documentation -->
                    <!-- YOU MUST CHANGE THE XidFactoryMBean config (in conf/jboss-service.xml -->
                    <!-- or transaction-service.xml) to this: -->
                    <!--

                    true

                    -->
                    <!-- ==================================================================== -->


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


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

                    <!--hack-->
                    <depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=Minerva JDBC XATransaction ResourceAdapter

                    <!--real attributes-->


                    <config-property name="XADataSourceProperties"
                    type="java.lang.String">URL=jdbc:oracle:thin:@x.x.x.x:1521:db</config-property>
                    <config-property name="XADataSourceClass"
                    type="java.lang.String">oracle.jdbc.xa.client.OracleXADataSource</config-property>
                    <!--set these only if you want only default logins, not through JAAS -->
                    <config-property name="UserName"
                    type="java.lang.String"></config-property>
                    <config-property name="Password"
                    type="java.lang.String"></config-property>
                    <!--
                    Do not try to set transaction isolation level here or you'll get
                    java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction
                    Read committed is a default isolation level for Oracle, so there
                    is not real need to set it here. I'll take a look into it later ...maybe.
                    <config-property name="TransactionIsolation"
                    type="java.lang.String">TRANSACTION_READ_COMMITTED</config-property>
                    -->


                    XAOracleDS



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


                    5
                    50
                    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

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

                    <application-policy name = "InformixXaDbRealm">

                    <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule" flag = "required">
                    <module-option name = "principal">yourprincipal</module-option>
                    <module-option name = "userName">yourusername</module-option>
                    <module-option name = "password">yourpassword</module-option>
                    <module-option name = "managedConnectionFactoryName">jboss.jca:service=XATxCM,name=InformixXaDS</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.
                    -->
                    <!--uncomment out this line if you are using the InformixXaDbRealm above
                    InformixXaDbRealm
                    -->

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

                    java:/TransactionManager