This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes JDBC and the fly likes Webapp hang when calling stored procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Webapp hang when calling stored procedure" Watch "Webapp hang when calling stored procedure" New topic
Author

Webapp hang when calling stored procedure

Masrudyn Main
Greenhorn

Joined: May 13, 2009
Posts: 20
I have a webservice running on glassfish that is calling an oracle stored procedure. Clients make about 30-50 request per second on that webservice.

The problem is that only 2-3 calls can be made to the webservice before glassfish hangs (can't even login to the console). When I do not make the stored procedure call, everything runs fine.

Is there something wrong with my code:

public void callStoredProcedure(String param)
{
Connection cnx = null;
try
{
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/mandplbs");
cnx = ds.getConnection();
}
catch(NamingException ex)
{
ex.printStackTrace();
}
catch(SQLException ex)
{
ex.printStackTrace();
}

if(cnx != null)
try
{
String sql = "{call mandplbs_test_proc(?)}";
CallableStatement stmt = cnx.prepareCall(sql);
stmt.setString(1, param);
boolean results = stmt.execute();
do
{
if(results)
{
ResultSet rs = stmt.getResultSet();
rs.close();
}
System.out.println();
results = stmt.getMoreResults();
} while(results);
stmt.close();
}
catch(SQLException ex)
{
ex.printStackTrace();
logger.debug("Uncaught exception", ex);
}
}
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42031
    
  64
Does the call to the stored proc ever return?

Can you make more simultaneous calls to that stored proc in some other environment, say from a desktop application, or the DB's command line interface?


Ping & DNS - my free Android networking tools app
Masrudyn Main
Greenhorn

Joined: May 13, 2009
Posts: 20
The stored proc only does 1 thing: write a timestamp to a database table.

I can see the record being inserted but my webservice does not execute the next line after calling the stored procedure method.

What could be wrong?
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
You never close your connection.

In regards to the line:


1. How many open connections are you allowed to have?
2. Will .getConnection wait synchronously until it can get one, or will it eventually throw an error?
Masrudyn Main
Greenhorn

Joined: May 13, 2009
Posts: 20
hi,

thanks for your reply. I was using the connection pool in glassfish. Anyway, after much troubleshooting, I found out that the problem was due to glassfish 2.1 not supporting jdk6 on AIX. Here's the thread which I posted on glassfish forum:
http://forums.java.net/jive/thread.jspa?messageID=350608񕦐
 
GeeCON Prague 2014
 
subject: Webapp hang when calling stored procedure