wood burning stoves 2.0*
The moose likes JDBC 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


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Weird problem ..Using Datasource to get result" Watch "Weird problem ..Using Datasource to get result" New topic
Author

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..
like
if(user_type=1)
{
//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.
TIA
Manohar
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.
Simon
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.
Thanks
Manohar
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?
Simon
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(SQLProcessor.java:185)
at com.slk.odds.presentation.commands.CmdSearch.exec(CmdSearch.java:220)
at com.slk.odds.presentation.controller.ODDSControllerS.service(ODDSController
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:91)
at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.java:226)
at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:527)
at jrun.servlet.http.WebService.invokeRunnable(WebService.java:172)ODDSCustome
ODDSCustomerControllerS - service(): Command Action Set in the CmdOutVBean ===> sales/
at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:45
at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)

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.
Thanks
Manohar
John Dunn
slicker
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
Greenhorn

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.
http://www.mail-archive.com/jrun-talk@houseoffusion.com/msg06592.html
Hope it can shed some light. Let us know if it helps.
-kundi
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
Manohar
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Weird problem ..Using Datasource to get result
 
Similar Threads
JNDI design question
Problem...Singleton DB Access class
What is “best” approach to create db connection in web application?
Is this good approach to design DB i/f?
Data Access Code