jeudi 17 décembre 2015

Test Mysql Stored Proc code using HSQLDB

I have a stored proc created in Mysql and I am testing it with my Java code and it works fine. I want to test the same proc like the rest of my db code using an in memory hsql database.

My actual proc code is very simple and is as follows

CREATE PROCEDURE p_getUsers         
        ()
        BEGIN       

        SELECT id, first_name
        FROM T_USER;                    

        END

The java method to call this code is as follows

public List<ValidUserVO> getUsers() {
    ProcedureCall procedureCall = currentSession().createStoredProcedureCall("p_getUsers");

    ProcedureOutputs outputs = procedureCall.getOutputs();
    ResultSetOutput resultSetOutput = (ResultSetOutput) outputs.getCurrent();

    List resultSetList = resultSetOutput.getResultList();

    List<UserVO> users = new LinkedList<ValidUserVO>();

    for(int i=0; i<resultSetList.size(); i++){

        Object[] resultSet = (Object[]) resultSetList.get(i);

        if (resultSet == null || resultSet.length != MCTNumberConstants.FOUR) {
            LOG.error("Returned resultSet is null or doesnt have the correct number of parameters");
            continue;
        }

        BigInteger userId = (BigInteger) resultSet[0];
        String firstName = (String) resultSet[1];


        UserVO userVO = new UserVO();
        userVO.setUserId(userId.longValue());
        userVO.setFirstName(firstName);

        users.add(validUserVO);
    }

     return users; 
}

When I try to recreate this proc (or a simplified version in HSQLDB i get an error

Here is the HSQLDB proc I have created

 CREATE TABLE T_USER (
    taskId INTEGER IDENTITY NOT NULL,
    taskName VARCHAR(255) NOT NULL
);  


-- Method to get all valid users
 CREATE PROCEDURE p_getUsers()
 READS SQL DATA DYNAMIC RESULT SETS 1
  BEGIN ATOMIC

  DECLARE result SCROLL CURSOR WITH HOLD WITH RETURN FOR SELECT * FROM T_USER;
  OPEN result;  

  END;
/;

The exception I get when I run my junit test against this HSQLDB proc is as follows

java.lang.ClassCastException: org.hibernate.result.internal.UpdateCountOutputImpl cannot be cast to org.hibernate.result.ResultSetOutput
    at com.mct.dao.database.impl.UserDetailsDAOImpl.getUsers(UserDetailsDAOImpl.java:41)
    at com.mct.dao.database.TestUserDetailsDAO.testGetUsers_Success(TestUserDetailsDAO.java:19)

Can anybody offer any guidance on what I need to do to return a proper ResultSetOutput using HSQLDB?

Thank you in advance Damien

Aucun commentaire:

Enregistrer un commentaire