asked on

Calling an MySQL Stored Prodecure from a php function with input and output parameters

In my ongoing effort to convert all of my company's MSSQL data to MySQL, I need more help with a mySQL variant of a php function.

In this instance, I have a stored procedure that takes 2 inputs and returns one output. It takes the name of the table and a unique ID number and returns the record id. I'm having trouble getting the syntax right and after looking at numerous examples over the past few days, I'm just confused and getting the same errors.

This is the sproc I'm using:

DROP PROCEDURE IF EXISTS `sp_GetRecordID`;

DELIMITER //

CREATE PROCEDURE `sp_GetRecordID`(
  `tablename_In` VARCHAR(64),
  `formSecret_In` VARCHAR(32),
  OUT `id_Out` BIGINT UNSIGNED
)
BEGIN
  SET @`query` := CONCAT('SELECT `id` INTO @`id_Out`
                          FROM ', `tablename_In` ,' 
                          WHERE `formSecret` = \'', `formSecret_In`, '\'');
  PREPARE `stmt` FROM @`query`;
  EXECUTE `stmt`;
  SET `id_Out` := @`id_Out`,
       @`query` := NULL;
  DEALLOCATE PREPARE `stmt`;
END//

DELIMITER ;

Open in new window

Then, I call the stored procedure and try to get the id to return. Eventually, I'll put this in a function but I want to get it to run out of a function first and build from there. The query I'm trying to use is:

            $stmt = $pdo->prepare("CALL sp_GetUserID(?,?)");
			$v1 = "tbl_JustSayThanks";
			$v2 = "16e90ccf482e72b9a562f5613b659fed";
            $stmt->bindValue(':tablename_In',$v1, PDO::PARAM_STR);
            $stmt->bindValue(':formSecret_In',$v2, PDO::PARAM_STR);
			$stmt->execute();
			foreach ($data as $row) {
                return $row['id_Out'];
            }

Open in new window

Which results in an error of: "Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined' in confirmation.php on line 41" - which is the line the execute statement is on.

I've tried it with and without output parameters defined, different formats and keep getting error after error. This last attempt we listed from the PHP manual on prepared statements and still no luck.

I'm a rank newb at MySQL so I'm sure I've got this all balled up but would appreciate some assistance untangling it.

arnold

You have two arguments that the sp expects two in one out, while your prepare statement includes only two placeholders .
You are missing the out/response output variable

saabStory

ASKER

I thought that as well, yet when I change the routine to include the third placeholder I get the same error message. The code with the third placeholder looks like:

                $stmt = $pdo->prepare("CALL sp_GetUserID(?,?,?)");
                $v1 = "tbl_JustSayThanks";
                $v2 = "16e90ccf482e72b9a562f5613b659fed";
                $v3 = "";
                $stmt->bindValue(':tablename_In',$v1, PDO::PARAM_STR);
                $stmt->bindValue(':formSecret_In',$v2, PDO::PARAM_STR);
                $stmt->bindValue(':id_Out',$v3, PDO::PARAM_INT);
                $stmt->execute();
                $data = $stmt->fetchAll();
                foreach ($data as $row) {
                    return $row['id_Out'];
                }

Open in new window

And, if I switch it out to use bindParam instead of bindValue, the error I get is: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1305 PROCEDURE gsnetx_web_events.sp_GetUserID does not exist - and references the execute as the source of the error. That code is:

                $stmt = $pdo->prepare("CALL sp_GetUserID(?,?,?)");
                $v1 = "tbl_JustSayThanks";
                $v2 = "16e90ccf482e72b9a562f5613b659fed";
                $v3 = "";
                $stmt->bindParam(1,$v1, PDO::PARAM_STR);
                $stmt->bindParam(2,$v2, PDO::PARAM_STR);
                $stmt->bindParam(3,$v3, PDO::PARAM_INT);
                $stmt->execute();
                $data = $stmt->fetchAll();
                foreach ($data as $row) {
                    return $row['id_Out'];
                }

Open in new window

I'm following the examples from the pdo section in the php manual - I'm sure I'm missing something very basic, but I just can't see it.

arnold

when using param your third item is pod::param_inout Xor int.
Data_type http://php.net/manual/en/pdo.constants.php
Though I think your out is a table not an integer based on your sp.

saabStory

ASKER

For starters, I'm an idiot - but still have a problem with the code. I'm converting all of our web forms from MSSQL to MySQL, copied some code and forgot to change the name of the procedure I was calling.

While that resolves this error, I get a new one whether using named parameters or placeholders. Using named parameters, the code looks like:

                $stmt = $pdo->prepare('CALL sp_GetRecordID(:tableName_In,:formSecret_In,:id_Out)');
                $v1 = "tbl_JustSayThanks";
                $v2 = "16e90ccf482e72b9a562f5613b659fed";
                $v3 = NULL;
                $stmt->bindParam(':tableName_In',$v1, PDO::PARAM_STR);
                $stmt->bindParam(':formSecret_In',$v2, PDO::PARAM_STR);
                $stmt->bindParam(':id_Out',$v3, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT,11);
                $stmt->execute();
                $data = $stmt->fetchAll();
                foreach ($data as $row) {
                    return $row['id_Out'];
                }

Open in new window

and the resulting error is:
"Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 3 for routine gsnetx_web_events.sp_GetRecordID is not a variable or NEW pseudo-variable in BEFORE trigger' in confirmation.php on line 44"

If I go with the placeholders i.e CALL_sp_GetRecordID(?,?,?) and change the bindings accordingly, I get the same error.

ASKER CERTIFIED SOLUTION

arnold

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

saabStory

ASKER

I finally figured this out. Everything I've read about MySQL indicated I needed output variables for the queries. I had previously converted a query and it was working without an ouput, so I thought why not. Created a different procedure without the output variable. Changed the php code to suit and everything worked. Moved all the code to a function and everything works. The really crazy part is that the syntax I was using for MSSQL is exactly the same as I wound up using for MySQL. Doesn't seem right though.

I will give the procedure with the output variable another try though but make the output an int instead of a bigint and see what that does as I'd really like to understand it better.

Thanks for sticking with me on this goose chase - I do appreciate all the help and reference material to study.