Sample files for Oracle Connection

[camus]

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

[forest&bird]

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

[forest&bird]

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!

[forest&bird]

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

[camus]

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.

[camus]

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