• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Webapp hang when calling stored procedure

 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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);
}
}
 
Rancher
Posts: 43016
76
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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񕦐
 
After some pecan pie, you might want to cleanse your palatte with this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic