CODEWITHSUNDEEP
snowflake-cloud-data-platform
RMC_DEV
RMC_DEV

Reputation: 119

Inserting into a table snowflake with a passed variable

I have a table which has 3 attributes

ATTRIBUTE DATATYPE FLAG( 0 OR 1)

My stored procedure takes a string of records in the form "Att1 Datatype1 1, Att2 Datatype2 0, Att3 Datatype3 0...) so for example a passed string can be "DATABASE VARCHAR 1, SCHEMA VARCHAR 1, TIMESTAMP TIMESTAMP 0"

My code gets the column names of the table and stores it in a string My code takes the passed string and puts them into array elements seperated by the , However the issue arrises when I try to do an insertion into my table I keep getting the error "invalid Identifier"

CREATE OR REPLACE PROCEDURE "ADMINDB"."TOOLKIT".ADD_ATTRIBUTES_SESSION_META ("P_ATTRIBUTE_DATATYPE_FLAG" VARCHAR(16777216))
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT='Add Attributes to the table SESSION_ATTRIBUTES_META'
EXECUTE AS CALLER
AS
$$
  var v_list = P_ATTRIBUTE_DATATYPE_FLAG;
  var arr_list = [];
  arr_list = P_ATTRIBUTE_DATATYPE_FLAG.split(',');
  var v_string;
  var arr_col_att = [];
  var v_sqlCode = `SELECT * FROM ` + "ADMINDB" + "." + "TOOLKIT" + "." + "SESSION_ATTRIBUTES_META";
  
    
  try{
    var sqlStmt = snowflake.createStatement({sqlText :  v_sqlCode});
    var sqlRS = sqlStmt.execute();
  }catch(err){
     errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
     errMessage += "\n  Message: " + err.message + v_sqlCode;
     errMessage += "\nStack Trace:\n" + err.stackTraceTxt + v_sqlCode;  
     throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
   }
   
  for (i = 1; i <= sqlStmt.getColumnCount(); i++) {
    arr_col_att.push(sqlStmt.getColumnName(i));
}

     arr_col_att[0] = arr_col_att[0].replace(/\s/g, ',');
    var v_col_att = arr_col_att.toString();
    v_string = arr_list[0].toString();
    v_string = v_string.replace(/\s/g, ',');
    
    v_sqlCode = `INSERT INTO ` + "ADMINDB" + "." + "TOOLKIT" + "." 
+ "SESSION_ATTRIBUTES_META" + `(` + v_col_att + `) VALUES( ` + v_string + `)`;
                
    try{
      var sqlStmt = snowflake.createStatement({sqlText :  v_sqlCode});
      var sqlRS = sqlStmt.execute();
    }catch(err){
      errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
      errMessage += "\n  Message: " + err.message + v_sqlCode;
      errMessage += "\nStack Trace:\n" + err.stackTraceTxt + v_sqlCode;  
      throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
    }   
  return "SUCCESS!";
$$;

CALL "ADMINDB"."TOOLKIT".ADD_ATTRIBUTES_SESSION_META('DATABASE VARCHAR 1,SCHEMA VARCHAR 1,TIMESTAMP TIMESTAMP 0');

Upvotes: 0

Views: 333

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10134

Modifying line 33 and 36, fixes the issue:

CREATE OR REPLACE PROCEDURE "ADMINDB"."TOOLKIT".ADD_ATTRIBUTES_SESSION_META ("P_ATTRIBUTE_DATATYPE_FLAG" VARCHAR(16777216))
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
COMMENT='Add Attributes to the table SESSION_ATTRIBUTES_META'
EXECUTE AS CALLER
AS
$$
  var v_list = P_ATTRIBUTE_DATATYPE_FLAG;
  var arr_list = [];
  arr_list = P_ATTRIBUTE_DATATYPE_FLAG.split(',');
  var v_string;
  var arr_col_att = [];
  var v_sqlCode = `SELECT * FROM ` + "ADMINDB" + "." + "TOOLKIT" + "." + "SESSION_ATTRIBUTES_META";
  
    
  try{
    var sqlStmt = snowflake.createStatement({sqlText :  v_sqlCode});
    var sqlRS = sqlStmt.execute();
  }catch(err){
     errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
     errMessage += "\n  Message: " + err.message + v_sqlCode;
     errMessage += "\nStack Trace:\n" + err.stackTraceTxt + v_sqlCode;  
     throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
   }
   
  for (i = 1; i <= sqlStmt.getColumnCount(); i++) {
    arr_col_att.push(sqlStmt.getColumnName(i));
}

     arr_col_att[0] = arr_col_att[0].replace(/\s/g, ',');
    var v_col_att = arr_col_att.toString();
    v_string = arr_list[0].toString();
    v_string = v_string.replace(/\s/g, "','" );
    
    v_sqlCode = `INSERT INTO ` + "ADMINDB" + "." + "TOOLKIT" + "." 
+ "SESSION_ATTRIBUTES_META" + `(` + v_col_att + `) VALUES( '` + v_string + `' )`;
                
    try{
      var sqlStmt = snowflake.createStatement({sqlText :  v_sqlCode});
      var sqlRS = sqlStmt.execute();
    }catch(err){
      errMessage =  "Failed: Code: " + err.code + "\n  State: " + err.state;
      errMessage += "\n  Message: " + err.message + v_sqlCode;
      errMessage += "\nStack Trace:\n" + err.stackTraceTxt + v_sqlCode;  
      throw 'Encountered error in executing v_sqlCode. \n' + errMessage;
    }   
  return "SUCCESS!";
$$;

CALL "ADMINDB"."TOOLKIT".ADD_ATTRIBUTES_SESSION_META('DATABASE VARCHAR 1,SCHEMA VARCHAR 1,TIMESTAMP TIMESTAMP 0');

As far I see, you haven't implemented the process multiple records in the argument for now, but I think you can handle it.

Upvotes: 1

Related Questions