Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 6CE3E635EE4 for ; Wed, 19 Aug 2009 17:58:46 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 62425-04-5 for ; Wed, 19 Aug 2009 20:58:35 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.zonarsystems.com (mail.zonarsystems.com [72.37.234.108]) by mail.postgresql.org (Postfix) with ESMTP id 5F585635F31 for ; Wed, 19 Aug 2009 17:58:16 -0300 (ADT) Received: from eric-mbp.zcorp.zonarsystems.com (10.0.1.22) by mail.zonarsystems.com (10.0.0.23) with Microsoft SMTP Server (TLS) id 8.1.393.1; Wed, 19 Aug 2009 13:59:42 -0700 CC: "pgsql-php@postgresql.org" Message-ID: <2AE0AB18-0F9D-4A32-92D3-4D86F176D432@zonarsystems.com> From: Eric Chamberlain To: Andy Shellam In-Reply-To: <4A8C6432.2050500@networkmail.eu> Content-Type: multipart/alternative; boundary="Apple-Mail-45--322138361" MIME-Version: 1.0 (Apple Message framework v936) Subject: Re: Stored procedures, PDO, and PHP issue Date: Wed, 19 Aug 2009 13:58:04 -0700 References: <4A8C6432.2050500@networkmail.eu> X-Mailer: Apple Mail (2.936) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200908/12 X-Sequence-Number: 4053 --Apple-Mail-45--322138361 Content-Type: text/plain; charset="US-ASCII"; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit > 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)"); > Unfortunately this does not work or I maybe doing it wrong. New code: $stmt = $db->prepare("SELECT is_password_expired($1::integer, $2::varchar); $stmt->bindValue(1, settype($userId, "integer"), PDO::PARAM_INT); $stmt->bindValue(2, $hashPass, PDO::PARAM_STR); $stmt->execute(); > When you say "this completely fails" - in what respect? What errors > do > you get? I get a blank screen. I've tried setting the error reporting level to: error_reporting(E_ALL); before calling the above code. Our servers are configured to display errors, etc. The fact that it just goes blank tells me there is a bigger issue going on. > 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. > Yes, I have tried settype hoping that would somehow make PDO use the right one. > 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 >> --Apple-Mail-45--322138361 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

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 =3D $db->prepare("SELECT = is_password_expired($1::integer,
$2::varchar)");


Unfortunately this does not work or I maybe doing it wrong.  New = code:
$stmt =3D $db->prepare("SELECT = is_password_expired($1::integer, = $2::varchar);
$stmt->bindValue(1, settype($userId, = "integer"), PDO::PARAM_INT);
$stmt->bindValue(2, $hashPass, = PDO::PARAM_STR);
$stmt->execute();

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

I get a blank screen. =  I've tried setting the error reporting level = to:

error_reporting(E_ALL);

<= div>before calling the above code.  Our servers are configured to = display errors, etc.  The fact that it just goes blank tells me = there is a bigger issue going on.

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.


Yes, I = have tried settype hoping that would somehow make PDO use the right = one.

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 =3D = $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 =3D = $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


= --Apple-Mail-45--322138361--