1

I have this function that executes a stored procedure on our mssql server

public void addProjectMember(String projectRefNo, Long projectMemberId, Long userId)
{
    logger.info("addProjectMember | BEGIN " + projectRefNo);
    logger.debug("addProjectMember | projectMemberId - user | " + projectMemberId + " - " + userId);
    
    Integer result = 0;
    CallableStatement cal = null;

    try{
        Session session = getHibernateTemplate().getSessionFactory().getCurrentSession();
        SessionImpl sessionImpl = (SessionImpl) session;

        cal = sessionImpl.connection().prepareCall("{call PS_ADD_PRJMEM(?,?,?)}");
        cal.setString(1, projectRefNo);
        cal.setLong(2, projectMemberId);
        cal.setLong(3, userId);
        result = cal.executeUpdate();

    } catch(SQLException e){
        logger.error("Error in calling stored procedure PS_ADD_PRJMEM", e);
    } finally {
        try {
            if (cal != null) {
                cal.close();
            }
        } catch (SQLException e) {
            logger.error("Error in clossing CallableStatement", e);
        }
    }
    logger.info("addProjectMember | END " + result.intValue());
}

The results returns 1, there is no error upon execution and I make sure the stored procedure's parameters are in correct order and data type. When I run the stored procedure manually, it does its job and works normally.

This way of calling stored procedure is commonly used on our project so I am really struggling why this is not working on this time.

Also is there a specific characteristic of a stored procedure that will probably cause this issue, I noticed on the stored procedure that it is calling another stored procedure but I doubt that is causing this issue.

Leroy
  • 352
  • 2
  • 11
  • Don't know java, but `executeUpdate()` sounds like the wrong method to call a stored procedure with. – HoneyBadger Oct 13 '20 at 12:58
  • Oh I forgot to tell, the initial code was execute() which returns boolean but it does not work, I read [link](https://stackoverflow.com/questions/16625656/statement-executesql-vs-executeupdatesql-and-executequerysql) and it does say its ok to use executeUpdate() which returns an integer, but still does not work – Leroy Oct 13 '20 at 13:02
  • 2
    Can you run a trace on the server and see if the SP is being called or if your program is communicating with the server at all? That would at least give you a point to troubleshoot. – Bee_Riii Oct 13 '20 at 13:32
  • Are you actually getting a connection ? via sessionImpl.connection(). Could you debug out the connection and ensure it is valid ? If it is valid should you also be closing the connection ? – mkane Oct 13 '20 at 16:08
  • @Bee_Riii I confirmed it has a connection to the server, I really suspect its due to the stored procedure. Do you have any idea what causes this, Im really suspicious of this stored procedure called inside the stored procedure, it is using 'with' to create a temp table – Leroy Oct 14 '20 at 02:31
  • @Leroy There shouldn't be any problem with a nested stored procedure. When you did the trace did you see in the textdata column the command it is passing through? if it runs when you manually run the proc then the proc isn't the problem. So you need to confirm via a trace that the proc is being called with the correct parameters. Its not just about whether it gets a connection you need to see what it is actually doing with the connection. – Bee_Riii Oct 14 '20 at 07:43
  • @Bee_Riii I traced the stored procedure, and found out there is a business logic involved on what I am doing, it appears the stored procedure needs to be executed twice before it work makes sense since every time I test I run the sp on app and run it again manually. But your advice to test the session if its connecting to server diminished my suspicion for the app defect – Leroy Oct 14 '20 at 08:36

0 Answers0