Stored Procedure Returning Null

Question

This question is similar to: Mysql - Stored procedure OUT variable return null, sadly their solutions did not work for me.

I have a database populated with data about who used my program and when, and I am trying to create a stored procedure in MySql to retrieve all the data and send it to my java servlet, where it will be processed.

This is what I have currently for my Stored Procedure:

CREATE DEFINER=`root`@`localhost` 

PROCEDURE `getTableData`(OUT time VARCHAR(45), OUT fName VARCHAR(45), 
                        OUT lName VARCHAR(45), OUT rVar INT)

BEGIN
    SELECT rqTime, name, lastName, requestVar FROM pdata.userlist;

END

Here is how I call the query in Java:

CallableStatement stmt = null;
ResultSet rs = null;        
String sqlQuery = "{call getTableData (?, ?, ?, ?)}";       
stmt = conn.prepareCall(sqlQuery);

    stmt.registerOutParameter(1, java.sql.Types.VARCHAR);
    stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
    stmt.registerOutParameter(3, java.sql.Types.VARCHAR);
    stmt.registerOutParameter(4, java.sql.Types.INTEGER);

stmt.execute();

When I try to call this stored procedure in Java (I've tried using result sets and the normal getString(), etc) all values return null.

My table design is along the lines of:

Request Time (millis), name, lastName, requestID 

1402341252155, John, Doe, 11

Any help would be greatly appreciated. I feel as though the problem lies in the stored procedure itself, particularly the how I select the variables from the the table and assign them to the "out" parameters.

Thanks!

Have you tried doing prepareQuery rather than prepareCall and just executing it as a query then processing the ResultSet that came back? I do that all the time and it works fine. — Tim B, Jun 13 '14 at 15:39
@Tim you should use `prepareCall` to execute stored procedures. — Luiggi Mendoza, Jun 13 '14 at 15:40
Since they're out parameters, you should use `stmt.getXxx(index)` to retrieve the data, do not use `ResultSet`. — Luiggi Mendoza, Jun 13 '14 at 15:41

score 0 · Accepted Answer · answered Jun 13 '14 at 15:43

0

The problem is that you never set the values for your out parameters in your procedure. Fix your query:

SELECT
    rqTime, name, lastName, requestVar
INTO
    time, fName, lName, rVar
FROM pdata.userlist;
-- Probably you need a WHERE or another statement to make sure you only retrieve 1 row as result
-- Otherwise, this stored procedure will fail

Also, in Java side, you should register the out parameters and retrieve the results directly from CallableStatement, do not retrieve them from a ResultSet.

answered Jun 13 '14 at 15:43

Luiggi Mendoza

85,076
16
154
332

Thanks, this works! Is there any way for me to get the entire table, given that I need a where statement. Also, is there any way for me to retrieve them from a resultSet? – jFram Jun 13 '14 at 18:04
If you can, use a `PreparedStatement` and directly execute your `SELECT` statement. If you must use a stored procedure, then execute the `SELECT` statement directly in your procedure and retrieve the `ResultSet` from the `CallableStatement`. – Luiggi Mendoza Jun 13 '14 at 18:08
I inserted "rs = stmt.getResultSet();" after the execute statement, but it gives me an SQL Exception, saying the result consisted of more than one row. (Also, I do have to use a stored procedure for security purposes). – jFram Jun 13 '14 at 18:14
@jFram I do not see how using a stored procedure increases security of application against a `PreparedStatement`. Anyway, it would be better if you post another question showing your stored procedure and how you're trying to invoke it from Java along with the exceptions. – Luiggi Mendoza Jun 13 '14 at 18:17

score -1 · Answer 2 · edited May 03 '18 at 01:34

public static  List<MonhthyBranchWiseSalesPurchesesDTO> getMonBranWiseDetails(Connection con){

    List<MonhthyBranchWiseSalesPurchesesDTO> monhthyBranchWiseSalesPurchesesDTOList = null;
    ResultSet rset=null;
    CallableStatement proc_stmt = null;
    CachedRowSet cRowSet = null;

    try{        
        MonhthyBranchWiseSalesPurchesesDTO objTarget = new MonhthyBranchWiseSalesPurchesesDTO();
     System.out.println("okkkkhhhh");
        proc_stmt = con.prepareCall("xremit.dbo.p_getBHDMBReport"); 
        //p_getBHDMBReport
        //proc_stmt = con.prepareCall("uaeexdw.dbo.DBA.p_getBHDMBReport");

        rset = proc_stmt.executeQuery();
        System.out.println("after   ------");
        cRowSet = new CachedRowSetImpl();
        cRowSet.populate(rset);

        System.out.println("lll");
        monhthyBranchWiseSalesPurchesesDTOList = (List) ResultSetUtils.getCollection(objTarget, cRowSet);


    }

Stored Procedure Returning Null

2 Answers2