Configuring an Oracle 8i Datasource

[bdonley]

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?

[haltom]

Having pproblems to post the reply

[haltom]

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, [e-mail]