I am using a data governance too called ATaccama and I have created a SP in Snowflake but I am getting below error when I am calling the SP 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?
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.