Call stored procedure and register out parameter using JDBC Driver

Question

We have written a stored procedure in Snowflake that inserts values in a table and returns a primary key. I'm trying to call this stored procedure using its JDBC driver.

final Connection connection = getJdbcTemplate().getDataSource().getConnection();
final CallableStatement callableStatement = connection.prepareCall("{call REWARD.sp_issue_reward(?, ?, ?)}");
callableStatement.setLong(1, reward.getClientSeq());
callableStatement.setLong(2, reward.getUserUniqueId());

callableStatement.registerOutParameter(3, Types.INTEGER); // throws SQLFeatureNotSupportedException

callableStatement.executeUpdate();

The connection.prepareCall returns an instance of SnowflakeCallableStatementV1.class. Problem is that this class has the following implementation for registering for output parameter:

  /*
   The Snowflake database does not accept OUT or INOUT parameters, so the registerOutParameter functions and the get
    functions (which get values of OUT parameters) will remain not implemented)
  */
    public void registerOutParameter(int parameterIndex, int sqlType) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }

A sample stored procedure definition that is in use:

create or replace procedure sp_issue_reward(CLIENT_SEQ float,
                             USER_SEQ float)
    returns float not null
    language javascript
    called on null input
    volatile
    as
    $$
    
        var REWARD_ID = 1;
        var insertStatement = snowflake.createStatement({
                                          sqlText: "INSERT INTO REWARD.REWARD_CPY ("
                                          + "reward_seq, "
                                          + "client_seq, "
                                          + "user_seq) "
                                          + "VALUES (?, ?, ?)",
                                          binds: [REWARD_ID, CLIENT_SEQ, USER_SEQ]
                                        })
                                       .execute();

        return REWARD_ID;
    $$; 

How to get an output of a stored procedure using Snowflake JDBC driver?

Results are same with this stored procedure as well:

CREATE or replace PROCEDURE testSp()
  RETURNS VARCHAR
  LANGUAGE javascript
  AS
  $$
  var rs = "Test"
  return rs;
  $$;

Answer 1

The problem is here:

callableStatement.executeUpdate();

When you're calling a stored procedure from JDBC, you're not executing an update. You're executing a query even though the SP is doing an update.

The stored procedure will return one row with a single column for the result. You can retrieve it like this:

Statement stmt = c.createStatement();
        
ResultSet rs = stmt.executeQuery("call TEST_JDBC()");
        
while (rs.next()) {
    System.out.println(rs.getString(1));
}

You can of course get more sophisticated than this using prepared statements, but use executeQuery.