PHP query into domoticz.db not working

[twbon]

Hi All,

I am trying to get data out of the domoticz.db with sqlite3 and php. For some reason I cannot get it to work.

what i do in PHP is:

Code: Select all

$dbname = "/home/name_user/domoticz/domoticz.db";
if($db = new SQLite3($dbname)) {
  echo("<br>Link met DB succes");
  }
$res = $db->query("select * from zwavenodes"); //simple Q as example
while ($rij = $res->fetchArray()) {
  echo($rij[4]);
  echo("<br>");
  }

This works just fine with another DB with the same rights AND the same query works when I use it in SQLite3 CLI directly.

The error log of nginx says it is unable to open the database file, but refers to the line where I 'fire' the query, not the line in which I try to open the DB. Opening the DB does not return FALSE...

Am I missing something?

Thanks, Tim

[Egregius]

The code is functional, so can only be a rights thing.
Depends on how you execute the code, from cli or from a webserver?
If it's from a webserver you gotta make sure that the users who runs the webserver has execute permissions to the database.
Write down the current rights and change them to 777 to test.

Your code could be a little tidier:

Code: Select all

if($db=new SQLite3('/var/log/cache/domoticz.db'))echo 'Link met DB succes<br>';
$res=$db->query('select * from zwavenodes');
while($rij=$res->fetchArray()){
  echo $rij[4].'<br>';
}

[twbon]

Egrerius, Thanks for pointing me in that direction. Although access rights were not the issue. After fiddling with them and comparing with rights and ownerships of DB I could access, I found out that I cannot access domoticz.db when domoticz is running. When I stop domoticz, I can access the DB.

So I guess my script to read from domoticz.db has to include making a temporary copy of the DB. Not a big deal and maybe safer anyway.

Cheers!

[Egregius]

I never stop domoticz when I use Adminer (php based SQL explorer) or if I wan't to clean the database.
Could be that I ran a query once to put the database in a multi user mode, can't remember.

[sincze]

I never stop domoticz when I use Adminer (php based SQL explorer) or if I wan't to clean the database.
Could be that I ran a query once to put the database in a multi user mode, can't remember.

Tnx for the tip. Did not know that.

Sent from my SM-G925F using Tapatalk