aspose file tools
The moose likes JDBC and Relational Databases 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


Win a copy of Head First Android this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
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
Rancher

Joined: Mar 22, 2005
Posts: 42956
    
  73
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?
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񕦐
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Webapp hang when calling stored procedure
 
It's not a secret anymore!