returning record identity using PDO

[jrahma]

Hi,

I am using MySQL, PHP and PDO to insert a support ticket. Inserting works fine but I want to get the ticket_id after insertions using OUT from the stored procedure. Can you please help.

here is my stored procedure followed by my PDO code for INSERT.

CREATE DEFINER=`jassimdb`@`%.%` PROCEDURE `sp_add_new_ticket`(IN param_ticket_name varchar(255), IN param_ticket_email varchar(255), IN param_ticket_subject varchar(255), IN param_ticket_message text, OUT param_record_identity int)
BEGIN
	INSERT INTO support_tickets (ticket_guid, ticket_name, ticket_email, ticket_subject) VALUES (UUID(), param_ticket_name, param_ticket_email, param_ticket_subject);
	SET param_record_identity = LAST_INSERT_ID();
	INSERT INTO support_ticket_messages (ticket_id, support_ticket_message) VALUES (LAST_INSERT_ID(), param_ticket_message);
END

$mysql_query = $mysql_connection->prepare("CALL sp_add_new_ticket(:param_ticket_name, :param_ticket_email, :param_ticket_subject, :param_ticket_message)");
$mysql_query->bindParam(':param_ticket_name', $ticket_name, PDO::PARAM_STR);
$mysql_query->bindParam(':param_ticket_email', $ticket_email, PDO::PARAM_STR);
$mysql_query->bindParam(':param_ticket_subject', $ticket_subject, PDO::PARAM_STR);
$mysql_query->bindParam(':param_ticket_message', $ticket_message, PDO::PARAM_STR);

$mysql_query->execute();

Thanks,
Jassim

[NogDog]

I believe...

// added one more parameter:
$mysql_query = $mysql_connection->prepare("CALL sp_add_new_ticket(:param_ticket_name, :param_ticket_email, :param_ticket_subject, :param_ticket_message, :param_record_identity)");
$mysql_query->bindParam(':param_ticket_name', $ticket_name, PDO::PARAM_STR);
$mysql_query->bindParam(':param_ticket_email', $ticket_email, PDO::PARAM_STR);
$mysql_query->bindParam(':param_ticket_subject', $ticket_subject, PDO::PARAM_STR);
$mysql_query->bindParam(':param_ticket_message', $ticket_message, PDO::PARAM_STR);
// bind that new output parameter:
$mysql_query->bindParam(':param_record_identity', $ticket_id, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 12);
$mysql_query->execute();  

[jrahma]

I tried your code but getting this error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 5 for routine jassimrahma_com.sp_add_new_ticket is not a variable or NEW pseudo-variable in BEFORE trigger' in /home/jassimuser/curesoftware.com/send_message.php:55 Stack trace: #0 /home/jassimuser/curesoftware.com/send_message.php(55): PDOStatement->execute() #1 {main} thrown in /home/jassimuser/curesoftware.com/send_message.php on line 55

here is the code:

$mysql_query = $mysql_connection->prepare("CALL sp_add_new_ticket(:param_ticket_name, :param_ticket_email, :param_ticket_subject, :param_ticket_message, :param_record_identity)");
$mysql_query->bindParam(':param_ticket_name', $ticket_name, PDO::PARAM_STR);
$mysql_query->bindParam(':param_ticket_email', $ticket_email, PDO::PARAM_STR);
$mysql_query->bindParam(':param_ticket_subject', $ticket_subject, PDO::PARAM_STR);
$mysql_query->bindParam(':param_ticket_message', $ticket_message, PDO::PARAM_STR);
$mysql_query->bindParam(':param_record_identity', $ticket_number, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 12);

$mysql_query->execute();

and here is the stored procedure:

CREATE DEFINER=`jassimdb`@`%.%` PROCEDURE `sp_add_new_ticket`(IN param_ticket_name varchar(255), IN param_ticket_email varchar(255), IN param_ticket_subject varchar(255), IN param_ticket_message text, INOUT param_record_identity int)
BEGIN
	INSERT INTO support_tickets (ticket_guid, ticket_name, ticket_email, ticket_subject) VALUES (UUID(), param_ticket_name, param_ticket_email, param_ticket_subject);
	SET param_record_identity = LAST_INSERT_ID();
	INSERT INTO support_ticket_messages (ticket_id, support_ticket_message) VALUES (LAST_INSERT_ID(), param_ticket_message);
END

[johanafm]

Sounds like you're using an older mysql server version which lacks support for this.

1. Check if your version of mysql server has support by:

   1. connecting to it

   2. creating a prepared statement with an out parameter

   3. prepare a statement for the above

   4. execute prepared statement using a session variable.

   DROP PROCEDURE IF EXISTS sp_out_test;
   DELIMITER ##
   CREATE PROCEDURE sp_out_test(OUT p INT UNSIGNED)
   BEGIN
       SET p := 1;
   END##
   DELIMITER ;
   
   SET @x := 0;
   PREPARE stmt FROM 'CALL sp_out_test(?)';
   EXECUTE stmt USING @x;
   SELECT @x;
   DROP PREPARE stmt;
   

   Expected output

   @x
   1
   

   If the above fails when executing the prepared statement with a "ERROR 1414 (42000): OUT or INOUT…" then your database server version does not support out parameters for prepared statement of procedure calls. But I believe this was fixed a long time ago. Perhaps 5.0 or 5.1?
   
   2. If the above works it is possible that client side prepared statements are used instead of server side prepared statements. If this is the case, the value of the "out" variable will be sent to the server, which you can verify by enabling query loggin and then inspecting it.
      First, create a log file if it doesn't already exist. Then set the owner of the log file to whatever user is running mysqld

      sudo ps aux | grep mysqld
      

      If you don't want to restart your server, you may issue the following statements given sufficient privileges.

      SET GLOBAL general_log_file = '/path/and/file.log';
      SET GLOBAL general_log = 1;
      

      Turning on general query logging will fail with an error message if the log file is not writeable by mysqld.
   Execute the called procedure using a prepared statement using php. Then check the log. If you do not see "Prepare" in the log, then server side prepared is not used, and the actual call statement is issued

   55 Connect
   55 CALL sp_out_test(NULL)
   

   I believe this would also result in error 1414: out or inout…
   
   Unfortunately when I looked into this, it still fails, albeit with a different error messages. I will provide a work around for you. However, for completeness I will show what it looks like on my end if anyone else has additional information to provide.
   PHP version: 5.5.3 (current is 5.5.4 - I'll try upgrading tonight, and then retry)
   Mysql version: 5.6.9 (current is 5.6.14 - I'll see if I can manage upgrading mysql tonight as well)
   

   55 Connect
   55 Prepare	CALL sp_out_test(?)
   55 Execute	CALL sp_out_test(NULL)
   

   which means server side prepares are used. I do not know if the log for the execute call should show the current value for the bound parameter or if this is another indication of a problem. Either way, I get two sets of error messages

   Premature end of data (mysqlnd_wireprotocol.c:1113)
   RSET_HEADER packet 1 bytes shorter than expected
   Error reading result set's header

   Premature end of data (mysqlnd_wireprotocol.c:1076)
   RSET_HEADER packet 4 bytes shorter than expected
   Error reading result set's header

   Workaround, shown using the same stored proc "sp_out_test" as above

   $out = null;
   $db->query("SET @out := 0");
   $s = $db->prepare('CALL sp_out_test(@out)');
   if ($s->execute()) {
       if ($s = $db->query("SELECT @out"))
       {
           $r = $s->fetch();
           echo $r['@out'];
       }
   }
   

   You would of course still be binding your input parameters using named parameters just like before.
   
   If you need an inout parameter, you simply need to first assign the appropriate value to your session variable.

   # If you want to use prepared statements when assigning value to session variables, this will work fine
   $s = $db->prepare("SET @inout := :in_now_out_later");
   $s->bindValue('in_now_out_later', 'in_value', PDO::PARAM_STR);
   $s->execute();
   
   # Using session variables enables the use of output parameters
   $s = $db->prepare('CALL insert_user(:in, @inout)');
   $s->bindValue('in', 'only in - not out', PDO::PARAM_STR);
   $s->execute();
   $s = $db->query("SELECT @inout AS out");
   $r = $s->fetch();
   echo $r['out'];