Multi-line SQL query doesn't throw PDOException, I can't detect execution failure

Need Help
1

Hi, I need to execute a large SQL query as explained here
https://book.cakephp.org/3.0/en/orm/database-basics.html#executing-queries
I don’t want to use ORM.

I found that when a query has more than one statement/line and there is a syntax error somewhere,
no PDO Exception is thrown.

This is a simplified version of my code

$conn = ConnectionManager::get('default');

try {
            sql="SELECT 1+1; BLAH BLAH BLAH; ";

            $stmt=$conn->prepare($sql);   
            $stmt->execute();
            $error = $stmt->errorInfo();
            pr($error);
                
} catch (\PDOException  $e) {
    pr($e);
 }

The exception never occurs and $error is always:

Array
(
[0] => 00000
[1] =>
[2] =>
)

I’m unable to detect whether it failed or not.

If I change the SQL just to one line like “BLAH BLAH BLAH;” the PDO is thrown

I read somewhere that all the rows in the the resultset has to be fetched in order to get invidual exceptions of each line. But I can’t find any example.

Any idea?

2 
    public function getTest()
    {
        $dbh = ConnectionManager::get('default');
        $sql = "SELECT dc_powners.ownerid, dc_powners.oname, ";
        $sql .= "COUNT(dc_pets.petid) AS CountOfpetid ";
        $sql .= "FROM dc_powners LEFT JOIN dc_pets ON ";
        $sql .= "dc_powners.ownerid = dc_pets.ownerid ";
        $sql .= "WHERE dc_powners.ownerid < :ownerid ";
        $sql .= "GROUP BY dc_powners.ownerid ";
        $sql .= "ORDER BY dc_powners.oname";
        $sth = $dbh->prepare($sql);
        $params = ['ownerid' => 4];
        $sth->execute($params);
        return $sth->fetchAll(\PDO::FETCH_OBJ);
    }

Cakephp will catch if there is an error, if I change dc_powners to dc_powner for example.

You could probably change the layout

<div id="footer">
            <?= $this->Html->link(__('Back'), 'javascript:history.back()') ?>
        </div>

To a redirect you choose, but if a user is getting PDO error, I have them contact admin.

To note, I use cake, laravel, and yii2 and also use the PDO instance in all three for thngs like reports. I have never wrapped a try catch around PDO, and have never had any errors.

Normally you can:

$dbh->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

But cake’s error handling is dealing with the errors. I would not mess with cake’s build in handling of errors, the Cakephp team knew what they were doing.

3

Hi, thanks for the answer. The reason why I want to catch the Exception is that I’m calling the controller method as a Shell task that inserts many records on the database and I need to know of some of the inserts failed.

I also want to wrap it in a transaction, but the problem occurs anyway.

The issue is that when the SQL string has more than one command and it has a syntax error somewhere, no exception is thrown. Even without try catch, when calling the action directly on the browser, no exception or error is thrown.

Anyway, I found out that if I split each sql command and call them individually, It works as expected

       try {
            $conn->begin();
            foreach ($sqls as $sql) {
                $stmt=$conn->prepare($sql);   
                $stmt->execute([]);
            }
            $conn->commit();   

       } catch (\PDOException  $e) {
            pr("PDOException");

        }
4

If that’s the case, have you considered writing a stored procedure. Just a thought.

Or using a new pdo connection, not cakes instance.