File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Weird problem ..Using Datasource to get result Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Weird problem ..Using Datasource to get result" Watch "Weird problem ..Using Datasource to get result" New topic

Weird problem ..Using Datasource to get result

Manohar Karamballi
Ranch Hand

Joined: Jul 17, 2001
Posts: 227
Hi All,
I am facing a problem which I am unbale to find root cause. But I think it is because of way I am querying DB.
Here is my approach.
I have singleton class that is responsible for handling all DB queries and is using Datasource.
DS in instance variable and I am initializing it in static Initializer.
Now I have to query depending upon userType and no of columns in result may vary..
//query to get 4 columns
else if(user_type=2)
//query to get 6 columns
and closing result sets. statements and connections in that order.
Problem is most of the times query executes fine and occasionaly it throws some DB related exception like No more data available or outof bound exception.
Is there ne problem in having singleton class and data source as instance variable ?
Also I am getting connection from data source for each query and closing thereafter.
Right now only one user is active so there is no question of simultaneous access.
Pls drive me out . Also pls give ur feedback regarding pros and cons in above approach.
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
it's not quite the way I'd architect the application . It could be that connections are being used by two different threads. Try making the methods syncronized & see if that helps.
Manohar Karamballi
Ranch Hand

Joined: Jul 17, 2001
Posts: 227
Hi Lee,
I appreciate your feedback. But I have taken care in my archi regarding synchronization issues.
See... Connection,ResultSet and Statements are local varibles. All local variables are thread safe. So as far as Connection,ResultSet and Statements are concerned there should not be ne synchronization issues. Now only left out member is Datasource.
Now is there any chance that dataSource.getConnection() returning same connection to two threads simultaneously?
I don't think so as datasource takes care not to give same connection for both threads.
Secondly , Now I am testing from single system which affectively means only one query @ a time and hence one thread @ a time.
So problem might be something else.
What do u say?
Correct me if I am wrong.
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
ok, getConnection() is not the problem.
tell us more about the exceptions? are they SQLExceptions? what is the DB? are you using the correct driver etc?
Manohar Karamballi
Ranch Hand

Joined: Jul 17, 2001
Posts: 227
Thanks once again,
Coming back to discussion,
yes there SQL Exceptions..
I am using JRUN 4.0 Datasource which pools DB Connections.
DB is MSSQL Server 2000.
In stored procedure that I am invoking ,
I am returning different result sets consisting of different number of columns depending on some input paramters.
and trying to retrieve them in my Java program conditionally.
I think problem is not with logic in SP as it works fine more often than not.
To throw some light , I am giving error log that I got..
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver]Column index 5 is out of ran
at macromedia.jdbc.base.BaseExceptions.createException(Unknown Source)
at macromedia.jdbc.base.BaseExceptions.getException(Unknown Source)
at macromedia.jdbc.base.BaseResultSet.validateColumnIndex(Unknown Source)
at macromedia.jdbc.base.BaseResultSet.getInt(Unknown Source)
at com.slk.odds.dblayer.SQLProcessor.getQuotationSearchResults(SQLProcessor.ja
at com.slk.odds.dblayer.SQLProcessor.getSearchResults(
at com.slk.odds.presentation.commands.CmdSearch.exec(
at com.slk.odds.presentation.controller.ODDSControllerS.service(ODDSController
at javax.servlet.http.HttpServlet.service(
at jrun.servlet.ServletInvoker.invoke(
at jrun.servlet.JRunInvokerChain.invokeNext(
at jrun.servlet.JRunRequestDispatcher.invoke(
at jrun.servlet.ServletEngineService.dispatch(
at jrun.servlet.http.WebService.invokeRunnable(
ODDSCustomerControllerS - service(): Command Action Set in the CmdOutVBean ===> sales/
at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(

Also @ some other time I got error log saying
java.sql.SQLException: [Macromedia][SQLServer JDBC Driver] No more data available

Hope this helps to point what went wrong.
John Dunn
Ranch Hand

Joined: Jan 30, 2003
Posts: 1108
what about userType?? Can you put trace in the exception handler that prints out type & id of user to maybe shed light on who the culprit is here??

"No one appreciates the very special genius of your conversation as the dog does."
kundi kx

Joined: Feb 03, 2003
Posts: 10
The second Exception message looks funny. Here is a link of discussion on how new SP fixed a "No More data" Exception.
Hope it can shed some light. Let us know if it helps.
Manohar Karamballi
Ranch Hand

Joined: Jul 17, 2001
Posts: 227
Hi Kundi,
Thanks a lot for ur help. I think I resolved that problem after installing SP. So far I did not come across exception. Let u know if that happened again.
Thanks once again
I agree. Here's the link:
subject: Weird problem ..Using Datasource to get result
It's not a secret anymore!