Help on Servlet using Stored Porceduresss

[mdazam]

Please guide me here...
Can I call 3 stored procedure one after other ?
Idea here is once this servlet retrive form data, it calls stored procedure1 using half the data of form to run it, then calls stroed procedure2 and uses other half of data of form to execute. And finaly on 3rd stored procedure I'm passing argument and trying to return resultset.

my code ..........

public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException,IOException{
String family = request.getParameter("family");
.....
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection connection = null;
connection = DriverManager.getConnection(".....");
// Calling Stored Procedure 1
String sp1 = "{call SP1 (?,?)}";
CallableStatement cs1 = connection.prepareCall(sp1);
cs1.setString(1,"save");
.......
ResultSet rs1= cs1.executeUpdate();

// Calling Stored Procedure 2
String sp2 = "{call SP2 (?,?)}";
CallableStatement cs2 = connection.prepareCall(sp2);
cs2.setString(1,"save");
.......
ResultSet rs2= cs2.executeUpdate();

// Calling Stored Procedure 3
String sp2 = "{call SP2 (?,?)}";
CallableStatement cs = connection.prepareCall(sp2);
cs.setString(1,"query");
.......

ResultSet rs3= cs2.executeQuery();
while(rs3.next())
{
rs3.getString(1) // how to retiver this value to display ???
}

 

[sedj]

You can call multiple statements and result sets on one connection, but there are some rules you should apply.

1) Only ever use one statement on one connection at a time.
2) Similarly, only use one result set on one statement at a time.
3) If you need to "nest" statements, then you should use several connections for each statement.

If you are using many connections you should look into connection pooling.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[sem]

Sedj, could you please elaborate with need to use different connections for different statement? I suppose that in many cases it's even dangerous, especially when making updates, as the same java procedure would see inconsistent data from different transactions, to say nothing about possible deadlocks!

Regards, Dima

http://seminihin.narod.ru