Error reporting for DB queries

[plb]

Using US 4.1.

How does one tell the PDO object to start dying (with informative messages) on errors? While under development it's a lot easier to let it die and tell me why than have it continue on its way without saying anything.

Obviously I can (and will) check the return value of each DB call, but while I'm in the development phase I'm used to leaning on those error messages. What settings is it that's making it so PDO errors get ignored?

(I just had an $db->insert() with the wrong table name and banged my head against it for a long time because I just didn't notice the typo - an error message would have called my attention to it immediately.)

Is this something in my wamp configuration or something set within UserSpice? I just reinstalled Wamp and so I've changed 2 things and I'm not sure which one to chase down...

[mudmin]

Let me check on that. I've never used PDO in that way. I'll get back to you.

[mudmin]

Let me check on that. I've never used PDO in that way. I'll get back to you.

[plb]

How do you normally get the error messages? I can't find anything in the DB class for that...

[waynez]

Hi, Did you ever figure out how to capture DB class errors - I've just had the same issue where a typo in a table name took hours to figure out, Im sure I must have other errors that are just not showing up.

Any help especially during development to display DB class errors would be appreciated, of if there is some other way of debugging DB errors would be appreciated.

Thanks

[mudmin]

I need to work on that. I've just gotten pretty decent at catching them. I'm not sure that PDO throws errors when something is wrong on an update query or something like that. I'm checking with another developer and I will follow up on this.

[jmac]

I think this is your answer, just need to work out how to work it into the existing database structure.

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

Something like:
$this->_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

[jmac]

After adding in the below line of code into the DB Class for an insert command
$this->_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

I receive the below error:

Code:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error' in /var/www/fas/users/classes/DB.php:57 Stack trace: #0 /var/www/fas/users/classes/DB.php(57): PDOStatement->fetchAll(5) #1 /var/www/fas/users/classes/DB.php(123): DB->query('INSERT INTO job...', Array) #2 /var/www/fas/includes/helpers/form_helpers.php(91): DB->insert('jobs', Array) #3 /var/www/fas/confirm_job.php(17): insert_job_form('XX', 'NA..') #4 {main} thrown in /var/www/fas/users/classes/DB.php on line 57

Is there any issue with the public function query($sql, $params = array()) Do you get the same error thrown if you add in the error reporting and run any insert SQL commands? The insert command seems to work so it looks like it is throwing an error while running this check:

<pre>

Code:

if (!$this->query($sql, $fields)->error()) {
            return true;
        }

</pre>

[jmac]

So it appears that that the query in the DB class is not ideal for Insert or Update statements.

The following two lines in the query function are not available to insert or update statements as nothing is returned and as such the Fatal error above is being produced.

$this->_results = $this->_query->fetchALL(PDO::FETCH_OBJ);
$this->_resultsArray = json_decode(json_encode($this->_results),true);

All I did was create a query2 function and commented out the two lines. I then updated my Insert statement to use the query2 function instead of the query function.

public function query2($sql, $params = array()){
$this->_queryCount++;
$this->_error = false;
if ($this->_query = $this->_pdo->prepare($sql)) {
$x = 1;
if (count($params)) {
foreach ($params as $param) {
$this->_query->bindValue($x, $param);
$x++;
}
}

if ($this->_query->execute()) {
#$this->_results = $this->_query->fetchALL(PDO::FETCH_OBJ);
#$this->_resultsArray = json_decode(json_encode($this->_results),true);
$this->_count = $this->_query->rowCount();
$this->_lastId = $this->_pdo->lastInsertId();
} else{
$this->_error = true;
}
}
return $this;
}

mudmin I'd like to get your thoughts on the above? Is this change safe to make or do you see it breaking something else?

I am running 4.2.6 this may be sorted in a later release. I assume it will break once I upgrade if not.

[karsen]

Instead of creating a near-duplicate method, I'd probably just modify the original slightly:

Code:

if ($this->_query->execute()) {

Code:

$type = substr($sql, 0, 6);

Code:

if ($type == 'SELECT') {

Code:

$this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);

Code:

$this->_resultsArray = json_decode(json_encode($this->_results), true);

Code:

}

Code:

if ($type == 'INSERT') {

Code:

$this->_lastId = $this->_pdo->lastInsertId();

Code:

}

Code:

$this->_count = $this->_query->rowCount();

Code:

} else {

Code:

$this->_error = true;

Code:

}

This way you don't have to modify any code you've already written.

You probably won't have to worry about it being overwritten unless mudmin actually changes the class. Nearly all version updates deal with implementation and not the classes, and I'd suspect that if he does change the DB class it would be to make a similar change.