ExecuteCommandLine with SQL query does not execute correctly

Setup, Configuration and Use Scripts & Rules
1

Hi,
I can successfully send the following SQL query from the commandline:

mysql -h 192.168.2.180 -P 3307 -u openhab -popenhab < /home/pi/query.txt

and it returns a single Temp. value from my external MariaDB database as it should be:
value 7.50

When I put this command string into the ExecuteCommandLine,


var String T_a // = executeCommandLine("mysql -h 192.168.2.180 -P 3307 -u openhab -popenhab &lt; /home/pi/query.txt",@@1000)

logInfo("execute", " wert {} ", T_a)

the return value is the full man page of the mysql command and instead of the result value.

2018-11-07 21:47:00.081 [INFO ] [lipse.smarthome.model.script.execute] -  wert mysql  Ver 15.1 Distrib 10.1.23-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Usage: mysql [OPTIONS] [database]
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 
The following groups are read: mysql client client-server client-mariadb
The following options may be given as the first argument:
--print-defaults        Print the program argument list and e....

What do I do wrong?

2

It’s not HTML, I think you’ll need to escape that < in a different way, < maybe.
I guess you’ve already tried just <

3

oh sorry, in the original console command line, the &lt was the correct “<” character, which I also used in the OH executeCommandLine call. It was somehow transformed by using the code fences.
As you bring it up, if this character is the problem, how would I have to escape it? I only read about spaces which have to be escaped by @@.
Is there a more comprehensive list of escape characters?

thank you

4

I think the escape character here is \ , e.g. to include a quote \"
I don’t know if that’s needed for <

Looking again at your post, I guess the // somehow got added as well? We probably ought to see the whole thing again. Not sure why you’ve got @@ in the timeout parameter.

What happens if you try some simpler sql query to begin with?

5

so here is the corrected code, simpler, now with direct sql command instead of a file link.
Also, -e causes a more readable error message:


var String T_a = executeCommandLine("mysql -h 192.168.2.180 -P 3307 -u openhab -popenhab -e \" USE openhab; SELECT samples.time ,  samples.value FROM samples WHERE  samples.address = 4 Limit 1\" ", 1000)
logInfo("execute", " wert {} ", T_a)

This gives the following result:

2018-11-08 15:57:30.127 [INFO ] [lipse.smarthome.model.script.execute] -  wert ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"USE openhab; SELECT samples.time ,  samples.value FROM samples WHERE  samples.a' at line 1

Using ’ or " as escape character does not change anything.
Appearantly, still something is wrong with the delimiter for the sql sequence.
Thus, both < and " make troubles and cannot be escaped, but I have no idea how to fix this.

I can get results by putting the whole commandline in a scriptfile:

var String T_a = executeCommandLine("/etc/openhab2/test1.sh", 1000)
logInfo("execute", " wert shellscript  {} ", T_a)

which returns the expected value for a temperature.

2018-11-09 12:57:30.120 [INFO ] [lipse.smarthome.model.script.execute] -  wert shellscript  time	value

2018-11-09 12:51:27	9.50

but its not what I originally wanted, as would I like to handle the commands directly in the rule.