• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

WAS 5 - Error while connecting to Oracle datasource:

 
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all,
I have configured a datasource with the Oracle thin driver. I am am able to retrieve a datasource object with the
Context.lookup("RESOURCE-REF") method, but as soon as I do a getConnection(), I get the following error:
java.sql.SQLException: DSRA9002E: ResourceException with error code null: javax.resource.spi.ResourceAllocationException:
Caught ResourceException thrown during creation of the ManagedConnection.
at com.ibm.ejs.j2c.poolmanager.FreePool.createManagedConnectionWithMCWrapper(FreePool.java:1138)
at com.ibm.ejs.j2c.poolmanager.FreePool.createOrWaitForConnection(FreePool.java:897)
at com.ibm.ejs.j2c.poolmanager.PoolManager.reserve(PoolManager.java:1065)
at com.ibm.ejs.j2c.ConnectionManager.allocateMCWrapper(ConnectionManager.java:560)
at com.ibm.ejs.j2c.ConnectionManager.allocateConnection(ConnectionManager.java:374)
at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:205)
at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:296)
at org.apache.jsp._conntest._jspService(_conntest.java:79)
at com.ibm.ws.webcontainer.jsp.runtime.HttpJspBase.service(HttpJspBase.java(Compiled Code))
.... [Rest of Stack Trace snipped for brevity]
I have my implementation class name correct, its: oracle.jdbc.pool.OracleConnectionPoolDataSource.
I have my resource-ref (in web.xml) mapped correctly. I have my authentication set to "CONTAINER". I have created a
authentication alias in WebSphere with the correct username and password. I have mapped this authentication alias to the
datasource correctly as well, and have set it to container managed.
My class path for the driver looks like:
${ORACLE_JDBC_DRIVER_PATH}/classes12.zip
${ORACLE_JDBC_DRIVER_PATH}/classes111.zip
where the ORACLE_JDBC_DRIVER_PATH is also correct. My db server is up. My JDBC URL is also correct. I have tested using
DriverManager.getConnection with the same URL, username and password and the same jars! from the same m/c and it connects
with no problems.
This problem is making me go nuts.
I have absolutely no clue as to why the stuff isn't working.
Any help/tips/suggestions/comments/hints (anything) will be deeply appreciated,
Thank you,
Tarun Ramakrishna
 
Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
make sure you remove all non obligatory datasource properties (all except URL, user, password). did the trick here
 
Tarun Ramakrishna Elankath
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Sander,
I did that but I got the same error. Finally in the height of desperation, I made everything component-managed: Set up a component-managed alias, set the data-source to component managed, made my res-auth to SERVLET, passed user-name-password in getConnection() and voila, IT WORKED on re-starting my web-app.
I feel terrible about wasting 2 days over this. Needless to say, this experience hasn't left me with a good impression of WebSphere. (its my first time with websphere) It really sucks in this configuration area. I never had these kind of problems whatsoever with containers such as weblogic, jboss or even tomcat.
Thanks anyway,
Tarun

Originally posted by sander hautvast:
make sure you remove all non obligatory datasource properties (all except URL, user, password). did the trick here

 
Ranch Hand
Posts: 144
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK here's how to access a data source via a servlet or other code. I apologize in advance for the long post.
Building and Accessing a Data Source

The RedBook has no information on how to build a data source correctly. We were lucky enough to have a contact at IBM who sent us some detailed instructions. What follows is that set of IBM instructions for creating a data source. I’ll add in my own comments as it becomes appropriate.
[My comments will look like this]

To use a DataSource from a servlet:
1) Set up the DataSource
1.a) Create a JAAS Authentication Entry to hold the login info for the DataSource:
In the Server perspective, double click on your server in the Server Configuration view to launch the Server Configuration editor.
Click on the security tab and click on Add next to JAAS Authentication Entries
The alias can be anything. Set the User ID and Password as appropriate for your database.
[Notice that this is where the userID and password goes. We use this to carry that info around in the application]
1.b) Create the JDBC Provider:
Click on the Data source tab and click on Add next to JDBC Provider list
Select the appropriate Database type and JDBC Provider Type. Click on Next
Give the provider a name and click on Finish
[Notice that there are TWO places that say “JDBC Provider Type’. One under Node Settings and one under Server Settings. You want to add this to the one that says “Server Settings”.
Click “Next”.
Now give your new provider a name. I called it Oracle JDBC Provider. Notice that the path to the classes12.zip file uses an environment name. We’ll fix that in step 1.d]
1.c Create the DataSource:
With the JDBC Provider that you just created selected, click on Add next to Data source defined...
Select the type of the JDBC Provider and select Version 5 data source. Click on Next.
You can leave all the defaults on the Modify Data Source page, except at the bottom, select the alias that you created for both authentication alias entries.
Remember the JNDI name, you will need it Later.
[I called my JNDI name jdbc/dsoirwe]
[At this point IBM’s instructions are missing a step. Once you click “Next” you will get a dialog box with a bunch of variables. The URL variable is a required variable. For my database the URL is
jdbc:oracle:oci8:@oirwe
Enter that data and click “Finish”]
1.d Set up the Oracle JDBC Driver Path:
WSAD doesn't know where you put classes12.zip, so you have to tell it. Click on the Variables tab and scroll through the Defined Variables until you come to ORACLE_JDBC_DRIVER_PATH. Edit the variable. The value should be the directory where you put classes12.zip. Click on OK
[In my case it was at C:\oracle\ora81\jdbc\lib
Be sure to click the Save button to save all your changes]
Close the Server Configuration Editor and save the changes. The DataSource is set up.
2) Code the servlet
When you look up the DataSource, use the no argument constructor for InitialContext (this is important. It allows you to use java:comp/env)
For Instance:

3) Create the Resource Reference to connect the name in the Servlet code with the DataSource. This also tells WebSphere that the Web Application will be using the DataSource, and so it makes all the needed classes available to the WebApp.
Launch the Web Deployment descriptor Editor for your Web Application and click on the References tab
Click on Resource (at the top right under the big References) and click on the add button
[WARNING: This is easy to miss. Be sure you selected “Resources” from the top of the page]
Click on the resource reference in the big box to change its name. The name you use should be the last part of the name that the Servlet used to look up the DataSource. for the code above, the correct name is jdbc/myds (note that there is no leading /)
In the Details section, click on Browse next to Type and select javax.sql.DataSource
In the WebSphere Bindings section set the JNDI name to the JNDI name of the DataSource that you created. (jdbc/ds1 is the default, but go back and check if you aren't sure) Again, there is usually no leading "/" in the JNDI name.
Close and save the Web Deployment Descriptor. You should be good to go.

When you start the server, you should see a message that says something like:
[2/27/03 12:01:27:733 PST] 66669f04 ResourceMgrIm I WSVR0049I: Binding Data source 1 as jdbc/ds1
If there is a problem with the DataSource definition, an error message will appear in the log at this point.
[The previous comment lets us know that yes, we must restart the server again. Luckily, this is one of the few times when we need to restart.]
Here is a complete servlet that uses a DataSource. You should be able to use it on your DataSource by just changing the Table/Field names:

[ May 15, 2003: Message edited by: Greg Brouelette ]
[ May 15, 2003: Message edited by: Greg Brouelette ]
[ May 15, 2003: Message edited by: Greg Brouelette ]
 
Tarun Ramakrishna Elankath
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a million Greg!
I am taking a printout of your post right now and giving it to some other guys here who are facing the same problem.
Man, I can't believe that something this simple has soooo many steps.

Thanks again for your time. Much appreciated!
 
Greg Brouelette
Ranch Hand
Posts: 144
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You're very welcome. I went through exactly the same pain you're going through about 4 months ago. Since I had the answer already written up in some documentation I wrote for our company it was easy to copy/paste it here.
I'm glad it helped.
 
reply
    Bookmark Topic Watch Topic
  • New Topic