Skip to main content

Hang when calling stored procedure

1 reply [Last post]
masrudyn
Offline
Joined: 2009-04-19

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);
}
}

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Marina Vatkina

Do you close connection and statement in case of an error? Can there be
something in your code that keeps the 'do-while' loop from exiting?

Regards,
-marina

glassfish@javadesktop.org wrote:
> 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);
> }
> }
> [Message sent by forum member 'masrudyn' (masrudyn)]
>
> http://forums.java.net/jive/thread.jspa?messageID=349273
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
> For additional commands, e-mail: users-help@glassfish.dev.java.net
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
For additional commands, e-mail: users-help@glassfish.dev.java.net