CODEWITHSUNDEEP
javascriptsqlobjectstored-proceduressnowflake-cloud-data-platform
alim1990
alim1990

Reputation: 4972

Snowflake unable to bind an object into a query to insert it into a variant column even when using JSON.parse

This stored procedure will take an object and insert it into a variant column of a table:

try {
  var table_query = "CREATE OR REPLACE TEMPORARY TABLE FIELD_NAME_TABLE (field_variant VARIANT)";
  var table_query_stmt_result = snowflake.createStatement({sqlText: table_query}).execute();
  var insert_query = "INSERT INTO FIELD_NAME_TABLE SELECT ?";
  var insert_stmt = snowflake.createStatement({sqlText: insert_query, binds: [(TABLE_COL_AND_FIELDS)]});
  var result = insert_stmt.execute();
  result.next();
  return result;
  return result['number of rows inserted'];
}
catch(err) {
  return err.message;
}

TABLE_COL_AND_FIELDS is an object having the following structure:

{
   "field1": "val1",
   "field2": "val2"
}

When running the script I am getting the following error:

"Invalid binds argument[object Object]. Error: Unsupported type for binding argument 2undefined"

I added JSON.parse(TABLE_COL_AND_FIELDS) and got this error:

"Unexpected token o in JSON at position 1"

I tried:

(TABLE_COL_AND_FIELDS).toString()

And I've got an error of:

"SQL compilation error:\nExpression type does not match column data type, expecting VARIANT but got VARCHAR(15) for column FIELD_VARIANT"

FIELD_VARIANT is the column having type of VARIANT in the temporary table.

Upvotes: 3

Views: 1082

Answers (1)

alim1990
alim1990

Reputation: 4972

I've found the solution by adding parse_json() to the query text, and JSON.stringify() within the binds part. The script is as follows:

try {
  var table_query = "CREATE OR REPLACE TEMPORARY TABLE FIELD_NAME_TABLE (field_variant VARIANT)";
  var table_query_stmt_result = snowflake.createStatement({sqlText: table_query}).execute();
  var insert_query = "INSERT INTO FIELD_NAME_TABLE SELECT parse_json(?)";
  var insert_stmt = snowflake.createStatement({sqlText: insert_query, binds: [JSON.stringify(TABLE_COL_AND_FIELDS)]});
  var result = insert_stmt.execute();
  result.next();
  return result;
  return result['number of rows inserted'];
}
catch(err) {
  return err.message;
}

Upvotes: 3

Related Questions