Unable to Call a Snowflake procedure with a Data Governance tool Called Ataccama

Question

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?

It looks like something is not being supported. What is this SP doing? Is it returning a result set? Are you using the JavaScript API? docs.snowflake.net/manuals/sql-reference/… I might suggest contacting Snowflake Support and also Ataccama support to see what Snowflake is returning and whether that is supported on both sides. — Suzy Lockwood
I've seen several of these posts where random tools throw errors about being unable to CALL Stored Procedures. I suspect some don't even try, but inspect the SQL and complain when a keyword is not recognized. Meaning they have to put out a new release to allow for the CALL command. — Hans Henrik Eriksen

Harsh J Harsh J · Accepted Answer · 2020-01-19T10:03:04

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.

Unable to Call a Snowflake procedure with a Data Governance tool Called Ataccama

1 Answers