• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Weird problem ..Using Datasource to get result

 
Manohar Karamballi
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 925
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 925
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1108
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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??
 
kundi kx
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 227
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic