3

I have a MySQL stored proc that returns a UUID via an output parameter. I'm calling this proc from PHP using PDO - but I'm just getting null back for the output param... There's no explicit binary option for the output data type (PDO::PARAM_???) - the closest I guess is LOB. Does anyone know how to do this?Here's my PHP code:

public function CreateTenant($name)
{
    $qry = $this->conn->prepare("call spInsertTenant(:name, :userId, :id)");
    $qry->bindParam(':name', $name);
    $qry->bindParam(':userId', $this->currUser);
    $qry->bindParam(':id', $id, PDO::PARAM_LOB, 16);
    try {
        $qry->execute();
        return $id;
    } catch (PDOException $e) {
        echo $e->getMessage();
        exit;
    }
}

EDIT: Ok - the original proc is pretty long. Here's a combination of MySQL and PHP I knocked up that is having the same problem. Note - there's nothing inserted into my table - even though no errors are thrown. Thanks Mihai for the nod towards declaring the $vars first... Tried that without any luck. Also trincot - my UUIDs are definitely binary - they're using this: https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/

Here's the stored proc:

CREATE TABLE IF NOT EXISTS `Tenant2` (
  `ID` BINARY(16) NOT NULL,
  `Name` VARCHAR(45) NULL,
  PRIMARY KEY (`ID`));

drop procedure if exists spInsertTenant2;
delimiter $$
CREATE PROCEDURE `spInsertTenant2`(pName nvarchar(45), out oId binary(16))
begin

    set @id = ordered_uuid(uuid());

    insert  Tenant2 (ID, Name)
    values  (@id, pName);

    set oId = @id;

end$$
delimiter ;

call spInsertTenant2('Test', @id);
select * from Tenant2;
select @id, hex(@id);

And here's some PHP to hook into this:

...
// Create connection
$this->conn = new PDO('mysql:dbname=' . $this->database . ';host=' . $this->servername, $this->username, $this->password);
...

function testParam()
{
    try {
        $name = 'SomeName';
        $id = 0;
        $qry = $this->conn->prepare('call spInsertTenant2(:name :id)');
        $qry->bindParam(':name', $name);
        $qry->bindParam(':id', $id, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT);
        $qry->execute();
        print $id;
    } catch (PDOException $e) {
        echo $e->getMessage();
        exit;
    }
}

Note the connection to the DB is working - I'm able to execute select statements and get data back no probs.

Matt Wales
  • 101
  • 5
  • 1
    `':name'` remove the quotes. check for errors here http://php.net/manual/en/pdo.error-handling.php would have triggered that error. – Funk Forty Niner Mar 03 '16 at 20:35
  • 1
    possible duplicate of [When to use single quotes, double quotes, and backticks?](http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks) – Funk Forty Niner Mar 03 '16 at 20:36
  • Thanks Fred - removing those quotes helped, BUT - I'm still not getting a value back from the output parameter in the proc. As per your suggestion I've added try / catch - the code is not throwing any exceptions... – Matt Wales Mar 03 '16 at 20:42
  • As fred ii says, binding values to a prepare auto escapes the values. While MySQL generously accepts quoted integers others do not unless defined `PDO::TYPE` – Xorifelse Mar 03 '16 at 20:43
  • Yep - I get the quotes thing... It's the binary output parameter I'm stuck on. – Matt Wales Mar 03 '16 at 20:47
  • bindParam passes by reference so you need the variable already existing BEFORE passing Create the variable at the beginning of the function`$var=$this->currUser;` and pass $var insted – Mihai Mar 03 '16 at 21:03
  • Are you sure your UUID is binary? If it corresponds to a call to MySql's [UUID()](http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_uuid), function then the data type is string. So you would use `PDO::PARAM_STR` and length 36. However, you'll have maybe better results without specifying length, and passing for options: `PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT` – trincot Mar 03 '16 at 21:05
  • Could you provide the declaration of your Stored procedure to see how you have typed the third parameter? – trincot Mar 03 '16 at 21:18
  • @MattWales Try it without the custom `testParam()` - `CreateTenant()` functions. If it works, then it's a variable scope issue. – Funk Forty Niner Mar 03 '16 at 22:07
  • @MattWales but this `spInsertTenant2(:name :id)` there should be a comma in there `spInsertTenant2(:name, :id)` and use the error checking links I left up there. – Funk Forty Niner Mar 03 '16 at 22:08
  • Hey @Fred -ii- Thanks - I had added a try/catch but hadn't set the exception attributes against the connection so they'd actually catch. After doing this (and fixing the comma) I'm getting "SQLSTATE[42000]: Syntax error or access violation: 1414 OUT or INOUT argument 2..." - seems a few other people have had this, and it's nothing to do with binary or otherwise; rather, the official PDO documentation doesn't work. The workaround involves a second round trip to the DB server - not ideal. http://forums.mysql.com/read.php?98,167022,222800#msg-222800. Thanks for the help. – Matt Wales Mar 03 '16 at 23:11
  • @MattWales You're welcome and am glad to see you've found your solution Matt, *cheers* – Funk Forty Niner Mar 03 '16 at 23:38

1 Answers1

1

The problem isn't to do with the type of parameters - the PDO documentation on this topic is incorrect. The following posts describe the workaround. http://forums.mysql.com/read.php?98,167022,222800#msg-222800 and OUT or INOUT argument 1 for routine xxx is not a variable or NEW pseudo-variable in BEFORE trigger.

Community
  • 1
  • 1
Matt Wales
  • 101
  • 5