Error calling callablestatement getmoreresults

First time I’ve used Java to call an SP so this is probably going to be a simple issues I’m overlooking.

I’ve got a couple of ways i’m trying to do this and am getting errors on both.

em is an EntityManager that’s declared higher up the class, i’ve inspected it during debugging and the database connection is correct.

The first is using a StoredProcedureQuery which throws the error;

javax.persistence.PersistenceException: org.hibernate.SQLGrammarException: Error calling CallableStatement.getMoreResults

try {
    StoredProcedureQuery query = em.createStoredProcedureQuery("usp_myProc");
    query.registerStoredProcedureParameter(0, Integer.class, ParameterMode.IN);
    query.setParameter(0, id);
    query.execute();
} 
catch (Exception ex) {
    log.error(ex.toString());
}

My second attempt was to try and use a native query (which sounded right when I read the documentation)

try {
    Query query1 = em.createNativeQuery("EXEC usp_myProc ?");
    query1.setParameter(1, id);
    Integer val = (Integer) query1.getSingleResult();
}
catch (Exception ex) {
    log.error(ex.toString());
}

The Procedure has been stripped down to a simple ‘Select 1’ to make sure that’s not a problem.

PROCEDURE [usp_myProc]
-- Add the parameters for the stored procedure here
@id INT
AS
BEGIN
   SELECT 1
END

I’ve had a google around (which is where I get the Native Query code from) but couldn’t get anything to return. Even just a stored procedure call that doesn’t return a value would be a god result for me now.

Thanks for any help you can give.

create or replace PROCEDURE          PROC_INS_RMIS_BNK_VCHER_TABLE (
   P_BUD_YEAR                  VARCHAR2,
   P_DISTRICT_CODE             VARCHAR2,                            —NOT NULL
   P_TRAN_NDATE                VARCHAR2,                            —NOT NULL
   P_TRAN_EDATE                DATE,
   P_BANK_CODE                 VARCHAR2,                             —NOT NULL
   P_DEPOSIT_BANK_CODE         VARCHAR2,                            —NOT NULL
   P_PO_CODE                   VARCHAR2,                            —NOT NULL
   P_ECONOMIC_CODE5            VARCHAR2,                            —NOT NULL
   P_AMOUNT                    NUMBER,
   P_PAN                       NUMBER,
   P_PAYEE_NAME                NVARCHAR2,
   P_PAYEE_ADD                 NVARCHAR2,
   P_PHONE_NO                  VARCHAR2,
   P_TAX_YEAR                  VARCHAR2,                            —NOT NULL
   P_PAYMENT_TYPE              CHAR,                                —NOT NULL
   P_CHEQUE_DRAFT_NO           VARCHAR2,
   P_DATETIME                  DATE,
   P_CREATED_BY                VARCHAR2,                            —NOT NULL
   P_TAX_FLAG                  CHAR,                                —NOT NULL
   P_CLEARANCE_EDATE           DATE,
   P_CLEARANCE_NDATE           VARCHAR2,
   P_VOUCHER_NO            OUT VARCHAR2,

      P_CHEQUE_TYPE               CHAR,
   P_CHE_DRA_ISSUE_BANK        VARCHAR2,
   P_VOUCHER_STATUS            CHAR,                                —NOT NULL
   P_RECORD_LOCK_FLAG          CHAR,                                —NOT NULL
   P_VOUCHER_DATE              DATE,
   P_ADJUST_FLAG               CHAR,                                —NOT NULL
   P_VOUCHER_NDATE             CHAR,                                —NOT NULL
   P_DEPOSIT_SLIP_NO           NUMBER,
   P_COUNTER_CODE              VARCHAR2,                            —NOT NULL

       P_UPDATE_DATETIME           DATE,
   P_UPDATE_BY                 VARCHAR2,
   P_PO_AMDANI_FLAG            CHAR,
   P_DOLLAR_AMOUNT             NUMBER,

       P_UPDATE_NDATE              VARCHAR2,
   P_DEPOSIT_SLIP_AUTO         CHAR,
   P_TRAN_BUD_YEAR             VARCHAR2,
   p_error_code            OUT VARCHAR2                             —NOT NULL
                                       —not null
   )
IS
   v_date                DATE;
   v_check_date          DATE;
   v_count               NUMBER (1);
   v_ret                 PLS_INTEGER;
   v_same_day_check      CHAR (1);
   v_error_code          VARCHAR2 (2500):=null;
   v_SUB_DISTRICT_CODE   VARCHAR2 (250);
   v_MAIN_BANK_CODE      VARCHAR2 (250);
   v_bank_account_no_nep varchar2(250);
   v_sno number;
BEGIN
   SELECT SUBSTR (p_BANK_CODE, 1, 5) INTO v_MAIN_BANK_CODE FROM DUAL;

   P_VOUCHER_NO := NULL;

   v_date := TO_DATE (SYSDATE) — 1;

      begin
        SELECT count(voucher_no)
        INTO v_count
        FROM rmis7172.BANK_VOUCHER
       WHERE     main_bank_code = v_main_bank_code
       AND deposit_slip_no = p_deposit_slip_no;
           if v_count>=1 then 
              v_error_code:=’Bank code and deposit slip no already exists’;
           end if;
   end;

      v_count:=0;   
      BEGIN
                select bank_account_no_nep INTO v_bank_account_no_nep FROM RMIS7172.RMIS_V_BANK
                WHERE BANK_CODE=p_BANK_CODE;
                EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                    v_bank_account_no_nep:=’५२०१४००’;
                    WHEN TOO_MANY_ROWS THEN
                    v_bank_account_no_nep:=’५२०१४००’;
                END ;

                            FOR i IN 1 .. 30
   LOOP
      BEGIN
         SELECT holiday_date
           INTO v_check_date
           FROM S_HOLIDAY_PARAMETER
          WHERE v_date = TO_DATE (holiday_date);

         v_date := v_date — 1;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
            NULL;
      END;
   END LOOP;

   ———————————————————

   IF P_BANK_CODE IS NOT NULL THEN
      IF V_BANK_ACCOUNT_NO_NEP IS NOT NULL
      THEN
      BEGIN
         BEGIN
            SELECT COUNT (*)
              INTO v_count
              FROM rmis7172.RMIS_DAY_CLOSE
             WHERE     bank_code = P_BANK_CODE
                   AND bank_account_no_nep = v_BANK_ACCOUNT_NO_NEP
                   AND tran_edate = TO_DATE (v_date);
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               v_error_code := ‘:Data not found in day close table:’;
         END;
      END;

      — CHECK DAY CLOSE OF SAME DATE

      BEGIN
         SELECT ‘X’
           INTO v_same_day_check
           FROM rmis7172.RMIS_DAY_CLOSE
          WHERE     bank_code = p_bank_code
                AND bank_account_no_nep = v_BANK_ACCOUNT_NO_NEP
                AND from_edate = p_tran_edate;

         v_error_code := ‘:Day Close has already done:’;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;
    END IF;
   ELSE
      v_error_code :=
         ‘:P_BANK_CODE is null or/and P_BANK_ACCOUNT_NO_NEP is null:’;
   END IF;

   IF p_bank_code IS NULL
   THEN
      v_error_code := v_error_code || ‘:Bank is null:’;

      NULL;
   END IF;

      IF P_deposit_slip_no IS NULL
   THEN
      v_error_code := v_error_code || ‘:Deposit slip No is null:’;
   END IF;

   IF P_amount IS NULL
   THEN
      v_error_code := v_error_code || ‘:Amount is null:’;
   END IF;

   IF P_economic_code5 IS NULL
   THEN
      v_error_code := v_error_code || ‘:Revenue Head is null:’;
   END IF;

   IF P_po_code IS NULL
   THEN
      v_error_code := v_error_code || ‘:Paying Office is null:’;
   END IF;

   IF P_PAYEE_NAME IS NULL
   THEN
      v_error_code := v_error_code || ‘:Payee  Name is null:’;
   END IF;

   IF P_payment_type <> ‘A’
   THEN
      IF p_cheque_type IS NULL
      THEN
         v_error_code := ‘:Cheque Type is null:’;
      END IF;

      IF P_CHE_DRA_ISSUE_BANK IS NULL
      THEN
         v_error_code := ‘:Cheque / Draft Issue Bank is null:’;
      END IF;

      IF P_CHEQUE_DRAFT_NO IS NULL
      THEN
         v_error_code := ‘:Cheque / Draft No is null:’;
      END IF;
   END IF;

   IF v_error_code IS NULL
   THEN
      SELECT NVL (MAX (SNO), 0) + 1
        INTO v_SNO
        FROM RMIS7172.BANK_VOUCHER
       WHERE created_by = p_created_by;

      BEGIN
         SELECT sub_district_code
           INTO v_SUB_DISTRICT_CODE
           FROM RMIS7172.RMIS_V_BANK
          WHERE     bank_code = p_bank_code
                AND bank_account_no_nep = v_bank_account_no_nep;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_SUB_DISTRICT_CODE:=04;
      END;

      INSERT INTO rmis7172.BANK_VOUCHER (BUD_YEAR,
                                          DISTRICT_CODE,
                                          SUB_DISTRICT_CODE,
                                          TRAN_NDATE,
                                          TRAN_EDATE,
                                          BANK_CODE,
                                          DEPOSIT_BANK_CODE,
                                          PO_CODE,
                                          ECONOMIC_CODE5,
                                          AMOUNT,
                                          BANK_ACCOUNT_NO_NEP,
                                          PAN,
                                          PAYEE_NAME,
                                          PAYEE_ADD,
                                          PHONE_NO,
                                          TAX_YEAR,
                                          PAYMENT_TYPE,
                                          CHEQUE_DRAFT_NO,
                                          DATETIME,
                                          CREATED_BY,
                                          TAX_FLAG,
                                          CLEARANCE_EDATE,
                                          CLEARANCE_NDATE,
                                          VOUCHER_NO,
                                                                                    CHEQUE_TYPE,
                                          CHE_DRA_ISSUE_BANK,
                                          VOUCHER_STATUS,
                                          RECORD_LOCK_FLAG,
                                          VOUCHER_DATE,
                                          ADJUST_FLAG,
                                          VOUCHER_NDATE,
                                          DEPOSIT_SLIp_NO,
                                          SNO,
                                          COUNTER_CODE,

                                                                                    MAIN_BANK_CODE,
                                          UPDATE_DATETIME,
                                          UPDATE_BY,
                                          PO_AMDANI_FLAG,
                                          DOLLAR_AMOUNT,

                                                                                    UPDATE_NDATE,
                                          DEPOSIT_SLIp_AUTO,
                                          TRAN_BUD_YEAR)
           VALUES (P_BUD_YEAR,
                   P_DISTRICT_CODE,
                   v_SUB_DISTRICT_CODE,
                   P_TRAN_NDATE,
                   P_TRAN_EDATE,
                   P_BANK_CODE,
                   P_DEPOSIT_BANK_CODE,
                   P_PO_CODE,
                   P_ECONOMIC_CODE5,
                   P_AMOUNT,
                   V_BANK_ACCOUNT_NO_NEP,
                   P_PAN,
                   P_PAYEE_NAME,
                   P_PAYEE_ADD,
                   P_PHONE_NO,
                   P_TAX_YEAR,
                   P_PAYMENT_TYPE,
                   P_CHEQUE_DRAFT_NO,
                   P_DATETIME,
                   P_CREATED_BY,
                   P_TAX_FLAG,
                   P_CLEARANCE_EDATE,
                   P_CLEARANCE_NDATE,
                   P_VOUCHER_NO,

                                     P_CHEQUE_TYPE,
                   P_CHE_DRA_ISSUE_BANK,
                   P_VOUCHER_STATUS,
                   P_RECORD_LOCK_FLAG,
                   P_VOUCHER_DATE,
                   P_ADJUST_FLAG,
                   P_VOUCHER_NDATE,
                   P_DEPOSIT_SLIP_NO,
                   v_SNO,
                   P_COUNTER_CODE,

                                      v_MAIN_BANK_CODE,
                   P_UPDATE_DATETIME,
                   P_UPDATE_BY,
                   P_PO_AMDANI_FLAG,
                   P_DOLLAR_AMOUNT,

                                     P_UPDATE_NDATE,
                   P_DEPOSIT_SLIP_AUTO,
                   P_TRAN_BUD_YEAR);

      COMMIT;

      SELECT voucher_no
        INTO P_VOUCHER_NO
        FROM rmis7172.BANK_VOUCHER
       WHERE     main_bank_code = v_main_bank_code
             AND deposit_slip_no = p_deposit_slip_no;
   ELSE
      p_error_code := v_error_code;
   END IF;
END;

Man, this one had me pulling my hair out. I was doing something I generally try to avoid — calling a stored procedure in MySQL from a Spring/Hibernate JPA app. Something like this:

StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("getSomeData");
storedProcedure.registerStoredProcedureParameter("myId", Integer.class, ParameterMode.IN);

storedProcedure.setParameter("in_id", myObj.getId());
storedProcedure.execute();
List<Object[]> rows = (List<Object[]>) storedProcedure.getResultList();

It gets some structured data from the database, and loads it into a list for further processing. So far so good, and it worked fine for several months. Until I tried to add a new feature the other day, calling this proc from within another transaction, and started getting this error:

org.hibernate.exception.GenericJDBCException: Error calling CallableStatement.getMoreResults
Caused by: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed

All the Java methods were wrapped in @Transactional(readOnly=true), so I couldn’t understand why it thought there would be “data modification”. I tried changing the readOnly flag back and forth on all the methods involved, but couldn’t shake it.

Finally I found that the fix is to add READS SQL DATA to the MySQL procedure.

CREATE PROCEDURE getSomeData(in_id int)
    READS SQL DATA
    BEGIN....

Now Hibernate and the MySQL driver can validate that no data will be changed, the error goes away, and I can stop yanking my hair out.

CREATE OR REPLACE FUNCTION GetUser(ipUserId integer)
RETURNS setof users AS $$
BEGIN
  IF ipUserId is null THEN
    return query select * from users A order by modifieddate desc;
  END IF;
  return query select * from users where iduser = ipUserId;
END;
$$ LANGUAGE plpgsql;

    StoredProcedureQuery query = entityManager.createStoredProcedureQuery("GetUser").
            registerStoredProcedureParameter("ipUserId",
                    Integer.class, ParameterMode.IN)
            .registerStoredProcedureParameter("users",
                    List.class, ParameterMode.OUT)
            .setParameter("postId", 1);

 StoredProcedureQuery query = entityManager.createStoredProcedureQuery("GetUser")
                .registerStoredProcedureParameter(1,void.class, ParameterMode.REF_CURSOR)
                .registerStoredProcedureParameter(2,Integer.class, ParameterMode.IN)
                .setParameter(2, ipIdUser);

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults
Caused by: org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.result.internal.OutputsImpl.convert(OutputsImpl.java:79)
    at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:56)
    at org.hibernate.procedure.internal.ProcedureOutputsImpl.<init>(ProcedureOutputsImpl.java:34)
    at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:453)
    at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:404)
    at org.hibernate.procedure.internal.ProcedureCallImpl.outputs(ProcedureCallImpl.java:663)
    at org.hibernate.procedure.internal.ProcedureCallImpl.getResultList(ProcedureCallImpl.java:751)
    ... 21 more
Caused by: org.postgresql.util.PSQLException: A CallableStatement was executed with an invalid number of parameters

3 ответа

Лучший ответ

    String SqlString = "select * from GetUser({0})";

    if (ipIdUser == null )
        SqlString = MessageFormat.format(SqlString, "NULL");
    else
        SqlString = MessageFormat.format(SqlString, ipIdUser);

    LOGGER.info("SqlSting =" + SqlString);

    return entityManager.createNativeQuery(SqlString, User.class)
            .getResultList();

CallableStatement stmt = con.prepareCall("{call SCHEMA.PROCEDURE_NAME (?, ?)}");
stmt.setInt(1, custom_var);
stmt.registerOutParameter(2, OracleTypes.INTEGER);
stmt.execute();

SimpleJdbcCall call = new SimpleJdbcCall(this.jdbcTemplate).withSchemaName(SCHEMA).withProcedureName(PROC);
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("ipUserId", custom_var);
Map out = call.execute(params);

SimpleJdbcCall call = new SimpleJdbcCall(this.jdbcTemplate);
List<Map<String, Object>> rows = call.getJdbcTemplate().queryForList(PROC_STRING, new Object[] { param1, param2 });

List<User> users = entityManager.createStoredProcedureQuery("GetUser")
  .registerStoredProcedureParameter("ipUserId", Integer.class, ParameterMode.IN)
  .setParameter("ipUserId", ipIdUser)
  .getResultList();

Version

• vert.x core:3.4.1
• vert.x jdbc-client:3.4.1
• jdbc driver:net.sourceforge.jtds.jdbc.Driver
• RDBMS: SQLServer 2012

General info

• What is your jdbc driver: http://jtds.sourceforge.net/
• What is your RDBMS server — Microsoft SQLServer 2012
• What is your connection string (no user/passwords please!)
  jdbc:jtds:sqlserver://dbServer:1433;databaseName=xxxx

Context

If I execute a stored proc which does a bunch of inserts into temp tables and other operations before returning a result set, the call to ResutSet.result() returns null. This is due to the fact that response from SQLServer contains multiple responses like updated 2 rows, etc and finally returns the result set.

Ideally, a callable statement when using the driver directly is handled as the below code segment indicates which works. Iterate till callablestatement.getMoreResults() returns a result set at which point a getResultSet() returns one.
Also a call to executeQuery on a CallableStatement for this driver does this logic of iterating through results till a result set is found before returning.

    //Looping till a resultSet is available ignores all other update count responses
           con = DriverManager.getConnection(dbConfig.getString("url"),
                    dbConfig.getString("user"),
                    dbConfig.getString("password"));
            String procSyntax = "{call "+procName+"( ? )}";
            statement = con.prepareCall(procSyntax);
            statement.setString(1,params);
            LOG.info("Statement is : "+statement.toString());
            boolean rsExists = statement.execute();

            //Ignore any other non result set results or update counts from stored proc and wait for a result set
            while(!rsExists && statement.getUpdateCount() != -1){
                rsExists = statement.getMoreResults();
            }

            if(statement.getResultSet()!= null) {
                hand.complete(convertResultSetToJson(statement.getResultSet()));
            }

The workaround I have for this, is to execute a SET NOCOUNT ON on the connection which suppresses any output related to counts, then execute the callablestatement and then execute the SET NOCOUNT OFF before returning the connection back to the pool. That way I am guaranteed to return the resultset as the first result

For example:

 // Call set nocount ON and then off around the call to the stored proc to turn off counts on sqlserver
       jdbcClient.getConnection(conRes -> {
        if (conRes.succeeded()) {
            SQLConnection connection = conRes.result();
            connection.execute("SET NOCOUNT ON", event -> {});
            JsonArray procParams = new JsonArray().add(params);
            String procSyntax = "{call "+procName+"( ? )}";
            connection.callWithParams(procSyntax,procParams,null,queryRes -> {
                populateResultSet(returnFuture, queryRes);
            });
            connection.execute("SET NOCOUNT OFF", event -> {});
            connection.close();
        } else {
            LOG.error(conRes.cause().getMessage());
            returnFuture.fail(conRes.cause());
        }
    });

Do you have a reproducer?

• Link to github project/gist

Steps to reproduce

1. …Create a stored procedure that inserts some data into a temp table
2. …return a select from the temp table at end of procedure
3. …CallableStatement.execute() returns false.
4. …CallableStatement.getResultSET() is null
5. …Calling CallableStatement.getMoreResults() till true and then a call to CallableStatement.getresultSet() returns the result set

Extra

• Anything that can be relevant