Using CCI to call stored procedures in Oracle

[evantoliopoulos]

Hi All,

I am using JBoss 3.0.x, and have been trying to call some Oracle stored procedures via a Common Client Interface (CCI) adaptor.

My main problem seems to be one of configuration.

I am using the CciBlackBox CCI package (cciblackbox-tx.rar) that Sun provide. I have written a resource adapator XML file which I have dropped into the deploy directory. It looks like:





CciBlackBoxTx
java:/TransactionManager
<depends optional-attribute-name="ResourceAdapterName">jboss.jca:service=RARDeployment,name=Minerva JDBC LocalTransaction ResourceAdapter
<depends optional-attribute-name="ConnectionManagerFactoryLoaderName">jboss.jca:service=ConnectionManagerFactoryLoader,name=MinervaSharedLocalCMFactory
#
#Wed Aug 15 16:17:29 EDT 2001
BlockingTimeoutMillis=50000
IdleTimeoutMinutes=30
MaxSize=10
CleanupIntervalMinutes=10
MinSize=0
MaxIdleTimeoutPercent=1.0


org.jboss.resource.security.ManyToOnePrincipalMapping

userName=setme
password=tosomething



I am not sure I have got the 'depends' tags right! When I try to get a connection from a connection factory I get the following dumped to the JBoss server.log:

INFO [STDOUT]Initial context lookup of 'java:comp/env/CCIEIS' returns object of type 'org.jboss.resource.adapter.jdbc.JDBCDataSource':
org.jboss.resource.adapter.jdbc.JDBCDataSource@7be8c2
ERROR [STDERR] java.lang.ClassCastException: org.jboss.resource.adapter.jdbc.JDBCDataSource

The stdout line is my own message. I was expecting to get back a ConnectionFactory object from the troubled line:
connectionFactory = (ConnectionFactory) initialContext.lookup("java:comp/env/CCIEIS");

BTW, my jboss.xml for my session bean contains the following:

...<resource-ref>
<res-ref-name>CCIEIS</res-ref-name>
<jndi-name>java:CciBlackBoxTx</jndi-name>
</resource-ref>...

and my ejb-jar.xml contains:

...<resource-ref>
<res-ref-name>CCIEIS</res-ref-name>
<res-type>javax.resource.cci.ConnectionFactory</res-type>
<res-auth>Application</res-auth>
</resource-ref>...

Any help would be greatly appreciated.
Cheers,
Evan

[davidjencks]

There are 2 problems I see.

1. Your RARDeployment depends tag is indicating that you want a minerva-wrapped jdbc 1 driver, not the cci driver. Find out what the display name sun gives the cci driver and make that tag like:

<depends optional-attribute-name="ResourceAdapterName">jboss.jca:service=RARDeployment,name=DISPLAYNAMEHERE

2. I strongly suspect you need some ManagedConnectionFactoryProperties to tell the cci wrapper what db driver, db, etc, to use. Look at the ra.xml with the cci adapter to find out what to set.

3. I don't know if this adapter works well with local tx. You may possibly need to use an Oracle xa driver and the xa ConnectionManagerFactory.

When you get it working, would you contribute the working configuration to our archive?

thanks
david jencks

[evantoliopoulos]

I have got it working,

Thanks David, those tidbits of info got me looking in the right place.

I am attaching the xml files that I am using:
- ccieisoraclebscs-service.xml
- ra.xml

You mention adding the files to archive. Where do I do that?

Cheers,
Evan

[davidjencks]

Thanks! I add the files to cvs. The pooling parameters are explained in ch 3 of the online manual.

Normally you don't change the ra.xml supplied with a resource adapter when you deploy it. Also the ra.xml you supply doesn't include the DriverClass property that you set in the service.xml. The ra.xml also has a "create=true" string at the end.

So..

Is this the ra.xml supplied with the blackbox cci adapter? Are you using it? How?

Is the create=true string needed? should it be in the service.xml?

Is the DriverClass parameter needed? I would expect if it is not in the ra.xml it would cause an error or not be loaded.

Hope you can clarify these minor points.

Thanks
david jencks

[evantoliopoulos]

Hi David,

Yes, these are the xml files that we are using with success.

No, the ra.xml is not as shipped.

Sun ship what they have called 'sample-adapters' which provide a CCI interface but use JDBC to do the work. As a result, the sample adapters should, in theory, be able to talk to a number of databases.

I have only modified two lines in the ra.xml that the sample adapters shipped with. They are:
[WAS] <display-name>CciBlackBoxLocalTx</display-name>
[IS] <display-name>CciBlackBoxTx</display-name>
[WAS] <config-property-value>jdbc:cloudscape:rmi:CloudscapeDB;create=true</config-property-value>
[IS] <config-property-value>jdbc:oracle:thin:@alderon:1521: DB1;create=true</config-property-value>

1. It was not necessary for me change the display name. I should change it back.
2. As you can see the sample stuff has been configured for cloudscape. I changed this for Oracle - our target database.
3. I do not know if the 'create=true' is necessary for Oracle. In any case it works. I have had a quick scan of the sample adpater source code and cannot see where it might be used. I will take it out today and see if it still works. Watch this space for an update.

You mentioned the DriverClass not being specified in the ra.xml. This helps me to understand the following output from JBoss - notice the lines 'trying driver':
12:54:35,185 INFO [CciBlackBoxTx] DriverManager.getConnection("jdbc:oracle:thin:@alderon:1521: DB1")
12:54:35,185 INFO [CciBlackBoxTx] trying driver[className=org.hsqldb.jdbcDriver,org.hsqldb.jdbcDriver@2ab653]
12:54:35,185 INFO [CciBlackBoxTx] trying driver[className=oracle.jdbc.driver.OracleDriver,oracle.jdbc.driver.OracleDriver@253de4]
12:54:36,276 INFO [CciBlackBoxTx] getConnection returning driver[className=oracle.jdbc.driver.OracleDriver,oracle.jdbc.driver.OracleDriver@253de4]

Very polite of JBoss!

How do I specify the DriverClass in the ra.xml?

Finally, to answer your question of two replies ago, it was not necessary to use the XA drivers. I have not tried these.

BTW, I was going to attach the unmodified zip archive of Sun's sample adapter source code and rar's - including three versions of ra.xml [only 240Kb]. However, the web interface for the forum does not seem to be accepting attachements at the moment. It is also available from their site [somewhere :(] - free. Note, I am not using their source as is. I could not get their code to recognise my Oracle stored procedure via JDBC and have made a slight mod the CciInteraction.java code. It now works but I am not convinced they had a bug. Possibly I have made an assumption about possible values for Catalog and Schema parameters to the execute method. Unfortunately, thorough doco for the sample adapters does not exist.

Cheers,
Evan

[davidjencks]

You should be able to use their ra.xml unmodified, the url you supply in the ConnectionFactoryLoader config should override that in the ra.xml (In fact IMO they should not be supplying a url in ra.xml. I think it is highly misleading as to the purpose of the config params).

I think the trace you show is DriverManager looking at all the Drivers to find one that works for the URL you supplied. I don't recall seeing that before--I wonder how they got that logged!

I'm not sure we want to redistribute a modified sun adapter-I haven't looked into their licensing for one thing. Can you supply a diff or patch of your changes?

Thanks!
david jencks