php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #77933 Unable to get Parameter Out
Submitted: 2019-04-23 17:21 UTC Modified: 2020-07-24 13:12 UTC
From: jose dot castro at stccorp dot net Assigned:
Status: Verified Package: PDO MySQL
PHP Version: 7.2.17 OS: *
Private report: No CVE-ID: None
 [2019-04-23 17:21 UTC] jose dot castro at stccorp dot net
Description:
------------
$outdata = 0;
$stmt = $pdo->prepare("CALL sp_insert_test(?,?,?,?,?,?)");
$stmt->bindParam(1, $d1, PDO::PARAM_INT);
$stmt->bindParam(2, $dtype, PDO::PARAM_STR);
$stmt->bindParam(3, $d2, PDO::PARAM_INT);
$stmt->bindParam(4, $d3, PDO::PARAM_INT);
$stmt->bindParam(5, $title, PDO::PARAM_STR);
$stmt->bindParam(6, $newid, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT);
$stmt->execute();

echo "NEW IS IS : . $newid;

Test script:
---------------
CREATE TABLE `test_table` (
  `tid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `d1` int(10) unsigned DEFAULT NULL,
  `dtype` varchar(10) DEFAULT NULL,
  `d2` int(10) unsigned DEFAULT NULL,
  `d3` int(10) unsigned DEFAULT NULL,
  `title` varchar(200) DEFAULT NULL,
  `dateCreated` datetime NOT NULL,
  `dateUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`tid`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE PROCEDURE sp_insert_test(IN p_d1 INT,IN p_dtype VARCHAR(10),IN p_d2 INT,IN p_d3 INT,IN p_title VARCHAR(200), OUT p_out INT)
BEGIN

INSERT INTO test_table (d1,dtype,d2,d3,title,dateCreated) values (p_d1,p_dtype,p_d2,p_d3,p_title,NOW());

SELECT LAST_INSERT_ID() INTO p_out;

END

Expected result:
----------------
Expected the last id of the table

Additional info:
MYSQL version: 5.7.19

Actual result:
--------------
always get 0

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-07-24 13:12 UTC] cmb@php.net
-Status: Open +Status: Verified
 [2020-07-24 13:12 UTC] cmb@php.net
Firstly, you should check the return value of $stmt->execute()
and/or change the error mode to warning or exception (this is
generally a good idea).  This shows that emulated prepares have an
issue with the procedure call:

| SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or
| INOUT argument 6 for routine bug77933.sp_insert_test is not a
| variable or NEW pseudo-variable in BEFORE trigger

So let's try with native prepares (i.e. emulated prepares off).
This way $stmt->execute() succeeds, and there are no
warnings/exceptions, but still we get NULL.  Apparently, PDO_MySQL
does not yet support in/out or out parameters.  According to the
docs[1] that would not be a bug, though:

| Some drivers support the invocation of stored procedures that
| return data as output parameters, and some also as input/output
| parameters that both send in data and are updated to receive it.

So, obviously, there are two unrelated issues; thus, I have filed
a separate ticket for supporting in/out and out parameters as bug
#79893.

[1] <https://www.php.net/manual/en/pdostatement.bindparam.php>
 [2020-07-24 13:12 UTC] cmb@php.net
-Operating System: Windows 10 +Operating System: *
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Nov 21 12:01:29 2024 UTC