I have a table in snowflake and the structure of the table is like below.

I am inserting this data into the table

        let data = {
        "a":"hi",
        "b":"i'm the problem"
        }
    let id = "id";
    let timestamp = "2022-06-29 20:30:44"
let data1 = JSON.stringify(data);
let data2 = data1.replace(/\'/g, "\\'");

my insert query is

` insert into xyz (Data,id,timestamp) select parse_json('${data}'),'${id}','${timestamp}' `

when I am running the query it is giving an error. SQL compilation error:\nsyntax error line 2 at position 378 unexpected ''\"

the record which doesn't have a single quote is inserted properly. but the data field with a single quote value is giving the error as mentioned above. I have tried many things. please suggest.

CodePudding user response：

As we don't see the whole procedure, I created one based on your question:

create or replace table xyz (data variant, id varchar, timestamp varchar );

create or replace procedure test_xyz()
returns varchar
LANGUAGE JAVASCRIPT
as
$$
    let data = {
        "a":"hi",
        "b":"i'm the problem"    }
    let id = "id";
    let timestamp = "2022-06-29 20:30:44"
    let data1 = JSON.stringify(data);
    let data2 = data1.replace(/\'/g, "\\'");


    snowflake.createStatement( { sqlText: ` insert into xyz (Data,id,timestamp) select parse_json('${data2}'),'${id}','${timestamp}' ` } ).execute();
 
$$ ;

As you see, I used ${data2}, and it worked:

call test_xyz();

select * from xyz;

 ---------------------------------------- ---- --------------------- 
|                  DATA                  | ID |      TIMESTAMP      |
 ---------------------------------------- ---- --------------------- 
| { "a": "hi", "b": "i'm the problem"  } | id | 2022-06-29 20:30:44 |
 ---------------------------------------- ---- --------------------- 

CodePudding user response：

This Javascript helper function will escape a string to use as a single-quoted literal in an insert statement. It should only be used in situations where bind variables cannot be used for some reason.

function escapeInsertString(value) {
    var s = value.replace(/\\/g, "\\\\");
    s = s.replace(/'/g, "''" );
    s = s.replace(/"/g, '\\"');
    return s;
}

You can change this line:

let data2 = data1.replace(/\'/g, "\\'");

To this:

let data2 = escapeInsertString(data1);

With the helper function added to the SP or Node.js code, this will work.