CODEWITHSUNDEEP
phpmysqlstored-proceduresprepared-statement
Azima
Azima

Reputation: 4141

prepared statement inside stored procedure not working

I am trying to execute prepared statement inside stored procedure.

But I'm getting issues with it.

Here's the stored procedure:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllUserLunchReport`(IN `start_date` DATETIME, IN `end_date` DATETIME)
BEGIN

SET GLOBAL group_concat_max_len=4294967295;
SET @SQL = NULL;
SET @start_date = DATE(start_date);
SET @end_date = DATE(end_date);

    SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
        'SUM(CASE WHEN date = "',DATE(issuedDateTime),'" THEN lunchStatus ELSE 0 END) AS `',DATE(issuedDateTime),'`'
        )
    ) INTO @SQL

        FROM `lunch_status` 
        WHERE DATE(issuedDateTime) BETWEEN @start_date AND @end_date;

    SET @SQL 
    = CONCAT('SELECT userId, ', @SQL, ' 
    FROM
    (
        SELECT userId, lunchStatus, DATE(issuedDateTime) as date 
        FROM `lunch_status` 
        WHERE DATE(issuedDateTime) BETWEEN "',@start_date,'" AND "',@end_date,'" 
    ) as a
    GROUP BY userId;');

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END$$
DELIMITER ;

And here's the error:

PDOStatement: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL' at line 1

Here's the controller:

public function weeklyLunchReport($date1=null, $date2=null)
{
    $user = AclHelper::getCurrentUser();
    $default_userID = intval($user['user_id']);
    $defDate = new DateTime();
    $default_date = $defDate->format('Y-m-d');

    $date1 = (isset($date1) && $date1 != '') ? $date1 : $default_date;
    $date2 = (isset($date2) && $date2 != '') ? $date2 : $default_date;

    $records = $this->db->exec("CALL getAllUserLunchReport ('$date1','$date2')");
    return $records;
}

ALSO: here's the value of SQL variable:

SELECT userId, 
SUM(CASE WHEN date = "2018-06-03" THEN lunchStatus ELSE 0 END) AS `2018-06-03`,
SUM(CASE WHEN date = "2018-06-04" THEN lunchStatus ELSE 0 END) AS `2018-06-04`,
SUM(CASE WHEN date = "2018-06-06" THEN lunchStatus ELSE 0 END) AS `2018-06-06`,
SUM(CASE WHEN date = "2018-06-07" THEN lunchStatus ELSE 0 END) AS `2018-06-07`,
SUM(CASE WHEN date = "2018-06-08" THEN lunchStatus ELSE 0 END) AS `2018-06-08` 
   FROM
   (
       SELECT userId, lunchStatus, DATE(issuedDateTime) as date 
       FROM `lunch_status` 
       WHERE DATE(issuedDateTime) BETWEEN "2018-06-03" AND "2018-06-09" 
   ) as a
   GROUP BY userId;

Running this query string separately is giving the expected resultset.

The query seems to be all fine, but I have no idea where it went wrong.

Any help is very much appreciated. Thanks in advance.

Upvotes: 1

Views: 877

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562320

I tried your procedure in the mysql client, so to rule out any chance the problem is related to PHP:

mysql> create table lunch_status (userId int, lunchstatus int, issueddatetime datetime);

mysql> DELIMITER $$
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllUserLunchReport`(IN `start_date` DATETIME, IN `end_date` DATETIME)
    -> BEGIN ...

mysql> CALL getAllUserLunchReport ('2018-06-17','2018-06-17');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1
mysql> select @SQL;
+------+
| @SQL |
+------+
| NULL |
+------+

But when I tried inserting a row that falls in the date range:

mysql> insert into lunch_status values (123, 1, now());
Query OK, 1 row affected (0.02 sec)

mysql> CALL getAllUserLunchReport ('2018-06-17','2018-06-17');
+--------+------------+
| userId | 2018-06-17 |
+--------+------------+
|    123 |          1 |
+--------+------------+

The problem is that your GROUP_CONCAT() generates NULL for the pivot-table column list when there are no rows in the date range you're searching for.

Then you CONCAT() that NULL into your full SELECT statement, but CONCAT('SELECT ...', NULL, 'FROM ...') yields NULL, because concatenating any string with NULL yields NULL.

You need to make sure your GROUP_CONCAT() defaults to some non-NULL string:

SELECT
    COALESCE(GROUP_CONCAT(DISTINCT
        CONCAT(
        'SUM(CASE WHEN date = "',DATE(issuedDateTime),'" THEN lunchStatus ELSE 0 END) AS `',DATE(issuedDateTime),'`'
        )
    ), '0 as `NoMatchingRows`') INTO @SQL

Calling the procedure will still return no result set, because there are no dates in the table matching the date range you gave. But at least it won't get a syntax error when you try to prepare NULL.

Upvotes: 3

Related Questions