Hi all

I am pretty new to stored procedures, so any help will be highly appreciated.

We need to get a list of subordinate position ids from a table by specifying a 'manager' position id. This is the approach I have taken.

1: Create a temp. table (this is a real table & will get deleted at the end).
2: Get position ids of employees whose manager position id is the specified id, and then write to the above temp. table.
3: For each of the position id's returned above, call the procedure at 2 & repeat this until the returned result set is empty.
4: Finally execute a SELECT * ... on the temp table & the resultset gets returned.

All but step 4 works fine. I get the result in the temp. table but the procedure returns an empty record set.

Pasted below is the code. I am not sure wat I am doing wrong.

-------------------------------------
/**
* JDBC Stored Procedure DB2ADMIN.PROCEDURE5
* @param strMgr
*/
import java.sql.*; // JDBC classes

public class PROCEDURE5
{
private static Connection con;
private static String strTempTableName;

public static void pROCEDURE5 ( String strMgrPosID,
ResultSet[] rs1 ) throws SQLException, Exception
{
// Get connection to the database
con = DriverManager.getConnection("jdbc:default:connecti on");
PreparedStatement stmt = null;
String sql;

//Create a temp. table
strTempTableName = "BLSTMP" + strMgrPosID;
//sql = "CREATE TABLE DB2ADMIN.? (IDPOS VARCHAR(50) )";
sql = "CREATE TABLE DB2ADMIN." + strTempTableName + " (IDPOS VARCHAR(50) )";
stmt = con.prepareStatement( sql );
//stmt.setString(1, strTempTableName);
stmt.execute();

// Call recursive method to populate with subordinates
populateTmpTable (strMgrPosID);


sql = "CREATE TABLE DB2ADMIN.FUNKY (ABC VARCHAR(10))";
stmt = con.prepareStatement (sql);
stmt.execute();
con.commit();


//Return values from temp table.
sql = " SELECT " + strTempTableName + ".* FROM DB2ADMIN." + strTempTableName + " AS " + strTempTableName + " GROUP BY " + strTempTableName + ".IDPOS";
stmt = con.prepareStatement( sql );
stmt.execute();
con.commit(); //<-- ?
rs1[0] = stmt.getResultSet();
}


private static void populateTmpTable (String strRefPosId) throws SQLException, Exception {
String strSQLInsert = "INSERT INTO DB2ADMIN." + strTempTableName + "(IDPOS) SELECT POSITIONS.IDPOS FROM PUNEET.POSITIONS AS POSITIONS WHERE POSITIONS.POSMGR=?";
PreparedStatement stmtInsert = con.prepareStatement (strSQLInsert);
stmtInsert.setString(1, strRefPosId);
stmtInsert.execute();

String strSQL = "SELECT POSITIONS.IDPOS FROM PUNEET.POSITIONS AS POSITIONS WHERE POSITIONS.POSMGR=?";
PreparedStatement stmt = con.prepareStatement( strSQL );
stmt.setString( 1, strRefPosId );
stmt.execute();
ResultSet objRS = stmt.getResultSet();

con.commit();

while (objRS.next()) {
populateTmpTable (objRS.getString("IDPOS"));
}
}


}

-----------------------------------------------
Again, any help will be highly appreciated. Thanks in advance.

Regards

Puneet
puneetmathur@msn.com