Calling MySql stored procedure from PHP showing error

Question

I am new in PHP. Create a stored procedure in Mysql database, now calling from my PHP code I get this error:

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

My procedure is:

DELIMITER $$
CREATE PROCEDURE InsertUser
(
    FirstName   VARCHAR(100),
    LastName        VARCHAR(100),
    Email           VARCHAR(50),
    Age         INT,
    Location        VARCHAR(100),
    OUT Res INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
     SET Res = 0;
    ROLLBACK;       
END;

START TRANSACTION;

INSERT INTO USERS
(firstname,lastname,email,age,location,date)
VALUES
(FirstName,LastName,Email,Age,Location,CURRENT_TIMESTAMP());

COMMIT;
SET Res = 1;
END;
$$

MY PHP Code is:

$connection = new PDO($dsn, $username, $password, $options);
$resStatus=false;
    //$res = 0;
    $statement = $connection->prepare("CALL InsertUser(?,?,?,?,?,?)");
    $statement->bindParam(1, $_POST['firstname'], PDO::PARAM_STR, 100);
    $statement->bindParam(2, $_POST['lastname'], PDO::PARAM_STR, 100);
    $statement->bindParam(3, $_POST['email'], PDO::PARAM_STR, 100);
    $statement->bindParam(4, $_POST['age'], PDO::PARAM_INT, 10);
    $statement->bindParam(5, $_POST['location'], PDO::PARAM_STR, 100); 
    $statement->bindParam(6, $res, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 0);         
    // call the stored procedure
    $statement->execute();
    if($res == 1)
    {
        $resStatus = true;
    }

My table:

CREATE DATABASE test;

use test;

CREATE TABLE users (
      id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
      firstname VARCHAR(30) NOT NULL,
      lastname VARCHAR(30) NOT NULL,
      email VARCHAR(50) NOT NULL,
      age INT(3),
      location VARCHAR(50),
      date TIMESTAMP
 );

What are you trying to achieve with your stored procedure? If you just want to make sure the row has been inserted, you can do an INSERT and then check rowCount() (php.net/manual/en/pdostatement.rowcount.php) — Nigel Ren
– Nigel Ren, Commented Jul 21, 2018 at 7:11
Can you give CREATE TABLE for your table, so I could replicate the issue? — user4035
– user4035, Commented Jul 21, 2018 at 7:24
@Raghubar Fixed the typo in procedure definition: "loation" -> "location" — user4035
– user4035, Commented Jul 21, 2018 at 8:52

user4035 · Accepted Answer · 2018-07-21 10:21:56Z

The problem was caused by a typo in your procedure. You typed "loation" instead of "location" in line:

(firstname,lastname,email,age,location,date)

Now it works:

mysql> CREATE PROCEDURE InsertUser
    (
         FirstName       VARCHAR(100),
         LastName        VARCHAR(100),
         Email            VARCHAR(50),
         Age                      INT,
         Location        VARCHAR(100),
         OUT Res                  INT
    )
    BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
      BEGIN
          SET Res = 0;
          ROLLBACK;
      END;

      START TRANSACTION;

      INSERT INTO USERS
      (firstname,lastname,email,age,location,date)
      VALUES
      (FirstName,LastName,Email,Age,Location,CURRENT_TIMESTAMP());

      COMMIT;
      SET Res = 1;
   END;
   $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> select * FROM users;
Empty set (0.00 sec)

mysql> Call InsertUser("aaa", "bbb", "[email protected]", 12, "Earth", @res);
Query OK, 0 rows affected (0.08 sec)

mysql> select * FROM users\G
*************************** 1. row ***************************
       id: 1
firstname: aaa
 lastname: bbb
    email: [email protected]
      age: 12
 location: Earth
     date: 2018-07-21 12:09:47
1 row in set (0.00 sec)

mysql> select @res;
+------+
| @res |
+------+
|    1 |
+------+
1 row in set (0.02 sec)

Talking about the PHP error, I looked into it, PARAM_INPUT_OUTPUT seems to be unstable. For me it didn't work too.

There is a workaround for this: using a MySQL variable and selecting it after running the CALL:

<?php
try {
    $username = 'user';
    $password = '';
    $conn = new PDO('mysql:host=localhost;dbname=test1', $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}

// call the stored procedure
$statement = $conn->prepare("CALL InsertUser(?,?,?,?,?,@result)");

$params =  array("aaa", "bbb", "[email protected]", 13, "Earth");
$statement->bindParam(1, $params[0], PDO::PARAM_STR, 100);
$statement->bindParam(2, $params[1], PDO::PARAM_STR, 100);
$statement->bindParam(3, $params[2], PDO::PARAM_STR, 100);
$statement->bindParam(4, $params[3], PDO::PARAM_INT, 10);
$statement->bindParam(5, $params[4], PDO::PARAM_STR, 100);

$statement->execute();

//select result
$sql = "SELECT @result";
$stmt = $conn->prepare($sql);
$stmt->execute();

list($result) = $stmt->fetch(PDO::FETCH_NUM);
var_dump($result);

Output: string(1) "1"

Collectives™ on Stack Overflow

Calling MySql stored procedure from PHP showing error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related