bendsql query can't execute complex sql?

[lddlww]

query sql like follow:

bendsql -D rsyslogdb --query="select logtime timestamp,ifnull(trim(both '"' from loginfo['source']),trim(both '"' from loginfo['hostname'])) source, trim(both '"' from loginfo['hostname']) hostname,host_ip fromhost_ip,ifnull(ifnull(trim(both '"' from loginfo['sysfacility']),trim(both '"' from loginfo['facility_text'])),'') facility_text,ifnull(ifnull(trim(both '"' from loginfo['level']),trim(both '"' from loginfo['severity_text'])),'') severity_text, ifnull(ifnull(trim(both '"' from loginfo['systag']),trim(both '"' from loginfo['tag'])),'') tag,ifnull(trim(both '"' from loginfo['message']),trim(both '"' from loginfo['log'])) message,ifnull(trim(both '"' from loginfo['app_name']),'') app_name,ifnull(ifnull(trim(both '"' from loginfo['source_ts']),trim(both '"' from loginfo['timereported'])),'') timereported, ifnull(trim(both '"' from loginfo['method']),'nat') method,ifnull(trim(both '"' from loginfo['rsyslogd']),'') rsyslogd, ifnull(trim(both '"' from loginfo['flb_version']),'') flb_version,ifnull(trim(both '"' from loginfo['kernel_version']),'') kernel_version,ifnull(trim(both '"' from loginfo['os_name']),'') os_name,ifnull(trim(both '"' from loginfo['region']),'') region,ifnull(trim(both '"' from loginfo['logfile_name']),'') logfile_name,trim(both '"' from loginfo['type']) type from t_transient_log_allsys where host_ip='10.35.19.10' limit 1"

throw exception

Error: APIError: ResponseError with 1005: error: 
  --> SQL:1:619
  |
1 | select logtime timestamp,ifnull(trim(both ' from loginfo[source]),trim(both ' from loginfo['hostname'])) source, trim(both ' from loginfo[hostname]) hostname,host_ip fromhost_ip,ifnull(ifnull(trim(both ' from loginfo['sysfacility']),trim(both ' from loginfo[facility_text])),) facility_text,ifnull(ifnull(trim(both ' from loginfo['level']),trim(both ' from loginfo[severity_text])),) severity_text, ifnull(ifnull(trim(both ' from loginfo['systag']),trim(both ' from loginfo[tag])),) tag,ifnull(trim(both ' from loginfo['message']),trim(both ' from loginfo[log])) message,ifnull(trim(both ' from loginfo['app_name']),'') app_name,ifnull(ifnull(trim(both ' from loginfo[source_ts]),trim(both ' from loginfo['timereported'])),'') timereported, ifnull(trim(both ' from loginfo[method]),nat) method,ifnull(trim(both ' from loginfo['rsyslogd']),'') rsyslogd, ifnull(trim(both ' from loginfo[flb_version]),) flb_version,ifnull(trim(both ' from loginfo['kernel_version']),'') kernel_version,ifnull(trim(both ' from loginfo[os_name]),) os_name,ifnull(trim(both ' from loginfo['region']),'') region,ifnull(trim(both ' from loginfo[logfile_name]),) logfile_name,trim(both ' from loginfo['type']) type from t_transient_log_allsys where host_ip='10.35.19.10' limit 1
  |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           ^ unexpected `)`, expecting `IS`, `NOT`, `IN`, `EXISTS`, `BETWEEN`, `+`, `-`, `*`, `/`, `//`, `DIV`, `%`, `||`, `<->`, `>`, `<`, `>=`, `<=`, `=`, `<>`, `!=`, `^`, `AND`, `OR`, `XOR`, `LIKE`, `REGEXP`, `RLIKE`, `SOUNDS`, <BitWiseOr>, <BitWiseAnd>, <BitWiseXor>, <ShiftLeft>, <ShiftRight>, `->`, `->>`, `#>`, `#>>`, `?`, `?|`, `?&`, `@>`, `<@`, `@?`, `@@`, `#-`, <Factorial>, <SquareRoot>, <BitWiseNot>, <CubeRoot>, <Abs>, `CAST`, `TRY_CAST`, `DATE_ADD`, `DATE_SUB`, `DATE_TRUNC`, `DATE`, `TIMESTAMP`, `INTERVAL`, `::`, or 47 more .

that's double quote problem？how can i solve it

best wishes

[lddlww]

it's solved now, 😄

bendsql -D rsyslogdb -n  --query="(select logtime timestamp,ifnull(ifnull(trim(both '\"' from loginfo['source']),trim(both '\"' from loginfo['hostname'])),'') source, ifnull(trim(both '\"' from loginfo['hostname']),'') hostname,host_ip fromhost_ip,ifnull(ifnull(trim(both '\"' from loginfo['sysfacility']),trim(both '\"' from loginfo['facility_text'])),'') facility_text,ifnull(ifnull(trim(both '\"' from loginfo['level']),trim(both '\"' from loginfo['severity_text'])),'') severity_text, ifnull(ifnull(trim(both '\"' from loginfo['systag']),trim(both '\"' from loginfo['tag'])),'') tag,ifnull(ifnull(trim(both '\"' from loginfo['message']),trim(both '\"' from loginfo['log'])),'') message,ifnull(trim(both '\"' from loginfo['app_name']),'') app_name,ifnull(ifnull(trim(both '\"' from loginfo['source_ts']),trim(both '\"' from loginfo['timereported'])),'') timereported, ifnull(trim(both '\"' from loginfo['method']),'nat') method,ifnull(trim(both '\"' from loginfo['rsyslogd']),'') rsyslogd, ifnull(trim(both '\"' from loginfo['flb_version']),'') flb_version,ifnull(trim(both '\"' from loginfo['kernel_version']),'') kernel_version,ifnull(trim(both '\"' from loginfo['os_name']),'') os_name,ifnull(trim(both '\"' from loginfo['region']),'') region,ifnull(trim(both '\"' from loginfo['logfile_name']),'') logfile_name,trim(both '\"' from loginfo['type']) type from t_transient_log_allsys where host_ip='10.35.19.10' limit 1)"

[sundy-li]

You can also use pipe from file :

echo "select 1 " | bendsql
bendsql < a.sql
cat a.sql | bendsql 

[lddlww]

awesome，thanks