I am using a data governance tool called Ataccama.
I have created a stored procedure in Snowflake and am attempting to call it from Ataccama. I am getting the below error when I am calling the stored procedure from Ataccama JDBC Sql Execute component. However, I am using latest JDBC driver for SNOWFLAKE : snowflake-jdbc-3.9.2. Your help is much appreciated!
ERROR:
net.snowflake.client.jdbc.SnowflakeSQLException: Statement 'call PII.kc11_search...' cannot be executed using current API.
at net.snowflake.client.jdbc.SnowflakePreparedStatementV1.executeBatch(SnowflakePreparedStatementV1.java:937)
at com.ataccama.dqc.internal.commons.sql.AtcPreparedStatement.executeBatch(AtcPreparedStatement.java:307)
at com.ataccama.dqc.io.jdbc.writer.FlatCommitStrategy.executeBatch(FlatCommitStrategy.java:115)
at com.ataccama.dqc.io.jdbc.writer.BatchWriter.executeBatch(BatchWriter.java:54)
at com.ataccama.dqc.io.jdbc.writer.BatchWriter.flush(BatchWriter.java:98)
at com.ataccama.dqc.tasks.jdbc.execute.SqlExecuteQueryProcessor.finish(SqlExecuteQueryProcessor.java:106)
at com.ataccama.dqc.tasks.jdbc.execute.SQLStepInstanceBase$SimpleProcessingStrategy.run(SQLStepInstanceBase.java:249)
at com.ataccama.dqc.tasks.jdbc.execute.SQLStepInstanceBase.run(SQLStepInstanceBase.java:136)
at com.ataccama.dqc.processor.internal.runner.ComplexStepNode.runNode(ComplexStepNode.java:69)
at com.ataccama.dqc.processor.internal.runner.RunnableNode.run(RunnableNode.java:28)
at com.ataccama.dqc.commons.threads.AsyncExecutor$RunningTask.run(AsyncExecutor.java:131)at java.lang.Thread.run(Thread.java:745)
[NOTE: using - Call PII. Kc11_seach().]
Any ideas or otherwise beneficial recommendations?
For reference, the docs for calling stored procedures via Snowflake's SQL API are here. Here's the documentation for Ataccama's Workflow and Scheduler Execute SQL task.
The Snowflake JDBC driver does not currently support executing any SQL statements that may return a value as part of its PreparedStatement::executeBatch(…) call that Attacama is using under the hood. This is because the batch call handling cannot process returned values from one or more of their multiple executed SQL queries.
Since a CALL PROCEDURE() may return a value, the driver considers it as a statement that may generate a result and throws the unsupported statement error.
To call a procedure via Attacama, use a different component that does not use JDBC's PreparedStatement::executeBatch(…) calls underneath.
Here's the documentation for Ataccama's Read SQL Result Workflow Task, which mentions output parameters which support returning results from a stored procedure.