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
posted
0
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
posted
0
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
posted
0
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
posted
0
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
posted
0
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."
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
0
. It could be that connections are being used by two different threads. Try making the methods syncronized & see if that helps.