Exception : Callable statement did not return any value

Question

I have written a java code to call a stored procedure which returns some data. Following is the code -

  CallableStatement callableStatement = null;
    List<OutputDTO> outputDTOList = new LinkedList<>();
    ResultSet rs = null;
    String query = "{call Get_DailyCampaignReachReport (?,?,?,?,?,?,?,?,?)}";
    try {
        callableStatement = connMan.getReportingDbConnection().prepareCall(query);
        Integer[] data = inDTO.getCampaignId().toArray(new Integer[inDTO.getCampaignId().size()]);

        callableStatement.setDate(1, new Date(inDTO.getStartDate().toDate().getTime()));
        callableStatement.setDate(2, new Date(inDTO.getEndDate().toDate().getTime()));
        callableStatement.setArray(3, connMan.getReportingDbConnection().createArrayOf("integer", data));
        callableStatement.setInt(4, inDTO.getNetworkId());

        callableStatement.registerOutParameter(5, java.sql.Types.DATE);
        callableStatement.registerOutParameter(6, java.sql.Types.INTEGER);
        callableStatement.registerOutParameter(7, java.sql.Types.INTEGER);
        callableStatement.registerOutParameter(8, java.sql.Types.BIGINT);
        callableStatement.registerOutParameter(9, java.sql.Types.BIGINT);

        boolean results = callableStatement.execute();
        while (results) {
            rs = callableStatement.getResultSet();
            while(rs.next()){
                OutputDTO outputDTO = new OutputDTO();
                outputDTO.setDate(new DateTime(rs.getDate(1).getTime()));
                outputDTO.setCampaignId(rs.getInt(2));
                outputDTO.setNetworkId(rs.getInt(3));
                outputDTO.setUniques(rs.getInt(4));
                outputDTO.setTotal(rs.getInt(5));
                outputDTOList.add(outputDTO);
            }
            results = callableStatement.getMoreResults();
        }
        collector.setOutputList(outputDTOList);
    } catch (SQLException e) {
        throw new InternalErrorException("runDayReport {" + query + "} - ", e);
    } finally {
        try {

            if (null != rs) {
               rs.close();
            }

            if (null != callableStatement) {
                callableStatement.close();
            }

        } catch (SQLException ee) {
            throw new InternalErrorException("Free Resources : runDayReport {"
                    + query + "} - " + ee);
        }
    }

"Get_DailyCampaignReachReport" is the name of the stored procedure. Now when I execute this code I get an exception "Callable statement did not returned any value." This happens when the execute method is called on callablestatement. But I am not able to understand why this is happening. Can anyone help me understand where I am making a mistake? Following is how the stored procedure looks in the DB.

   Schema |             Name             | Result data type |                                                                                  Argument data types                                                                                   |  Type  | Volatility |  Owner   | Language |                                                                                                                                  Source code                                                                                                                                   | Description
--------+------------------------------+------------------+----------------------------    ------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+------------+----------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------  -------------------------------------------------------------------------------------------   ------------------+-------------
    public | get_dailycampaignreachreport | SETOF record     | start_date date, end_date date, p_campaign_id integer[], p_network_id integer, OUT sqldate date, OUT campaign_id integer, OUT network_id integer, OUT uniques bigint, OUT total bigint | normal | volatile   | postgres | plpgsql  |                                                                                                                                                                                                                                                                               +|
    |                              |                  |                                                                                                                                                                                        |        |            |          |          | DECLARE                                                                                                                                                                                                                                                                       +|
    |                              |                  |                                                                                                                                                                                        |        |            |          |          | where_campaign_id text;                                                                                                                                                                                                                                                       +|
    |                              |                  |                                                                                                                                                                                        |        |            |          |          | in_values varchar default '';                                                                                                                                                                                                                                                 +|
    |                              |                  |                                                                                                                                                                                        |        |            |          |          | BEGIN                                                                                                                                                                                                                                                                         +|
    |                              |                  |                                                                                                                                                                                        |        |            |          |          | IF p_campaign_id NOTNULL THEN                                                                                                                                                                                                                                                 +|
    |                              |                  |                                                                                                                                                                                        |        |            |          |          | FOR i IN 1..array_upper(p_campaign_id, 1) LOOP                                                                                                                                                                                                                                +|
    |                              |                  |                                                                                                                                                                                        |        |            |          |          | in_values := in_values || p_campaign_id[i] || ',';                                                                                                                                                                                                                            +|
    |                              |                  |                                                                                                                                                                                        |        |            |          |          | END LOOP;                                                                                                                                                                                                                                                                     +|
    |                              |                  |                                                                                                                                                                                        |        |            |          |          | in_values   := substring(in_values FROM 1 FOR character_length(in_values) - 1);                                                                                                                                                                                               +|
    |                              |                  |                                                                                                                                                                                        |        |            |          |          | where_campaign_id := ' campaign_id IN (' || in_values || ')' ;                                                                                                                                                                                                                +|
    |                              |                  |                                                                                                                                                                                        |        |            |          |          | END IF;                                                                                                                                                                                                                                                                       +|
    |                              |                  |                                                                                                                                                                                        |        |            |          |          | RETURN QUERY EXECUTE 'SELECT sqldate,campaign_id,network_id,users,total FROM campaign_uniques_daily WHERE sqldate BETWEEN ' || quote_literal(start_date) || ' AND ' || quote_literal(end_date) || ' AND network_id = ' || p_network_id || ' AND ' || where_campaign_id || ' ';+|
    |                              |                  |                                                                                                                                                                                        |        |            |          |          | END;

Have a look at [an example of using CallableStatement with OUT parameters](http://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-out-parameter-example/). — Rahul, Nov 28 '13 at 05:22
Hey I checked the article but how do I manage the looping. I wont be getting a single row from the database but multiple row. — user1305398, Nov 28 '13 at 05:32
You are misinterpreting the return value of `execute` and `getMoreResults`. Also depending on the type of stored procedure, values might actually be returned through the `CallableStatement` itself, instead of as a `ResultSet`. — Mark Rotteveel, Nov 28 '13 at 12:18

score 1 · Answer 1 · edited May 23 '17 at 11:56

1

You are not using a result parameter, that is

{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}

but if you were to use that form, then your stored procedure might return a cursor.

edited May 23 '17 at 11:56

Community

1
1

answered Nov 29 '13 at 02:46

Elliott Frisch

198,278
20
158
249

Exception : Callable statement did not return any value

1 Answers1