Re: Stored procedures, PDO, and PHP issue

Hi Eric,

What about setting your SQL text to use $1 and $2 as the parameter
values?  I'm currently doing this in the C API (as I type this!) so not
sure if this will work in PHP PDO.

$stmt = $db->prepare("SELECT is_password_expired($1::integer,
$2::varchar)");

When you say "this completely fails" - in what respect?  What errors do
you get?

Also have you tried making sure your PHP $userId is an integer
not a string - e.g. settype($userId, 'integer');  Again I'm not sure if
this has a bearing on the PDO stuff as I've never used it.

Regards,
Andy


Eric Chamberlain wrote:

I'm having an issue calling a specific stored proc using PHP and PDO.

I have two procs with the same name and same number of parameters.

However, the parameter types are different.  When the below code is

called in PHP it always calls the varchar, varchar proc.  I can not

get it to call the integer, varchar proc.

Stored procedure definitions:

boolean is_password_expired(i_user varchar, i_pass varchar)

boolean is_password_expired(i_user_id integer, i_pass varchar)

$stmt = $db->prepare("SELECT is_password_expired(?, ?)");

$stmt->bindValue(1, $userId, $hashPass, PDO::PARAM_INT);

$stmt->execute();

This always returns false because it's passing the $userId, of say

"1", to the varchar, varchar proc.  I've tried using the following:

$stmt = $db->prepare("SELECT is_password_expired(?::integer,

?::varchar)");

This completely fails.

$stmt->bindParam(1, $userId, $hashPass, PDO::PARAM_INT);

Same as bindValue result.

$stmt->execute(array($userId, $hashPass));

Same result.

Is there some way to inform PDO and Postgresql that I plan on using

the integer, varchar proc?  If so, how do I do this?  Thanks all!

Eric