CODEWITHSUNDEEP
snowflake-cloud-data-platform
KevR
KevR

Reputation: 131

Snowflake JavaScript SP does not support all methods described in official documentation

I am trying to create a JavaScript stored procedure in Snowflake that performs an INSERT INTO statement. As a result I would like to receive the amount of rows that have been inserted into the target table. The Snowflake documentation describes that I should be able to achieve this by applying the method getNumRowsInserted() to the object Statement. Yet it does not seem to work at all.

Here is an example:

CREATE OR REPLACE TEMP TABLE my_little_test (x INT, y INT);

INSERT INTO my_little_test
SELECT 1,2;

CREATE OR REPLACE PROCEDURE merge_test_sp()
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$

var result          = {"num_rows":""};

var stmt            = "INSERT INTO my_little_test SELECT 3, 4";

var exec_stmt       = snowflake.createStatement({sqlText:stmt}).execute();

result["num_rows"] = exec_stmt.getNumRowsInserted();
        
return result;

$$
;

CALL merge_test_sp();

It throws this error message:

JavaScript execution error: Uncaught TypeError: exec_stmt.getNumRowsInserted is not a function in MERGE_TEST_SP at 'result["num_rows"] = exec_stmt.getNumRowsInserted();' position 31 stackstrace: MERGE_TEST_SP line: 9

However applying the method getNumRowsAffected works:

CREATE OR REPLACE TEMP TABLE my_little_test (x INT, y INT);

INSERT INTO my_little_test
SELECT 1,2;

CREATE OR REPLACE PROCEDURE merge_test_sp()
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$

var result          = {"num_rows":""};

var stmt            = "INSERT INTO my_little_test SELECT 3, 4";

var exec_stmt       = snowflake.createStatement({sqlText:stmt}).execute();

// this method somehow works ?!

result["num_rows"] = exec_stmt.getNumRowsAffected();
        
return result;

$$
;

CALL merge_test_sp();

The output is {"num_rows": 1}.

Also other methods do not work as I tried the same thing with MERGE INTO statements. getNumDuplicateRowsUpdated, getNumRowsDeleted, getNumRowsUpdated all of them do not work. Only the less informative method getNumRowsAffected() works.

Has anyone experienced the same issue? Does someone know a work-around for this?

BTW: Heres the link to the documentation I am refering to: https://docs.snowflake.com/en/sql-reference/stored-procedures-api.html#object-statement

Thanks and best regards

Upvotes: 0

Views: 413

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10189

Can you try this?

CREATE OR REPLACE TEMP TABLE my_little_test (x INT, y INT);

INSERT INTO my_little_test
SELECT 1,2;

CREATE OR REPLACE PROCEDURE merge_test_sp()
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var result          = {"num_rows":""};
var stmt            = "INSERT INTO my_little_test SELECT 3, 4";
var exec_stmt       = snowflake.createStatement({sqlText:stmt});

exec_stmt.execute();
result["num_rows"] = exec_stmt.getNumRowsInserted();
return result;
$$
;

CALL merge_test_sp();

You assign a resultset to exec_stmt by running snowflake.createStatement({sqlText:stmt}).execute(), this is why it doesn't have getNumRowsInserted method is not available.

Upvotes: 1

Related Questions