You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by "wanglei2@geekplus.com.cn" <wa...@geekplus.com.cn> on 2019/08/22 07:53:09 UTC
ExecuteSQL ERROR if the field name is a sql keyword
ConvertJSONToSQL -> ExecuteSQL
If the field name is the sql keyword, there will be error.
Is there any simple way to avoid this?
INSERT INTO t_stat_robot_poll (id, robot_id, shelf_code,
task_id, exe_task_id, task_type, exe_task_type, onload_flag, new_shelf_flag, value, start_time, complete_time, start_point,
end_point, distance, instruction, path, pos, power_percent, stop_time, station_id, stop_count) VALUES (?, ?, ?, ?, ?, ?, ?
, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) for StandardFlowFileRecord[uuid=c228ec24-626e-4868-946c-69ada0ac2fa6,claim=S
tandardContentClaim [resourceClaim=StandardResourceClaim[id=1566460129309-36512, container=default, section=672], offset=90
191, length=349],offset=0,name=a049ba19-72b8-482c-827c-29241e04b7ae,size=349] due to com.mysql.jdbc.exceptions.jdbc4.MySQLS
yntaxErrorException: [13000, 2019082215484919216800001603151061933] syntax error :ERROR. token : value is reserved keyword,
you shoud use `value`, pos : 139; routing to failure: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: [13000, 2
019082215484919216800001603151061933] syntax error :ERROR. token : value is reserved keyword, you shoud use `value`, pos :
139
wanglei2@geekplus.com.cn
Re: Re: ExecuteSQL ERROR if the field name is a sql keyword
Posted by "wanglei2@geekplus.com.cn" <wa...@geekplus.com.cn>.
Thanks very much, it works.
wanglei2@geekplus.com.cn
From: Pierre Villard
Date: 2019-08-22 16:12
To: users
Subject: Re: ExecuteSQL ERROR if the field name is a sql keyword
Hi,
That's because your JSON contains a field with the name "value" which is a reserved keyword. In the processor ConvertJSONToSQL, you can set the property "Quote Column Identifiers" to "true". This will solve your problem by using quotes around the column identifiers like suggested in the error message you got.
Hope this helps,
Pierre
Le jeu. 22 août 2019 à 09:53, wanglei2@geekplus.com.cn <wa...@geekplus.com.cn> a écrit :
ConvertJSONToSQL -> ExecuteSQL
If the field name is the sql keyword, there will be error.
Is there any simple way to avoid this?
INSERT INTO t_stat_robot_poll (id, robot_id, shelf_code,
task_id, exe_task_id, task_type, exe_task_type, onload_flag, new_shelf_flag, value, start_time, complete_time, start_point,
end_point, distance, instruction, path, pos, power_percent, stop_time, station_id, stop_count) VALUES (?, ?, ?, ?, ?, ?, ?
, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) for StandardFlowFileRecord[uuid=c228ec24-626e-4868-946c-69ada0ac2fa6,claim=S
tandardContentClaim [resourceClaim=StandardResourceClaim[id=1566460129309-36512, container=default, section=672], offset=90
191, length=349],offset=0,name=a049ba19-72b8-482c-827c-29241e04b7ae,size=349] due to com.mysql.jdbc.exceptions.jdbc4.MySQLS
yntaxErrorException: [13000, 2019082215484919216800001603151061933] syntax error :ERROR. token : value is reserved keyword,
you shoud use `value`, pos : 139; routing to failure: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: [13000, 2
019082215484919216800001603151061933] syntax error :ERROR. token : value is reserved keyword, you shoud use `value`, pos :
139
wanglei2@geekplus.com.cn
Re: ExecuteSQL ERROR if the field name is a sql keyword
Posted by Pierre Villard <pi...@gmail.com>.
Hi,
That's because your JSON contains a field with the name "value" which is a
reserved keyword. In the processor ConvertJSONToSQL, you can set the
property "Quote Column Identifiers" to "true". This will solve your problem
by using quotes around the column identifiers like suggested in the error
message you got.
Hope this helps,
Pierre
Le jeu. 22 août 2019 à 09:53, wanglei2@geekplus.com.cn <
wanglei2@geekplus.com.cn> a écrit :
>
> ConvertJSONToSQL -> ExecuteSQL
>
> If the field name is the sql keyword, there will be error.
> Is there any simple way to avoid this?
>
> INSERT INTO t_stat_robot_poll (id, robot_id, shelf_code,
> task_id, exe_task_id, task_type, exe_task_type, onload_flag,
> new_shelf_flag, value, start_time, complete_time, start_point,
> end_point, distance, instruction, path, pos, power_percent, stop_time,
> station_id, stop_count) VALUES (?, ?, ?, ?, ?, ?, ?
> , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) for
> StandardFlowFileRecord[uuid=c228ec24-626e-4868-946c-69ada0ac2fa6,claim=S
> tandardContentClaim
> [resourceClaim=StandardResourceClaim[id=1566460129309-36512,
> container=default, section=672], offset=90
> 191,
> length=349],offset=0,name=a049ba19-72b8-482c-827c-29241e04b7ae,size=349]
> due to com.mysql.jdbc.exceptions.jdbc4.MySQLS
> yntaxErrorException: [13000, 2019082215484919216800001603151061933] syntax
> error :ERROR. token : value is reserved keyword,
> you shoud use `value`, pos : 139; routing to failure:
> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: [13000, 2
> 019082215484919216800001603151061933] syntax error :ERROR. token : value
> is reserved keyword, you shoud use `value`, pos :
> 139
>
> ------------------------------
> wanglei2@geekplus.com.cn
>