Accessing Oracle without EJB

[sselena911]

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

[stevewink]

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

[sselena911]

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

[tdang]

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.

[stevewink]

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.)

[sselena911]

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

[luqi]

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

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




<!-- ==================================================================== -->
<!-- ConnectionManager setup for xa oracle dbs -->
<!-- Thanks to igor fedorenko <[e-mail]> -->
<!-- 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