#1 Fixed alarm insert and added PDO::prepare error handling

[Anonymous]

I had database permission issues that weren't visible until I added error handling in the Driver\sql.php. I then added error handling for all prepare statements.

While testing alarms, I noticed that alarms.php was raising alarms, but they were not being inserted into the database. Found a '!' instead of a '?' on a sqlInsert.

[Anonymous]

patch file for the Driver\sql.php

[Anonymous]

Here's the patch in unified format:

@@ -686,12 +686,15 @@
} else {
array_push($params, 3600); //default amount of time
//this isn't a problem cause then maxchange_alarm
//shouldn't be set
}

+ if (PEAR::isError($ps)) {
+ echo "Error preparing $sql ". $ps->getMessage();
+ }
//run the alarms query
$rs = $ps->execute($params);

$row = $rs->fetchRow(MDB2_FETCHMODE_ASSOC);
$realmin = $row['realmin'];
$realmax = $row['realmax'];
@@ -844,12 +847,15 @@
$sqlRead = 'SELECT alarm_id from ';
$sqlRead .= $this->_params['table_alarms'];
$sqlRead .= ' where serialnumber = ? and alarm_type = ? ';
$sqlRead .= ' and time_cleared is null ';

$ps = $this->_db->prepare($sqlRead);
+ if (PEAR::isError($ps)) {
+ echo "Error preparing $sqlRead ". $ps->getMessage();
+ }
$rs = $ps->execute(array($serial, $type));

if (!PEAR::isError($rs)) {
if ($rs->numRows() > 0) {
echo "updating alarm $type";
//already in table, update
@@ -862,24 +868,30 @@
$sqlUpdate .= ' set fahrenheit = ? ' ;
//$sqlUpdate .= ' and time_updated = null ';
$sqlUpdate .= ' , description = ? ';
$sqlUpdate .= ' where alarm_id = ? ';
echo "<BR>Running $sqlUpdate (temp = $temp)";
$ps = $this->_db->prepare($sqlUpdate);
+ if (PEAR::isError($ps)) {
+ echo "Error preparing $sqlUpdate ". $ps->getMessage();
+ }
$ps->execute(array( $temp, $description, $id ));

} else {
echo "inserting alarm $type";
//not yet in table, insert
$sqlInsert = ' INSERT into ';
$sqlInsert .= $this->_params['table_alarms'];
$sqlInsert .= ' (serialnumber, fahrenheit, alarm_type, time_raised, description) ';
- $sqlInsert .= ' VALUES (?, ?, ?, !, ?)';
+ $sqlInsert .= ' VALUES (?, ?, ?, ?, ?)';

$ps = $this->_db->prepare($sqlInsert);
+ if (PEAR::isError($ps)) {
+ echo "Error preparing $sqlInsert ". $ps->getMessage();
+ }
$ps->execute(array($serial, $temp, $type, 'now()' , $description));

//hope all was well
$wasNew = true;

}
@@ -915,12 +927,15 @@

$sqlUpdate = 'DELETE from ';
$sqlUpdate .= $this->_params['table_alarms'];
$sqlUpdate .= ' where serialnumber = ?';

$ps = $this->_db->prepare($sqlUpdate);
+ if (PEAR::isError($ps)) {
+ echo "Error preparing $sqlUpdate ". $ps->getMessage();
+ }
return $ps->execute(array($serial));
}

/**
* counts alarms (active or not)
@@ -930,12 +945,15 @@

$sqlUpdate = 'SELECT count(1) from ';
$sqlUpdate .= $this->_params['table_alarms'];
$sqlUpdate .= ' where serialnumber = ?';

$ps = $this->_db->prepare($sqlUpdate);
+ if (PEAR::isError($ps)) {
+ echo "Error preparing $sqlUpdate ". $ps->getMessage();
+ }
$qr = $ps->execute(array($serial));

$result = 0;
if (!PEAR::isError($qr)) {
$row = $qr->fetchRow();
$result = array_pop($row);
@@ -1101,12 +1119,15 @@
$time = time() - 3600 * 24 * 365;

$query = "SELECT count(*) from digitemp where time > FROM_UNIXTIME($time)";
$stop_watch['Start'] = microtime();
$ps = $this->_db->prepare($query);
$stop_watch['prepared']= microtime();
+ if (PEAR::isError($ps)) {
+ echo "Error preparing $query ". $ps->getMessage();
+ }
$rs = $ps->execute(array($serial, $type));
$stop_watch['executed one year count']= microtime();

if (!PEAR::isError($rs)) {
if ($rs->numRows() > 0) {

@@ -1120,12 +1141,15 @@

$stop_watch['starting timing actual read'] = microtime();
$query = "SELECT * from digitemp where time > FROM_UNIXTIME($time)";

$ps = $this->_db->prepare($query);
$stop_watch['prepared read']= microtime();
+ if (PEAR::isError($ps)) {
+ echo "Error preparing $query ". $ps->getMessage();
+ }
$rs = $ps->execute(array($serial, $type));
$stop_watch['executed one year read']= microtime();

if (!PEAR::isError($rs)) {
if ($rs->numRows() > 0) {
@@ -1271,16 +1295,17 @@
global $conf;

$sql="INSERT INTO digitemp SET SerialNumber=?,Fahrenheit=?";
$ps = $this->_db->prepare($sql);
if (PEAR::isError($ps)) {
echo "Prep error: $sql";
+ echo "message: ". $ps->getMessage();
return;
}
$ps->execute(array($serial,$temperature));

$ps->free();

}

}
?>