Calling MYSQL stored functions (not stored procedures) in Zend Framework

Question

Is there a way to call a MySQL stored function (not procedure) in Zend Framework? Or work around the framework to use unfiltered PHP code?

In a 'pure' php application, the following code works - using the mysqli db connection (not mysql). However in Zend framework, this code will not cause an application error but will also not bind the result to the php variable.

If you use a Zend_Db adapter, there is no way I can find to bind the result of the function to a variable (and thus get the answer - there is no Zend_Db method equivalent to 'bind_result').

I have looked at previous questions but they focus on stored procedures (e.g. How can I use a stored procedure in a MySql database with Zend Framework? ).

Thanks.

UPDATE: Since writing, I've discovered that this works fine with MYSQL on MAMP, but not with MYSQL on Zend Server CE on Mac.

MySQL stored function:

CREATE FUNCTION GetUserAccountType(
inUsername  VARCHAR(50)
) RETURNS INT(3)
BEGIN
    SELECT AccountType_i INTO @retVal FROM UserTable WHERE Username_vc = inUsername;
    RETURN @retVal;
END;
GO

Php code calling the MySQL function:

// open mysqli connection with db
$my_db = new mysqli($db_hostname, $db_username, $db_password, $db_database);

//  Prepare MySQL function ($userName is a string)

$stmt=$my_db->prepare("SELECT GetUserAccountType(?)") or die($my_db->error); 
$stmt->bind_param('s', $userName) or die($stmt->error);
$stmt->execute() or die($stmt->error); 
$stmt->bind_result($result);   
$stmt->fetch();

// (in php the $result variable  is now set as the result of the MySQL function, @retVal.
// In Zend framework, the $result variable is set to FALSE)
return $result;

You can use "pure" php within the framework if you want. I don't use any of the framework database functions and it just works. — Lobo, Jan 02 '12 at 09:47
I actually don't think that Zend has great support for UDF. I've had experiences where UDF called via Zend cause strange php errors. However, calling the same UDF via PHP's `mysqli` functions worked. — Chris Henry, Jan 03 '12 at 02:25

score 1 · Accepted Answer · answered Jan 03 '12 at 03:00

I just tested using your example stored function. I ran the following code and it works fine. I'm using PHP 5.3.6 on Mac OS X, connecting to a Percona Server 5.5.18 instance on CentOS 6 Linux.

<?php

set_include_path(get_include_path() . ":ZendFramework-1.11.10/library");

require_once 'Zend/Loader/Autoloader.php';
$autoloader = Zend_Loader_Autoloader::getInstance();

$db = Zend_Db::factory('pdo_mysql', ...); 

// test that I can read the table directly
print_r($db->query("SELECT * FROM UserTable")->fetchAll());

// test that I can fetch from a stored function
$stmt = $db->prepare("SELECT GetUserAccountType(?)");
$stmt->execute(array("default"));
print_r($stmt->fetchAll());

Note that the result set comes back using the full expression as the key.
So my print_r outputs the following:

Array
(
    [0] => Array
        (
            [GetUserAccountType('default')] => 42
        )

)

If you want to have a more conventional key, you should give the column an alias.
For example:

$stmt = $db->prepare("SELECT GetUserAccountType(?) AS AccountType_i");

Bill, thanks very much. So it seems that it is a server issue. Thanks for the advice on setting a key when calling the stored function. — Paul_O, Jan 03 '12 at 10:48

Calling MYSQL stored functions (not stored procedures) in Zend Framework

1 Answers1