• 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

Connection Busy with another result hstmt Error - updated (SOLVED WITH JTDS)

 
Ranch Hand
Posts: 99
Postgres Database Flex Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

It was suggested to me in another forum that I could change my ResultSet Type flags from TYPE_SCROLL_SENSITIVE to TYPE_FORWARD_ONLY in order to speed up performance. I did that. Then my code broke all over the place. Generally it broke because I was calling ResultSet.first() in a lot of the code. I fixed that. Once I did, I got errors in my code stating that Connection is busy with results of another hstmt.

I tracked through the code and found a few places where I call the DB and ask for some ResultSet which is now TYPE_FORWARD_ONLY and then did not close my ResultSet and Statement. The reason I have not been concerned about closing them was due to the fact that I use a Singleton Connection object which returns a single Connection for a thread and then closes that Connection just before the Thread dies. Still, it's better to close them so I'm glad this change made me aware that I was keeping so many open. As I closed those ResultSets I made it further and further in the execution of my code until I got stuck.

In one part of my code, I open a Connection and create a ResultSet. From that ResultSet, I build an array of information to be returned to the caller. Some of the information I have in that array needs to come from unrelated queries so I call the methods that will return me the data I need from those queries. This is where I run into a problem with the Connection being busy:



As you can see, there are a lot of ResultSet objects in all these methods I'm calling which use the same Connection (returned by my Singleton for this sessionID) I'm using in this ResultSet. I can't close the ResultSet because I'm looping over it in my while. Is there an easy way I can solve this?

Best,
Al
 
Al Johnston
Ranch Hand
Posts: 99
Postgres Database Flex Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I found a blog that says you can switch to the SQL Native drivers and it will solve the problem I'm having. I removed my ODBC data source and then set up a new one with SQL Native selected as the driver. Then I tested my data source and all is well. I then ran my java code and now I get: java.sql.SQLException [Microsoft] [SQL Native Client] Shared memory provider: No process is on the other end of the pipe.

Can anyone help me?
 
Al Johnston
Ranch Hand
Posts: 99
Postgres Database Flex Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Changed the driver... no help. Now getting "Connection is busy with results for another command" error.
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
where do you get the connection from? is it shared between threads?
 
Al Johnston
Ranch Hand
Posts: 99
Postgres Database Flex Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here is the latest Connection object ...




There is a socket (PlatformServer) which accepts a connection from a client (PlatformClient). The server then creates a thread for the client. The thread calls an object (PlatformController) which calls many objects. Those object all call CPAWinsDataConnection cd = CPAWinsDataConnection.getInstance() and they create a Connection as Connection con = cd.getConnection("session_id"); So, it's the same connection being used by a single thread until the thread dies - but a different Connection for each thread.

Any thoughts on what I can change to make this work and not sacrifice the speed increases I get from using the best ResultSet.TYPE for my needs?
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Firstly you should try to get a better Driver that the JDBC-ODBC bridge. It isn't designed for production use.
Secondly the synchronisation only ensures one thread gets a connection at a time, it doesn't ensure that the connnection is used by one thread at a time. Multiple threads could be serving requests from the same sessionID concurrently and try to run queries on the same connection at the same time.

Using a ConnectionPool would be much better, and allow you to ignore the sessionID



There are plenty of connection pools available such as the popular DBCP and would result in the same performance but open (potentially) a fraction of the connections.
 
Al Johnston
Ranch Hand
Posts: 99
Postgres Database Flex Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the insight. I am happy to change the driver and had intended to before we go live.

It is not possible for more than one Thread to have more than one Connection at this time. The Server "PlatformServer" starts the Socket connection. When the socket is started, the caller asks for a new iframe by sending a command to the PlatformServer. Once that happens, PlatformServer adds the sessionID passed by that caller to a Hashtable PlatformServer manages. Every time a new process is asked for by the Client Socket, PlatformServer checks the Hashtable to see if there's already a PlatformController for this sessionID. PlatformController is created once for each Thread and all Threads have to have a unique sessionID.

This all worked and has been in testing for several weeks as we have been setting up the GUI (web based) which works with this Server. That is, it worked until I changed my flags to TYPE_FORWARD_ONLY.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
FYI I'm still focusing on the possibility of a Connection inadvertently being shared between threads at the same time.

So does this mean that the session ID is actually a request ID? otherwise it could still be associated with multiple requests from a single client and cause this problem.
I'm not sure where your sessionID comes from, but if it is not unique then this could also cause threads to collide.

I am still wondering why the connection needs to be stored against the sessionID. ConnectionPooling is the standard solution and omits this problem (assuming connections aren't allowed to leak)
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In your initial post, you have the ResultSet open between lines 2 and 75. Between those lines you don't have any explicit references to another Statement or another ResultSet, but you're calling a lot of methods which might do that. So, are they? Some JDBC drivers won't allow you to have more than one ResultSet open per Connection. Others don't mind as long as each ResultSet came from a different Statement. It would be helpful to find out whether that difference makes a difference to your code.
 
Al Johnston
Ranch Hand
Posts: 99
Postgres Database Flex Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Paul,

Thanks to everyone for the help.

Paul, I put in my comments in the code that I posted that each of those methods do in fact call the same Connection object with the same sessionID and therefore get back the same Connection. Each of those methods then creates a ResultSet. When the ResultSet flag was set to TYPE_SCROLL_INSENSITVE, this was not a problem. But, when I changed to TYPE_FORWARD_ONLY, I had the hang up.

I fixed it by changing the data driver. I am now using JTDS which took care of everything.

Thanks again for your help.

Best,
Al
 
Paul Clapham
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Al Johnston wrote:Paul, I put in my comments in the code...



Oh, sorry, after all these years of programming I don't read other people's comments any more. Code can be relied upon but comments can't be. Anyway, it's good you got the problem fixed.
 
I'm THIS CLOSE to ruling the world! Right after reading this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic