2 Replies Latest reply on Mar 6, 2003 12:52 PM by Tom Halvorsen

    Configuring an Oracle 8i Datasource

    Bob Donley Newbie

      I'm haveing an extream amount of difficulty configuring a datasource for an Oracle database. Does anyone have detailed instructions on how to do this. Also, once the datasource is configured, how can it be tested easily?

        • 1. Re: Configuring an Oracle 8i Datasource
          Tom Halvorsen Newbie

          Having pproblems to post the reply

          • 2. Re: Configuring an Oracle 8i Datasource
            Tom Halvorsen Newbie

            Hi, I have just been able to create a DataSource for Oracle,
            and I hope this will help you. I have an application that both uses CMP and SQL to write/read data. We are using CMP for writing/updating and SQL for reading.

            for CMP's in the classes/META-INF directory you find the jbosscmp-jdbc.xml file. Here the datasource to use for the CMP-bean is set to:

            <jbosscmp-jdbc>

            java:/jdbc/gabds
            <datasource-mapping>Oracle8</datasource-mapping>


            <enterprise-beans>


            <ejb-name>Tadr</ejb-name>
            <create-table>false</create-table>
            <remove-table>false</remove-table>
            <read-only>false</read-only>
            <table-name>TADR</table-name>

            <cmp-field>
            <field-name>kAdrID</field-name>
            <column-name>kAdrID</column-name>
            and so on.

            the datasource is => java:/jdbc/gabds

            if you are using SQL and getting a connection like this:

            public static Connection getConnection() {
            MaConst divConst = null;
            DataSource ds = null;
            Context jndiCntx = null;
            Connection con = null;

            try {
            divConst = MaConst.getInstance();
            jndiCntx = new InitialContext();
            ds = (DataSource) jndiCntx.lookup(divConst.getEjbDSName());
            con = ds.getConnection();
            } catch (NamingException ne) {
            throw new MaProgrammingEx(MaSystemEx.EXS_NO_CTX, ne);
            } catch (SQLException se) {
            throw new MaProgrammingEx(MaSystemEx.EXS_NO_CTX, se);
            } finally {
            divConst = null;
            jndiCntx = null;
            ds = null;
            }
            return con;
            }

            The method divConst.getEjbDSName() returns => java:/jdbc/gabds from a configuration file.

            Now the JBoss part. In the Jboss/..../docs/example/jca/ directory you find the file oracle-service.xml. This file shall be copied to the deploy directory together with your
            .ear file containing your application.

            I have modified the oracle-service.xml file to fit my DataSource name and the destination of my oracle server. As you will se my sid is MATEST1, yours might be orcl or whatever. Here is my oracls.service.xml file:

            <?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-->


            jdbc/gabds



            <config-property name="ConnectionURL" type="java.lang.String">jdbc:oracle:thin:@159.162.42.233:1521:MATEST1</config-property>
            <!--

            Here are a couple of the possible OCI configurations.
            For more information, see http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/java.920/a96654/toc.htm

            <config-property name="ConnectionURL" type="java.lang.String">jdbc:oracle:oci:@youroracle-tns-name</config-property>
            or
            <config-property name="ConnectionURL" type="java.lang.String">jdbc:oracle:oci:@(description=(address=(host=youroraclehost)(protocol=tcp)(port=1521))(connect_data=(SERVICE_NAME=yourservicename)))</config-property>

            Clearly, its better to have TNS set up properly.
            -->
            <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">GABA2900</config-property>
            <config-property name="Password" type="java.lang.String">GABA2900</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-->


            3
            <!-- attribute name="MaxSize">50</attribute Dette var default-verdi, satt ned til 25 -->
            6
            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






            Be aware of that the datasource shall be put like this
            jdbc/gabds
            without the "java:" part in this file.

            At last remember the jdbc driver. I use the classes12.zip, you can also use the ojdbc14.jar if you are using jdk1.4.

            The jdbc driver shal be copied into the lib directory of JBoss.

            I have got it to work this way, but I can se there might be some tuning to do. If I connect using a client it works
            fine. If I wait some minutes and try again it seams that some timeout has occured. I have not have time to envestigate this, maybe you can help me on this part.

            good luck, tom.halvorsen@statkart.no