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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: jose dot castro at stccorp dot net
New email:
PHP Version: OS:

 

 [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: Sat Dec 14 00:01:26 2024 UTC