mysql - SQL Stored procedure on snowflake is treating binded column as string and its not getting its value from the table

Question

I am doing a procedure to loop over specific field names for 2 reasons:

1. I want to hash the field name itself using md5 (We are working with data vault);
2. I want to add each field name value as a row in the table.

I have the following procedure which is working perfect:

CREATE PROCEDURE ADD_OBSERVATION_VALUES()
RETURNS string
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    arr = [];
    var row_num = 1;
    // Set the indicators
    COLUMN_FIELD_NAMES = ["care_beneficiary", "cg_child_6mo_receiv_ind_iycf_nbr_1st_cons_6mc_iycfc number",
    "preg_women_rec_ind_counselling_nbr_1st_cons_pregw_iycfc",
    ...
    ];
    
    COLUMN_FIELD_NAMES_TO_HASH = [
    "cg_child_6mo_receiv_ind_iycf/nbr_1st_cons_6mc_iycfc",
    "cg_child_6mo_receiv_ind_iycf/nbr_followup_2nd_time_6mc_iycfc",
    ...
    ];
    try{
//      while(rows_result.next()){
          for (var col_num = 0; col_num<COLUMN_FIELD_NAMES_TO_HASH.length; col_num = col_num+1){
              var COL_NAME = COLUMN_FIELD_NAMES_TO_HASH[col_num];
              var query = "INSERT INTO LINK_OBSERVATION_FIELD SELECT (SELECT md5(concat(?, concat('CAMP', concat(CAMPNO, DATE))))), current_timestamp(), (SELECT 'ONA'), (SELECT (md5(concat(DATE, concat('CAMP', CAMPNO))))), md5(?) FROM IYCF_TEMP";
              var stmt = snowflake.createStatement( {sqlText: query, binds:[COL_NAME, COL_NAME]} );
              if(stmt.execute()){
//                  var query = "INSERT INTO SAT_FIELD_VALUES SELECT (SELECT (md5(md5(concat(?, concat('CAMP', concat(CAMPNO, DATE))))))), current_timestamp(), NULL, (SELECT 'ONA'), ?, (SELECT 'PENDING'), (SELECT _SUBMISSION_TIME), (SELECT md5(concat(?, concat('CAMP', concat(CAMPNO, DATE))))) FROM IYCF_TEMP";
//                  var stmt = snowflake.createStatement( {sqlText: query, binds: [COL_NAME, COL_NAME, COL_NAME] });
//                  stmt.execute()
              }
          }
//      }
      return "DONE"
    }
    catch(error){
        return error
    }
    
$$;

The first insert query is working fine, when it goes to the second insert query after successful execution, I get the following error:

Numeric value 'care_beneficiary' is not recognized

I am guessing that the error is coming from , ?, of the below insert query:

INSERT INTO SAT_FIELD_VALUES SELECT (SELECT (md5(md5(concat(?, concat('CAMP', concat(CAMPNO, DATE))))))), current_timestamp(), NULL, (SELECT 'ONA'), ?, (SELECT 'PENDING'), (SELECT _SUBMISSION_TIME), (SELECT md5(concat(?, concat('CAMP', concat(CAMPNO, DATE))))) FROM IYCF_TEMP;

The ? within the CONCATS are working fine, but the standalone field , ?, . In this standalone binded field, I want to get its value from the table not its name, and I am assuming that it is because the field is being read as with quotes, and the query is considering it string that should not be added to a numeric field.

Any idea how to remove quotes or let the query treat it as field name and not as a value?

question from:https://stackoverflow.com/questions/65625913/sql-stored-procedure-on-snowflake-is-treating-binded-column-as-string-and-its-no

Answer 1

The solution was as mentioned by Felipe in the comment section of the question, to bind and compose the query with strings and variables:

var query = "SELECT ... " + COL_NAME + "FROM ..."

Apparently IDENTIFIER(COL_NAME) didn't work for me but I am sure that it will work if I knew where to use the binding.