Help needed on OH3 using executeCommandLine to run a MYSQL query

Setup, Configuration and Use Scripts & Rules
1

I am trying to execute an MYSQL insert query using an executeCommandLine statement and I am not able to figure out where I am going wrong. I have read various comments stating that the spaces should be replaced by @@. Then I read another that the executeCommandLine has changed where the @@ are no longer used and the string should be split up anywhere there is a pace.

Below is the rule I am trying to run. The query works with no issue on a terminal.

var mysql_host = "localhost"
var mysql_user = "ohuser"
var mysql_pw   = "supersecretpassword"
var mysql_db   = "databasename"


rule "Write to database"
    when
    Item monitoredItem changed
    then
    		var latitude = Xlatitude.state as Number
            var longitude = Xlongitude.state as Number
            var accuracy = Xaccuracy.state.toString().replace(" ft", "")
            var DateTimeType lastSeen = XlastSeen.state.format("%1$tY-%1$tm-%1$td %1$tH:%1$tM:%1$tS").toString
            var battery = Xbattery.state as Number
            var icon = "mylocation"
            
            var nowvalue =  lastSeen + "'," + latitude + ","  + longitude + ",'"  + accuracy + "','"  + icon + "', " + battery  
    		var nowvaluestring = nowvalue.toString()
            
            //var String sql_query = "/usr/bin/mysql -h " + mysql_host + " -u " + mysql_user + " -p" + mysql_pw + " -e \"INSERT INTO Tracker (last_seen,latitude,longitude,accuracy,icon,battery) VALUES (" + nowvaluestring + ");\" -D " + mysql_db
            var String sql_query = "/usr/bin/mysql@@-h@@" + mysql_host + "@@-u@@" + mysql_user + "@@-p" + mysql_pw + "@@-e@@INSERT INTO Tracker (last_seen, latitude, longitude, accuracy, icon, battery) VALUES ('" + nowvaluestring + ");@@" + mysql_db
            var String mysql = executeCommandLine(sql_query)
        

    		if( mysql!="") {
    			//logInfo("SQL","SQL query to be executed: " + sql_query)
        	    logInfo("SQL","Result SQL query: " + mysql)
    		}else{
                logInfo("SQL","Success: " + mysql )
            }
    end

Finally, I saw another https://community.openhab.org/t/bmw-connectdrive-bypass-mariadb-persistence-using-execcommandline-fails-with-error-36-filename-too-long/122541/6 using the following setup which to me is a bit more elegant and easier to read but I am getting an error saying there is no suitable driver but not really sure what to do about it.



            var String sql_query = "INSERT INTO GPSTracker (last_seen, latitude, longitude, accuracy, 
icon, battery) VALUES (" +nowvaluestring + ");"
        
          
            var conn = java.sql.DriverManager.getConnection( "jdbc:mariadb://127.0.0.1:3306/ohdb?useSSL=false&serverTimezone=US/Eastern", "username", "password" );
            var stmt = conn.createStatement();
            var rs = stmt.executeQuery(sql_query)

I am using the Mariadb persistence via JDBC and that is working as expected so I am at a loss.

internal.handler.ScriptActionHandler] - Script execution of rule with UID ‘mysql-1’ failed: No suitable driver found for jdbc:mariadb:

2

Your first rule isn’t working because there are two versions of executeCommandLine. One that takes a “single” argument and one that takes “two” arguments.

The single argument one is fire-and-forget. There is no meaningful value returned because the it doesn’t even wait for the command to finish before returning.

The second version takes a Duration as the first argument. This tells it the maximum amount of time to wait for the command to exit before timing out. This version returns the output from the command as a String.

So why did I put the “one” and “two” in quotes? Because executeCommandLine no long uses @@ to split up the arguments. Instead, each argument is passed as a separate argument to executeCommandLine. See the docs for more details.

I can’t help with the second version.

3

Rich I gave that a try but still coming up short. I tried two different ways, one with the entire command and the other separated as parameters every time I come across a space. It doesn’t really make sense to me that that is how it would work but I can’t make sense of the examples they show. I put in an insert query without using any variables or item states I need to derive the real query in an effort to simplify the query just for testing.

var String mysql = executeCommandLine( Duration.ofSeconds(120), "/usr/bin/mysql -h localhost -u ohuser -psupersecretpassword -e INSERT INTO Tracker (last_seen) VALUES ('2022-06-29 21:00:00'); databasename" )

This one above returns null for the mysql variable I defined.

var String mysql = executeCommandLine( Duration.ofSeconds(120), "/usr/bin/mysql", "-h", "localhost" , "-u" , "ohuser",  "-psupersecretpassword", "-e", "INSERT", "INTO", "Tracker",  "(last_seen)" ,  "VALUES", "('2022-06-29 21:00:00');", "databasename" )

2022-06-30 23:59:23.182 [WARN ] [rg.openhab.core.io.net.exec.ExecUtil] - Failed to execute commandLine '[mysql -h localhost -u ohuser -psupersecretpassword -e INSERT INTO Tracker (last_seen) VALUES ('2022-06-29 21:00:00'); databasename]'
2022-06-30 23:59:23.182 [INFO ] [org.openhab.core.model.script.SQL   ] - Result SQL query: null

This last one just fails. It seems to just be interpreting the call to mysql but the remaining parameters do nothing.

2022-06-30 23:51:02.620 [INFO ] [org.openhab.core.model.script.SQL   ] - Result SQL query: /usr/bin/mysql  Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Usage: /usr/bin/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:
4

@fifo try this…

var String mysql = "/usr/bin/mysql -h localhost -u ohuser -psupersecretpassword -e 'INSERT INTO Tracker (last_seen) VALUES ('2022-06-29 21:00:00');' databasename"
var String result = executeCommandLine(Duration.ofSeconds(120), "bash", "-c", mysql)
5

Hey, @owl770 and @rlkoshak thanks! Your tips got me where I wanted to go. Here is what I ended up with as a working sample for anyone that runs into this.

var mysql_host = "localhost"
var mysql_user = "ohuser"
var mysql_pw   = "supersecretpassword"
var mysql_db   = "databasename"


rule "Write location to mysql database"
    when
    Item monitoredItem changed
    then
    		var latitude = Xlatitude.state as Number
            var longitude = Xlongitude.state as Number
            var accuracy = Xaccuracy.state.toString().replace(" ft", "")
            var DateTimeType lastSeen = XlastSeen.state.format("%1$tY-%1$tm-%1$td %1$tH:%1$tM:%1$tS").toString
            var battery = Xbattery.state as Number
            var icon = "mylocation"
            
            var nowvalue =  "'" + lastSeen + "'," + latitude + ","  + longitude + ",'"  + accuracy + "','"  + icon + "', " + battery  
    	    var nowvaluestring = nowvalue.toString()
                                
            var String sql_query = "INSERT INTO GPSTracker (last_seen, latitude, longitude, accuracy, icon, battery) VALUES (" + nowvaluestring +");"
            var String mysql = executeCommandLine( Duration.ofSeconds(10), "/usr/bin/mysql", "-h", mysql_host , "-u" , mysql_user , "-p" + mysql_pw, "-e", sql_query, mysql_db )

    		if( mysql!="") 
            {
        	    logInfo("SQL","Result SQL query: " + mysql)
    		}
end