I have a table in snowflake and the structure of the table is like below.
| Data | id | timestamp |
|---|---|---|
| variant type | varchar | varchar |
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.
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 |
+----------------------------------------+----+---------------------+
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.