Snowflake Update table error: Unsupported type for binding argument

Question

I'm trying to update a table via a stored procedure based on the Log_Id passed and update the status and insert today's date timestamp in the table (when the table is updated).

Stored procedure:

...
CREATE OR REPLACE PROCEDURE update_table(P_ETL_STATUS_CODE VARCHAR,P_LOG_ID FLOAT)
RETURNS FLOAT 
LANGUAGE JAVASCRIPT 
AS 
$$
var sql_command1 = "UPDATE ETL_EXECUTION_STATUS_LOG SET ETL_STATUS_CODE = :1,ETL_EXEC_END_TIME = :2 WHERE LOG_ID = :3";
 var create_stmt1 = snowflake.execute({sqlText : sql_command1,
                    binds: [P_ETL_STATUS_CODE,(new Date()),P_LOG_ID].map(function(x){return x === undefined ? null : x})
                    }
                    );
return P_LOG_ID;
$$
;

... call update_table('Test77',47)

But, I'm getting the following error:

Error message: Execution error in stored procedure UPDATE_TABLE: Unsupported type for binding argument Sun Apr 05 2020 18:39:31 GMT-0700 (PDT) At Snowflake.execute, line 3 position 30

The table already contains Log_Id 47. Column 'ETL_EXEC_END_TIME' is timestamp_ntz in table.

Can you suggest where I'm going wrong?

Cheers

Gokhan Atil Gokhan Atil · Accepted Answer · 2020-04-06T11:16:27

JavaScript date format is not compatible with Snowflake's date format. As you can see from the message, JavaScript sends this:

"Sun Apr 05 2020 18:39:31 GMT-0700 (PDT)"

You may convert this to more common date format:

https://stackoverflow.com/a/21482470/12550965

I see that you want to record timestamp. So why don't you use CURRENT_TIMESTAMP command?

CREATE OR REPLACE PROCEDURE update_table(P_ETL_STATUS_CODE VARCHAR,P_LOG_ID FLOAT)
RETURNS FLOAT 
LANGUAGE JAVASCRIPT 
AS 
$$
var sql_command1 = "UPDATE ETL_EXECUTION_STATUS_LOG SET ETL_STATUS_CODE = :1,ETL_EXEC_END_TIME = CURRENT_TIMESTAMP::TIMESTAMP_NTZ WHERE LOG_ID = :2";
 var create_stmt1 = snowflake.execute({sqlText : sql_command1,
                    binds: [P_ETL_STATUS_CODE,P_LOG_ID].map(function(x){return x === undefined ? null : x})
                    }
                    );
return P_LOG_ID;
$$
;

Snowflake Update table error: Unsupported type for binding argument

1 Answers