CODEWITHSUNDEEP
javascriptsnowflake-cloud-data-platform
Woodsman
Woodsman

Reputation: 1179

Pass a JSON object to a stored procedure in Snowflake

I want to call a Snowflake procedure and pass in a JSON object, but I got a bind error. 

/*
Function to insert one record into the database.

@param tableName : string = Name of table to insert into
@param record: object with the columns and their respective values. Expected format:

var record = {'severity': 'I',message:'TestMess\"\'age'};

In the above, column severity detected type string due to value 'I'
and message 'TestMess"'age', where the surrounding single quotes (')
will not be inserted.  

@return True if the insert success
*/
create or replace procedure sc_hub_insert(tableName varchar,record object)
    RETURNS boolean
    LANGUAGE javascript
    strict 
    execute as owner 
    as 
    $$
    var result = false;

    var columnNames=[];
    var quotedValues=[];
    var SINGLE_QUOTE_CHAR="'";
    var DOUBLE_QUOTE_CHAR="\"";
    var COMMA_CHAR=",";
    var LEFT_PARENTHESIS="(";
    var RIGHT_PARENTHESIS=")";
    var ESCAPED_SINGLE_QUOTE_CHAR="\\'";
    var ESCAPED_DOUBLE_QUOTE_CHAR="\\\"";
    for(var propertyName in record) {
        columnNames.push(propertyName);
        var recordValue=record[propertyName];
        var quotedValue=recordValue;
        if(typeof(recordValue)==='string') {
            var escapeStep1=recordValue.replace(SINGLE_QUOTE_CHAR,ESCAPED_SINGLE_QUOTE_CHAR);
            var escapeStep2=escapeStep1.replace(DOUBLE_QUOTE_CHAR,ESCAPED_DOUBLE_QUOTE_CHAR);
            quotedValue=SINGLE_QUOTE_CHAR+escapeStep2+SINGLE_QUOTE_CHAR;
        }
        quotedValues.push(quotedValue);
    }
    var sql_command = "insert into "+tableName+LEFT_PARENTHESIS+columnNames.join(",")+RIGHT_PARENTHESIS+
    " values "+LEFT_PARENTHESIS+quotedValues.join(",")+RIGHT_PARENTHESIS);

    try {
        snowflake.execute ({sqlText: sql_command});
        result = true;
    } catch(error) {
        result = false;
    }

    return result;
    $$
    ;

/*
Function to log message into SC_HUB_LOG_MESSAGES table

@param tableName : string = Name of table to insert into
@param record: object with the columns and their respective values. Expected format:

var record = {'severity': 'I',message:'TestMess\"\'age'};

In the above, column severity detected type string due to value 'I'
and message 'TestMess"'age', where the surrounding single quotes (')
will not be inserted.  

@return True if the insert success
*/
create or replace procedure sc_hub_log_message(severity varchar,message varchar)
    RETURNS varchar
    LANGUAGE javascript
    strict 
    execute as owner 
    as 
    $$
    var result = 'UNKNOWN';
    var record = {'severity': SEVERITY,message:MESSAGE};
    var SINGLE_QUOTE_CHAR="'";
    var DOUBLE_QUOTE_CHAR="\"";
    var COMMA_CHAR=",";
    var LEFT_PARENTHESIS="(";
    var RIGHT_PARENTHESIS=")";
    var ESCAPED_SINGLE_QUOTE_CHAR="\\'";
    var ESCAPED_DOUBLE_QUOTE_CHAR="\\\"";
    try {
        statement = snowflake.createStatement(
            {
            sqlText: "call SC_HUB_INSERT(?,?);",
            binds:['SC_HUB_LOG_MESSAGES',record]
            }
        );
        statementResult = statement.execute();
        statementResult.next();
        result = 'SUCCESS';
    } catch(error) {
        result = 'FAILURE: '+error;
    }

    return result;
    $$
    ;

When I call the sc_hub_log_message routine below using the below syntax: 

call sc_hub_log_message('I','***TestMessage');

I get 1 FAILURE: Unsupported type for binding argument [object Object] as the result value.

From the documentation, I assume that the DB type for a JSON object is object, but I wasn't sure. I'm also not sure if I need to do any kind of JSON conversion, (ie. treat the object like a string then de-serialize it to a true JavaScript JSON object).

Secondly, how do people work with this given that there is apparently no concept of a console.log or server output like Oracle would have had?

Upvotes: 1

Views: 4255

Answers (2)

Lesly Premsingh C
Lesly Premsingh C

Reputation: 65

to get detail error use this for logging error logging in catch block 

               catch(err)
   {
       var result_set= "";
        result_set =  "Failed: Code: " + err.code + " State: " + err.state + " Message: " + err.message +" Stack Trace:" + err.stackTraceTxt;

--return result_set; --- or log into a table }

Upvotes: 1

Gokhan Atil
Gokhan Atil

Reputation: 10039

As you said, object is not supported as bind variable, and you need to do some JSON conversion. You can convert your JSON object to varchar when binding in sc_hub_log_message, and then you can convert back to JSON in your sc_hub_insert function.

I fixed some syntax errors, also included extra variable definitions to read from parameters, and removed the comments to keep them shorter:

create or replace procedure sc_hub_insert(tableName varchar,record varchar)
    RETURNS boolean
    LANGUAGE javascript
    strict 
    execute as owner 
    as 
    $$
    var result = false;

    var columnNames=[];
    var quotedValues=[];
    var SINGLE_QUOTE_CHAR="'";
    var DOUBLE_QUOTE_CHAR="\"";
    var COMMA_CHAR=",";
    var LEFT_PARENTHESIS="(";
    var RIGHT_PARENTHESIS=")";
    var ESCAPED_SINGLE_QUOTE_CHAR="\\'";
    var ESCAPED_DOUBLE_QUOTE_CHAR="\\\"";
    var record = JSON.parse(RECORD)
    var tableName = TABLENAME;
    for(var propertyName in record) {
        columnNames.push(propertyName);
        var recordValue=record[propertyName];
        var quotedValue=recordValue;
        if(typeof(recordValue)==='string') {
            var escapeStep1=recordValue.replace(SINGLE_QUOTE_CHAR,ESCAPED_SINGLE_QUOTE_CHAR);
            var escapeStep2=escapeStep1.replace(DOUBLE_QUOTE_CHAR,ESCAPED_DOUBLE_QUOTE_CHAR);
            quotedValue=SINGLE_QUOTE_CHAR+escapeStep2+SINGLE_QUOTE_CHAR;
        }
        quotedValues.push(quotedValue);
    }
    var sql_command = "insert into "+tableName+LEFT_PARENTHESIS+columnNames.join(",")+RIGHT_PARENTHESIS+
    " values "+LEFT_PARENTHESIS+quotedValues.join(",")+RIGHT_PARENTHESIS;

    try {
        snowflake.execute ({sqlText: sql_command});
        result = true;
    } catch(error) {
        result = false;
    }

    return result;
    $$
    ;



create or replace procedure sc_hub_log_message(severity varchar,message varchar)
    RETURNS varchar
    LANGUAGE javascript
    strict 
    execute as owner 
    as 
    $$
    var result = 'UNKNOWN';
    var record = {'severity': SEVERITY,message:MESSAGE};
    var SINGLE_QUOTE_CHAR="'";
    var DOUBLE_QUOTE_CHAR="\"";
    var COMMA_CHAR=",";
    var LEFT_PARENTHESIS="(";
    var RIGHT_PARENTHESIS=")";
    var ESCAPED_SINGLE_QUOTE_CHAR="\\'";
    var ESCAPED_DOUBLE_QUOTE_CHAR="\\\"";
    try {
        statement = snowflake.createStatement(
            {
            sqlText: "call SC_HUB_INSERT(?,?);",
            binds:['SC_HUB_LOG_MESSAGES',JSON.stringify(record) ]
            }
        );
        statementResult = statement.execute();
        statementResult.next();
        result = 'SUCCESS';
    } catch(error) {
        result = 'FAILURE: '+error;
    }

    return result;
    $$
    ;

You are right about that there could be much better "logging" feature, maybe you can post this idea to Snowflake ideas:

https://community.snowflake.com/s/ideas

Upvotes: 3

Related Questions