雪花变种数据插入问题：＆＃x27;专家级问题＆＃x27;

发布于 2025-02-11 23:38:34 字数 857 浏览 1 评论 0原文

我有一个雪花上的桌子，桌子的结构如下所示。

数据	ID	TIMESTAMP
变体类型	VARCHAR	VARCHAR

I正在将此数据插入表中

        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, "\\'");

我的插入查询是

`插入XYZ（数据，ID，TIMESTAMP）选择Parse_json（'$ {data}'），'$ {id}'），'$ {id}'，'$ {timestamp}'`

当我运行查询时，它会出现错误。 SQL汇编错误：位置378意外'\“ \ nsyntax错误第2行2”，

没有单个报价的记录插入正确的记录。但是带有一个报价值的数据字段如上所述。尝试了很多事情。请建议。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

筱武穆 2025-02-18 23:38:34

此JavaScript助手功能将逃脱一个字符串，以用作插入语句中的单引号。它仅在因某种原因无法使用绑定变量的情况下使用。

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

您可以更改此行：

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

对此：

let data2 = escapeInsertString(data1);

将辅助功能添加到SP或Node.js代码中，这将起作用。

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.

回复收藏 0 原文

眼藏柔 2025-02-18 23:38:34

由于我们没有看到整个过程，因此我根据您的问题创建了一个过程：

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();
 
$ ;

如您所见，我使用了$ {data2}，并且有效：

call test_xyz();

select * from xyz;

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

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 |
+----------------------------------------+----+---------------------+

回复收藏 0 原文

~没有更多了~