T-SQL in PHP with PDO

Question

I'm using php with the PDO sqlsrv driver to connect to an Azure SQL database. Mostly it's working fine until I tried to use the t-SQL convert function in my sql statement.

To be clear about what I'm asking: Why does this t-SQL not work in my PDO statement. Is this a limitation on using PDO with sqlsrv?

This statment works fine:

"SELECT Courses.courseName, Classes.classID, Classes.classStartDate, Classes.classEndDate, Classes.classStartTime ,Classes.classEndTime, Classes.location, Classes.classType, Courses.courseInfoLink  
        FROM Classes 
        JOIN Courses
        ON Courses.courseID = Classes.courseID 
        JOIN ClassRegistration 
        ON Classes.classID = ClassRegistration.classID 
        JOIN Employees ON 
        ClassRegistration.employeeID = Employees.employeeID 
        WHERE Employees.employeeID = {$employeeID} AND ClassRegistration.isCompleted = 0 AND Classes.classStartDate > GETDATE()"

This statment does not (though it does work in the Azure SQL management portal so I know it's valid T-SQL):

 "SELECT Courses.courseName, Classes.classID, Classes.classStartDate, Classes.classEndDate, convert(nvarchar(MAX),Classes.classStartTime, 0)+' - '+convert(nvarchar(MAX),Classes.classEndTime,0), Classes.location, Classes.classType, Courses.courseInfoLink  
        FROM Classes 
        JOIN Courses
        ON Courses.courseID = Classes.courseID 
        JOIN ClassRegistration 
        ON Classes.classID = ClassRegistration.classID 
        JOIN Employees ON 
        ClassRegistration.employeeID = Employees.employeeID 
        WHERE Employees.employeeID = {$employeeID} AND ClassRegistration.isCompleted = 0 AND Classes.classStartDate > GETDATE()"

When I use that statement I get an error message: "PHP Fatal error: Cannot access empty property in .../db.php on line 44"

Line 44 is "$this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);" from the query() function below

If it helps to see how this is being used then here's my related code:

public function query($sql, $params = array()) {
        $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->_count = $this->_query->rowCount();

            }
            else {
                $this->_error = TRUE;

            }
        }

        return $this;
    }

    public function getUpcomingClasses () {

         $employeeID = $this->_employeeID;


        $sql = "SELECT Courses.courseName, Classes.classID, Classes.classStartDate, Classes.classEndDate, convert(nvarchar(MAX),Classes.classStartTime, 0)+' - '+convert(nvarchar(MAX),Classes.classEndTime,0), Classes.location, Classes.classType, Courses.courseInfoLink  
            FROM Classes 
            JOIN Courses
            ON Courses.courseID = Classes.courseID 
            JOIN ClassRegistration 
            ON Classes.classID = ClassRegistration.classID 
            JOIN Employees ON 
            ClassRegistration.employeeID = Employees.employeeID 
            WHERE Employees.employeeID = {$employeeID} AND ClassRegistration.isCompleted = 0 AND Classes.classStartDate > GETDATE()";

            $x= 0;
            foreach ($this->_db->query($sql)->results() as $results) {
                $classes[$x] = $results; 
                $x++;
            }

            return $classes;
    }

**** this was marked as a possible duplicate of how to get an error out of PDO. Not really sure that applies. Though that might help, it's not the cause of the issue. As the real question here is "Is this a limitation on using PDO with sqlsrv?"

I'm always up for anything that might help so I added $this ->_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); to my PDO and then modified my code with a try catch in the query() function as below:

try { 
                $this->_query->execute();

            }
            catch (PDOException $e) {
    $this->_error = "Error: " . $e->getMessage() . "<br />\n";
}
        }

        return $this;

I then modified my foreach loop to simply to return my error (I just have an error() function that returns $this->_error in my class that's running the queries:

echo $this->_db->query($sql)->error()"

which returned blank. So dumped it with:

var_dump($this->_db->query($sql)->error());

which returned "bool(false)". Since I set my error property to false when I initialize it, it would seem that it's not catching any specific error, correct?

* another update. I created a stored procedure and tried to execute that, but got the same result.
However I then changed my PDO query to Fetch an array (FETCH_ASSOC) instead of FETCH_OBJ and it's returning the array (both as I originally posted it and as a stored procedure). I can't find any documentation on this that would say why I couldn't fetch it as an object, so I would still appreciate any insight. thanks

You've got an error message ? Did you try to add an alias to your concatenated field ? — Raphaël Althaus, Jul 15 '14 at 16:03
error message: "PHP Fatal error: Cannot access empty property in .../db.php on line 44" Line 44 is "$this->_results = $this->_query->fetchAll(PDO::FETCH_OBJ);" from the query() function — Andy McCormick, Jul 15 '14 at 16:15

DinoAmino · Accepted Answer · 2014-07-15T18:19:16.663

1

The difference between the functioning and non-functioning queries is in your SELECTs. The one that fails includes concatenated conversions.

I've seen similar failings when simple complexity (how's that for an oxymoron?) is added to a query. Specifically, I was using both dblib and sqlserv PDO with Doctrine.

It might help adding parentheses to group the concatenation and give it a column alias:

SELECT
  Courses.courseName, Classes.classID, 
  Classes.classStartDate, Classes.classEndDate,
  (convert(nvarchar(MAX),Classes.classStartTime, 0) 
    + ' - ' 
    + convert(nvarchar(MAX),Classes.classEndTime,0)) AS timeRange, 
  Classes.location, Classes.classType, Courses.courseInfoLink  
...

edited Jul 15 '14 at 18:19

answered Jul 15 '14 at 17:53

DinoAmino

93
6

In fact, I'm almost sure the solution is to add a column alias. objects need named properties. – DinoAmino Jul 15 '14 at 18:21
1

jackpot! hence why the associate array worked with a blank property name. The key was "objects need named properties". I aliased the column to give it a name and now it works as both FETCH_OBJ and FETCH_Array thanks also to @raphaël-althaus who mentioned adding an alias to the field. But I only thought alias the individual columns not the concatenated column. I must admit I've never used mssql with concatination or functions like this so it was all new to me. Typically I return reach column and concatenate in php but thought i'd try something cleaner today. thanks for the help – Andy McCormick Jul 15 '14 at 18:25

T-SQL in PHP with PDO

1 Answers1