我们在Snowflake中编写了一个存储过程,它在表中插入值并返回主键。我正试图使用它的JDBC驱动程序来调用这个存储过程。
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();
CCD_ 1返回一个CCD_。问题是,这个类有以下用于注册输出参数的实现:
/*
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();
}
正在使用的示例存储过程定义:
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;
$$;
如何使用Snowflake JDBC驱动程序获得存储过程的输出?
结果也与此存储过程相同:
CREATE or replace PROCEDURE testSp()
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
var rs = "Test"
return rs;
$$;
问题就在这里:
callableStatement.executeUpdate();
当您从JDBC调用存储过程时,您并没有执行更新。即使SP正在进行更新,您也在执行查询。
存储过程将为结果返回一行一列。你可以这样检索:
Statement stmt = c.createStatement();
ResultSet rs = stmt.executeQuery("call TEST_JDBC()");
while (rs.next()) {
System.out.println(rs.getString(1));
}
当然,使用准备好的语句可以获得比这更复杂的功能,但是使用executeQuery。