Correct use of Exec.executeCommandLine in JS Rule

[milo (M)]

Hello,
I try to use the action Exec but get an error

Here is my rule

rules.JSRule({
  name: "Test rules",
  description: "Testse rule",
  triggers: [triggers.GenericCronTrigger("0/10 * * * * ? *")],
  execute: (event) => {
	  actions.Exec.executeCommandLine("php /etc/openhab/scripts/mysqltocsv.php number_airsensor1_co2");
  },
  tags: ["teset", "Tsest"],
  id: "test"
});

But the error is

Error occurred when executing commandLine '[php /etc/openhab/scripts/mysqltocsv.php number_airsensor1_co2]'
java.io.IOException: Cannot run program "php /etc/openhab/scripts/mysqltocsv.php number_airsensor1_co2": error=2, No such file or directory

but on the consoe i can use it as user openhab

sudo -u openhab /etc/openhab/scripts/mysqltocsv.php 

What is wrong in my syntax?

[Mark_Webster]

Have you tried specifying the full path to the php executable in the rule?

[milo (M)]

Same error

 Error occurred when executing commandLine '[/usr/bin/php /etc/openhab/scripts/mysqltocsv.php number_airsensor1_co2]'
java.io.IOException: Cannot run program "/usr/bin/php /etc/openhab/scripts/mysqltocsv.php number_airsensor1_co2": error=2, No such file or directory

[Mark_Webster]

Give this a try…

actions.Exec.executeCommandLine("/usr/bin/php","/etc/openhab/scripts/mysqltocsv.php","number_airsensor1_co2");

I have a rule that ssh’s to another box to execute a remote command and had to separate the three elements (ssh, user@device, remote command) albeit that’s within a UI javascript rule.

[milo (M)]

Hello,

No error message only the output null on the console…

[INFO ] [penhab.automation.script.file.res.js] - null

Mhhm…

I found this…Openhab 3 javascript Exec.executeCommandLine passing variables - #5 by ubeaut

Without actions

I get

penhab.automation.script.file.res.js] - Failed to execute rule test: ReferenceError: "Exec" is not defined: ReferenceError: "Exec" is not defined

I am confused

[JimT (jimtng)]

@Mark_Webster

Give this a try…

actions.Exec.executeCommandLine("/usr/bin/php","/etc/openhab/scripts/mysqltocsv.php","number_airsensor1_co2");

This is the correct way of calling Exec.executeCommandLine. Each part must be passed as a separate argument. Spaces don’t separate the arguments.

[milo (M)]

Mhhm it’s not running… that is the output

[INFO ] [penhab.automation.script.file.res.js] - null

Mhhm @rlkoshak sorry for pinging…do you have any idea?

[JimT (jimtng)]

Show us your code?

[milo (M)]

/etc/openhab/scripts/mysqltocsv.php

#!/usr/bin/php
<?php
if(empty($argv[1])){
        die("Item name missing");
}else{
        $itemname = $argv[1];
}
$db = new mysqli('localhost','xxxxx', 'xxxxx', 'xxxxx');
// Check connection 
if ($db->connect_error) { 
    die("Connection failed: " . $db->connect_error); 
}

$query = $db->query("SELECT ItemId FROM items WHERE itemname='" .$itemname. "'");

if($query->num_rows > 0){ 
    $row = $query->fetch_assoc();
    $table="item".str_pad($row['ItemId'], 4, 0, STR_PAD_LEFT);
} else {
    $table ="";
}

$query = $db->query("SELECT time, value FROM " .$table); 
 
if($query->num_rows > 0){ 
    $delimiter = ","; 
    $filename = "/etc/openhab/html/Graph/" .$itemname. ".csv"; 
     
    // Create a file pointer 
    $f = fopen($filename, 'w'); 
     
    // Set column headers 
    $fields = array('time', 'value'); 
    fputcsv($f, $fields, $delimiter); 
         
    // Output each row of the data, format line as csv and write to file pointer 
    while($row = $query->fetch_assoc()){ 
        //echo(strtotime($row['time'])*1000);
        //$status = ($row['status'] == 1)?'Active':'Inactive'; 
        if (($row['time']) === false) {
            die('failed');
        } else {
            $lineData = array(1000*strtotime($row['time']), $row['value']); 
            fputcsv($f, $lineData, $delimiter); 
        }
    } 
     
    fclose($f);
}
?>

[JimT (jimtng)]

Aside from the problem you’re facing, it seems to me that you should be able to do what you’re doing in php with jsscripting, thus completely avoiding the need to execute an external (php) script altogether. You can use jsscripting + JDBC to connect to mysql. Remember you have the combined features of Java AND jsscripting at your disposal. You can create a Java object (e.g. jdbc), or install third party js packages.

Getting back to the issue at hand, you’ve only shown the php script. The issue is in your jsscripting call that you claimed to log “null”. That’s the part that we need to see.

3. Your php script just reads from a table and outputs it to a csv, it doesn’t output anything to stdout, and only to stderr (via die) if an error occurred. So naturally no output to be expected. I presume you’re aware of this.

[milo (M)]

I am happy if you can help me with a better JS script which can do my phone code as well…I am not so familiar with JS…

Here is my code in the rule

console.log(actions.Exec.executeCommandLine("/usr/bin/php","/etc/openhab/scripts/mysqltocsv.php","number_airsensor1_co2"));

That’s it

Yes, I am aware of this… But the file is not created…

[JimT (jimtng)]

There are two versions of executeCommandLine. The one you called is a function that returns void.

The other version returns a String, I think it’s the stdout. Either way, it won’t show you anything because your php script doesn’t output anything.

You could start by executing a simple php script <?php echo "test"; ?> and execute that from your jsscripting (using the version that returns you the output, i.e. use a Duration as the first argument).

That would tell you whether the script got executed.

[JimT (jimtng)]

Also, assuming the execution worked, does the openhab user have write permission on /etc/openhab/html/Graph/ ?

Add some error handling in your php script to help you troubleshoot problems.

[JimT (jimtng)]

@milo

I am happy if you can help me with a better JS script which can do my phone code as well…I am not so familiar with JS…

I am not familiar with JS myself. I went from RulesDSL → Jython → Jruby. I can give it a crack in jruby if that’s the path you want to explore. Alternatively, someone else who’s more used to jsscripting might chime in.

I can’t help but wonder what you’re going to do with the CSV though? Is that yet another intermediate step to achieve the end goal that you could perhaps even skip altogether?

Lastly I also noticed that your SQL schema is a bit weird. Why have one table for each itemid? That seems to violate good database design principles. Is that from persistence or something?

I’m starting to suspect that this is an XY problem, but since we aren’t given the whole story, it’s hard to tell.

[milo (M)]

I used

sudo -u openhab /etc/openhab/scripts/mysqltocsv.php 

to check regarding write permissions and there was no problem

At the end I want to use Highcharts to display nice graphs for the openhab items therefore database schema is set by openhab and I need csv or json for Highcharts at the end.

[rlkoshak (Rich Koshak)]

@JimT

Lastly I also noticed that your SQL schema is a bit weird. Why have one table for each itemid? That seems to violate good database design principles. Is that from persistence or something?

Time series data often looks like that is an SQL database unfortunately. That’s one reason specific time series databases like InfuxDB were created.

I’m not sure that the Database driver library is make available to rules so I don’t know if it works be possible to code this entirely in JS unless there is a MysQL Node library that could be installed and used.

@milo , if the script is generating errors you’d never know it. Add a Duration to the call to executeCommandLine and log what it returns.

[milo (M)]

So…

<?php
echo "jj";
?>

console log is still null…

[JimT (jimtng)]

@rlkoshak

I’m not sure that the Database driver library is make available to rules so I don’t know if it works be possible to code this entirely in JS unless there is a MysQL Node library that could be installed and used.

In jruby I can use JDBC pretty easily. I’ve done it to create and access sqlite tables. I presume there should be something similar in JS, or at the very least, simply use Java’s objects, similar to what the jdbc persistence extension does. Just have to find the docs for the api.

@milo

So…

<?php
echo "jj";
?>

console log is still null…

Did you use the second form of executeCommandLine as per my link above? The first argument needs to be a Duration object. That’s when executeCommandLine would return the script’s output.

[rlkoshak (Rich Koshak)]

@JimT

or at the very least, simply use Java’s objects, similar to what the jdbc persistence extension does. Just have to find the docs for the api.

That’s the part I’m not sure is possible. Not every library used by every add-on is available in rules. Java provides a generic JDBC interface but those depend on a driver library for the specific database. I don’t think there is a way to make that driver library available to the rules, even though the JDBC persistence add-on uses it.

[JimT (jimtng)]

I don’t know how it’s done in Jsscripting, but I tried writing a quick test in jruby and it worked:

gemfile do
  source "https://rubygems.org"
  gem "jdbc-mysql"
  gem "sequel"
end

Sequel.jdbc("mysql://root:xxxxxx@172.18.0.3:3306/photoprism") do |db|
  db["show tables"].each do |row|
    logger.info row
  end
end