Mysql procedure issue with multiple PREPARE statement

Question

Following is my mysql stored procedure, which I am calling inside java code. I am dynamically checking the table name and limit value. It is working inside mysql commandline.

Issue is when I call this procedure inside java and try to read from result set it gives invalid column exception.

       `CREATE DEFINER=`root`@`localhost` PROCEDURE `top_gainers`(
            IN sExchange VARCHAR (24),
            IN iLimit INT,
            OUT sStatus VARCHAR(10),
            OUT sMessage VARCHAR(40)
        )
        BEGIN

    SET sStatus = '0';

    IF sExchange = '' OR iLimit = ''
    THEN
        SET sStatus = '1';
        SET sMessage = 'Parameter is missing';
    END IF;

    SET @table_test = CONCAT('SHOW TABLES LIKE \'', sExchange,'\'');
    PREPARE stmnt FROM @table_test;
    EXECUTE stmnt;
    DEALLOCATE PREPARE stmnt;
    IF FOUND_ROWS() = 0
    THEN
        SET sStatus = '2';
        SET sMessage = 'Invalid exchange selected';
    END IF;

    IF sStatus = '0'
    THEN
        SET @text = CONCAT('SELECT * FROM ', sExchange, ' ORDER BY CHANGE_PER DESC LIMIT ', iLimit);
        PREPARE stmnt FROM @text;
        EXECUTE stmnt;
        DEALLOCATE PREPARE stmnt;
        IF FOUND_ROWS() = 0
        THEN
            SET sStatus = '3';
            SET sMessage = 'Could not find relevant data';
        ELSE
            SET sMessage = 'Market movers fetched successfully';
        END IF;
    END IF;

END`

Here is the java code ( My table has SYMBOL and DESCRIPTION columns) :

conn = DBConnection.getInstance().getConnection();    
String sQuery = "{CALL top_gainers(?, ?, ?, ?)}";
cstmt = conn.prepareCall(sQuery);
cstmt.setString(1, sExchange); // "NSE"
cstmt.setString(2, sLimit); // "10"
cstmt.registerOutParameter(3, java.sql.Types.VARCHAR);
cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);    
boolean hasResult = cstmt.execute();    
JSONArray jArray = new JSONArray();    
while (hasResult) {    
    ResultSet res = cstmt.getResultSet();    
    sStatus = cstmt.getString("sStatus");
    sMessage = cstmt.getString("sMessage");    
    if (sStatus.equals("0")) {    
        while (res.next()) {
            JSONObject jObj = new JSONObject();    
            jObj.put("symbol", res.getString("SYMBOL"));
            jObj.put("description", res.getString("DESCRIPTION"));    
            jArray.put(jObj);
        }
    }    
    DBConnection.closeResultSet(res);    
    hasResult = cstmt.getMoreResults();
}

Exception "Column 'SYMBOL' not found."

this is my table :

+--------------------+---------------+------+-----+---------------------+-------+
| Field              | Type          | Null | Key | Default             | Extra |
+--------------------+---------------+------+-----+---------------------+-------+
| SYMBOL             | varchar(255)  | NO   | PRI |                     |       |
| DESCRIPTION        | varchar(255)  | YES  |     | NULL                |       |
--------------------------------------------------------------------------------+

Answer 1

Found the issue. My procedure was returning 2 result set, and I was trying to access column 'SYMBOL' on top of first set which only had table name (in case on valid table name)

I added the following lines in java code, and checked the number of columns before accessing values.

ResultSetMetaData rsmd = res.getMetaData();
int columnsNumber = rsmd.getColumnCount();

while (hasResult) { ResultSet res = cstmt.getResultSet();

ResultSetMetaData rsmd = res.getMetaData();
int columnsNumber = rsmd.getColumnCount();

sStatus = cstmt.getString("sStatus");
sMessage = cstmt.getString("sMessage");

if (columnsNumber > 1) {

    while (res.next()) {

    JSONObject jObj = new JSONObject();
    jObj.put(APP_CONSTANT.SYMBOL, res.getString("SYMBOL"));
    jObj.put(APP_CONSTANT.DESCRIPTION, res.getString("DESCRIPTION"));
    jArray.put(jObj);

    }
}

DBConnection.closeResultSet(res);

hasResult = cstmt.getMoreResults();

}