pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
To: Andy Shellam <andy-lists@networkmail.eu>
Cc: pgsql-php@postgresql.org <pgsql-php@postgresql.org>
Subject: Re: Stored procedures, PDO, and PHP issue
Date: Wed, 19 Aug 2009 13:58:04 -0700
Message-ID: <2AE0AB18-0F9D-4A32-92D3-4D86F176D432@zonarsystems.com> (raw)
In-Reply-To: <4A8C6432.2050500@networkmail.eu>
References: <A11E730E-82D7-4EE6-AD80-ECD95982D78A@zonarsystems.com>
	<4A8C6432.2050500@networkmail.eu>


> 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
>>

view thread (10+ messages)  latest in thread

Message-ID: <2AE0AB18-0F9D-4A32-92D3-4D86F176D432@zonarsystems.com>
Permalink:  ../2AE0AB18-0F9D-4A32-92D3-4D86F176D432@zonarsystems.com/
Also on:    postgresql.org/message-id/2AE0AB18-0F9D-4A32-92D3-4D86F176D432@zonarsystems.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: Eric.Chamberlain@zonarsystems.com, andy-lists@networkmail.eu
  Subject: Re: Stored procedures, PDO, and PHP issue
  In-Reply-To: <2AE0AB18-0F9D-4A32-92D3-4D86F176D432@zonarsystems.com>
  References: <A11E730E-82D7-4EE6-AD80-ECD95982D78A@zonarsystems.com> <4A8C6432.2050500@networkmail.eu> <2AE0AB18-0F9D-4A32-92D3-4D86F176D432@zonarsystems.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox