Hi,

I am trying to OUTPUT using PDO and MySQL but getting this error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 7 for routine bizcards.sp_subscribe is not a variable or NEW pseudo-variable in BEFORE trigger' in /home/meskholdings/aromaexpress/web/send_subscription.php:27 Stack trace: #0 /home/meskholdings/aromaexpress/web/send_subscription.php(27): PDOStatement->execute() #1 {main} thrown in /home/meskholdings/aromaexpress/web/send_subscription.php on line 27

this is my code:

$mysql_query = $mysql_connection->prepare('CALL sp_subscribe(:param_first_name, :param_last_name, :param_birthday, :param_mobile, :param_email, :param_created_ip, :param_promotion_code)');

$mysql_query->bindParam(':param_first_name', $first_name, PDO::PARAM_STR);
$mysql_query->bindParam(':param_last_name', $last_name, PDO::PARAM_STR);
$mysql_query->bindParam(':param_birthday', $borthday, PDO::PARAM_STR);
$mysql_query->bindParam(':param_mobile', $mobile_number, PDO::PARAM_STR);
$mysql_query->bindParam(':param_email', $email_address, PDO::PARAM_STR);
$mysql_query->bindParam(':param_created_ip', $_SERVER['REMOTE_ADDR'], PDO::PARAM_STR);
$mysql_query->bindParam(':param_promotion_code', $promotion_code, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT);

$mysql_query->execute();

$data = $promotion_code;

Thanks,
Jassim

    Did you set $promotion_code to a valid value before this code snippet?

    Have you tested sp_subscribe directly from the database command prompt, or otherwise know for sure that it works?

      Yes I tested it directly on the database server and Yes it's working on the database server. Here is the stored procedure:

      CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_subscribe`(IN param_first_name varchar(255), IN param_last_name varchar(255), IN param_birthday varchar(255), IN param_mobile varchar(255), IN param_email varchar(255), IN param_created_ip varchar(255), OUT param_promotion_code varchar(50))
BEGIN
	IF NOT EXISTS(SELECT person_id FROM persons_aroma_express WHERE personal_email = param_email OR business_email = param_email OR other_email = param_email OR mobile_1 = param_mobile OR mobile_2 = param_mobile) THEN
	BEGIN
		INSERT INTO persons_aroma_express
		(first_name, last_name, birthday, mobile_1, personal_email, created_ip)
		VALUES (param_first_name, param_last_name, param_birthday, param_mobile, param_email, param_created_ip);

	SET param_promotion_code = LAST_INSERT_ID();
END;
END IF;
END

      but I am still getting the error on the PHP.

        I'm guessing that for an "out" parameter in the stored procedure, you would use bindValue() instead of bindParam() (and thus would only need to specify the STR type).

          I tried BindValue and got this:

          Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 7 for routine bizcards.sp_subscribe is not a variable or NEW pseudo-variable in BEFORE trigger' in /home/meskholdings/aromaexpress/web/send_subscription.php:27 Stack trace: #0 /home/meskholdings/aromaexpress/web/send_subscription.php(27): PDOStatement->execute() #1 {main} thrown in /home/meskholdings/aromaexpress/web/send_subscription.php on line 27

            It may depend on your version of MySQL. Here are some paragraphs from the manual that seem relevant.

            To get back a value from a procedure using an OUT or INOUT parameter, pass the parameter by means of a user variable, and then check the value of the variable after the procedure returns....

            Before calling the procedure, initialize the variable to be passed as the INOUT parameter. After calling the procedure, the values of the two variables will have been set or modified:

            mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);
mysql> SELECT @version, @increment;
+--------------+------------+
| @version     | @increment |
+--------------+------------+
| 5.5.3-m3-log |         11 |
+--------------+------------+

            In prepared CALL statements used with PREPARE and EXECUTE, placeholders can be used for IN parameters. For OUT and INOUT parameters, placeholder support is available as of MySQL 5.5.3. These types of parameters can be used as follows:

            mysql> SET @increment = 10;
mysql> PREPARE s FROM 'CALL p(?, ?)';
mysql> EXECUTE s USING @version, @increment;
mysql> SELECT @version, @increment;
+--------------+------------+
| @version     | @increment |
+--------------+------------+
| 5.5.3-m3-log |         11 |
+--------------+------------+

            Before MySQL 5.5.3, placeholder support is not available for OUT or INOUT parameters. To work around this limitation for OUT and INOUT parameters, forego the use of placeholders; instead, refer to user variables in the CALL statement itself and do not specify them in the EXECUTE statement:

            mysql> SET @increment = 10;
mysql> PREPARE s FROM 'CALL p(@version, @increment)';
mysql> EXECUTE s;
mysql> SELECT @version, @increment;
+--------------+------------+
| @version     | @increment |
+--------------+------------+
| 5.5.0-m2-log |         11 |
+--------------+------------+

            So it may be a matter of sending multiple commands to set user variables, run the procedure, and query the variables for their results. That's something that would have to be tested by someone with MySQL. And is more comfortable with a manual that seems really pretty badly organised.

              Write a Reply...