pg.ddx.io pgsql-php@postgresql.org mailing list archive
help / color / mirror / Atom feedStored procedures, PDO, and PHP issue
10+ messages / 5 participants
[nested] [flat]
* Stored procedures, PDO, and PHP issue
@ 2009-08-19 20:20 Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2009-08-19 20:42 ` Re: Stored procedures, PDO, and PHP issue Hajek, Nick <Nick.Hajek@Vishay.com>
2009-08-19 20:44 ` Re: Stored procedures, PDO, and PHP issue Andy Shellam <andy-lists@networkmail.eu>
2009-08-19 21:11 ` Re: Stored procedures, PDO, and PHP issue Bill Moran <wmoran@potentialtech.com>
0 siblings, 3 replies; 10+ messages in thread
From: Eric Chamberlain @ 2009-08-19 20:20 UTC (permalink / raw)
To: pgsql-php
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stored procedures, PDO, and PHP issue
2009-08-19 20:20 Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
@ 2009-08-19 20:42 ` Hajek, Nick <Nick.Hajek@Vishay.com>
2009-08-19 20:47 ` Re: Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2 siblings, 1 reply; 10+ messages in thread
From: Hajek, Nick @ 2009-08-19 20:42 UTC (permalink / raw)
To: Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>; pgsql-php
>>> $stmt->bindParam(1, $userId, $hashPass, PDO::PARAM_INT);
You should be calling bindParam once for each parameter
$stmt->bindParam(1, $userId, PDO::PARAM_INT);
$stmt->bindParam(2, $hashPass, PDO::PARAM_STR);
Nick
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stored procedures, PDO, and PHP issue
2009-08-19 20:20 Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2009-08-19 20:42 ` Re: Stored procedures, PDO, and PHP issue Hajek, Nick <Nick.Hajek@Vishay.com>
@ 2009-08-19 20:47 ` Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
0 siblings, 0 replies; 10+ messages in thread
From: Eric Chamberlain @ 2009-08-19 20:47 UTC (permalink / raw)
To: Hajek, Nick <Nick.Hajek@Vishay.com>; +Cc: pgsql-php
Wow, I apologize. The code is exactly like what yours is. In my rush
I missed the correct code.
Eric
> >>> $stmt->bindParam(1, $userId, $hashPass, PDO::PARAM_INT);
>
>
> You should be calling bindParam once for each parameter
> $stmt->bindParam(1, $userId, PDO::PARAM_INT);
> $stmt->bindParam(2, $hashPass, PDO::PARAM_STR);
>
>
> Nick
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stored procedures, PDO, and PHP issue
2009-08-19 20:20 Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
@ 2009-08-19 20:44 ` Andy Shellam <andy-lists@networkmail.eu>
2009-08-19 20:58 ` Re: Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2 siblings, 1 reply; 10+ messages in thread
From: Andy Shellam @ 2009-08-19 20:44 UTC (permalink / raw)
To: Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>; +Cc: pgsql-php
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stored procedures, PDO, and PHP issue
2009-08-19 20:20 Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2009-08-19 20:44 ` Re: Stored procedures, PDO, and PHP issue Andy Shellam <andy-lists@networkmail.eu>
@ 2009-08-19 20:58 ` Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2009-08-19 21:15 ` Re: Stored procedures, PDO, and PHP issue Andy Shellam <andy-lists@networkmail.eu>
0 siblings, 1 reply; 10+ messages in thread
From: Eric Chamberlain @ 2009-08-19 20:58 UTC (permalink / raw)
To: Andy Shellam <andy-lists@networkmail.eu>; +Cc: pgsql-php
> 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
>>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stored procedures, PDO, and PHP issue
2009-08-19 20:20 Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2009-08-19 20:44 ` Re: Stored procedures, PDO, and PHP issue Andy Shellam <andy-lists@networkmail.eu>
2009-08-19 20:58 ` Re: Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
@ 2009-08-19 21:15 ` Andy Shellam <andy-lists@networkmail.eu>
2009-08-19 22:25 ` Re: Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
0 siblings, 1 reply; 10+ messages in thread
From: Andy Shellam @ 2009-08-19 21:15 UTC (permalink / raw)
To: Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>; +Cc: pgsql-php
Hi Eric,
>
> 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();
settype() doesn't return anything so it needs to be used on it's own
line. E.g.:
$userId = "2"; // string
settype($userId, 'integer'); // $userId is now an integer
$stmt->bindValue(1, $userId, PDO::PARAM_INT);
>
> 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.
Does your server definitely have display_errors set to On as well as the
error_reporting line, and it's not been overridden by your application?
I've only ever known really serious errors (i.e. core dumps) to not
display anything even when display_errors is set to on. What's logged
in your Apache or IIS error log?
Lastly have you tried "named" parameters? From the PHP manual:
(actually the PostgreSQL syntax "$1::integer" I suggested may have
caused PHP to crash as PDO uses a colon to introduce a named parameter.)
|$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);|
Andy
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stored procedures, PDO, and PHP issue
2009-08-19 20:20 Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2009-08-19 20:44 ` Re: Stored procedures, PDO, and PHP issue Andy Shellam <andy-lists@networkmail.eu>
2009-08-19 20:58 ` Re: Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2009-08-19 21:15 ` Re: Stored procedures, PDO, and PHP issue Andy Shellam <andy-lists@networkmail.eu>
@ 2009-08-19 22:25 ` Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2009-08-19 22:36 ` Re: Stored procedures, PDO, and PHP issue Andy Shellam <andy-lists@networkmail.eu>
0 siblings, 1 reply; 10+ messages in thread
From: Eric Chamberlain @ 2009-08-19 22:25 UTC (permalink / raw)
To: Andy Shellam <andy-lists@networkmail.eu>; +Cc: pgsql-php
On a hunch I have solved the problem. You can not use the question
mark syntax and the cast. Instead you have to use the named
parameters with the cast.
$stmt->prepare("SELECT
is_password_expired(:user_id::INTEGER, :pass::VARCHAR);
$stmt->bindValue(':user_id', $userId);
$stmt->bindValue(':pass', $hashPass);
$stmt->execute();
Thank you all for your help! You guys are awesome!
For completeness I'd like to answer your questions:
My cohort grabbed this information somewhere from the PHP site:
These are the conversions PDO does during binding.
* PDO::PARAM_STR converts whatever you give it to a string
* PDO::PARAM_INT converts bools into longs
* PDO::PARAM_BOOL converts longs into bools
That's it. Nothing else is converted. PDO uses the PARAM flags to
format SQL not to cast data types.
This explains why it didn't correctly identify the correct proc to use.
> Hi Eric,
>
>>
>> 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();
>
> settype() doesn't return anything so it needs to be used on it's own
> line. E.g.:
That's very good to know! It still didn't work, but at least I
learned something new :)
> $userId = "2"; // string
> settype($userId, 'integer'); // $userId is now an integer
> $stmt->bindValue(1, $userId, PDO::PARAM_INT);
>
>>
>> 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.
>
> Does your server definitely have display_errors set to On as well as
> the
> error_reporting line, and it's not been overridden by your
> application?
> I've only ever known really serious errors (i.e. core dumps) to not
> display anything even when display_errors is set to on. What's logged
> in your Apache or IIS error log?
It does have errors displayed.
Here is what is in the Apache log:
[Wed Aug 19 15:07:02 2009] [notice] child pid 31897 exit signal
Segmentation fault (11)
[Wed Aug 19 15:07:03 2009] [notice] child pid 4163 exit signal
Segmentation fault (11)
[Wed Aug 19 15:07:05 2009] [notice] child pid 4122 exit signal
Segmentation fault (11)
[Wed Aug 19 15:07:07 2009] [notice] child pid 4209 exit signal
Segmentation fault (11)
This was after 5 successive hits using the ?::BIGINT, ?::VARCHAR syntax.
> Lastly have you tried "named" parameters? From the PHP manual:
> (actually the PostgreSQL syntax "$1::integer" I suggested may have
> caused PHP to crash as PDO uses a colon to introduce a named
> parameter.)
>
> |$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES
> (:name, :value)");
> $stmt->bindParam(':name', $name);
> $stmt->bindParam(':value', $value);|
Tried this. No go.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stored procedures, PDO, and PHP issue
2009-08-19 20:20 Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2009-08-19 20:44 ` Re: Stored procedures, PDO, and PHP issue Andy Shellam <andy-lists@networkmail.eu>
2009-08-19 20:58 ` Re: Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2009-08-19 21:15 ` Re: Stored procedures, PDO, and PHP issue Andy Shellam <andy-lists@networkmail.eu>
2009-08-19 22:25 ` Re: Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
@ 2009-08-19 22:36 ` Andy Shellam <andy-lists@networkmail.eu>
0 siblings, 0 replies; 10+ messages in thread
From: Andy Shellam @ 2009-08-19 22:36 UTC (permalink / raw)
To: Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>; +Cc: pgsql-php
Hi Eric,
Ah cool well I guess we all learnt something now then huh!? Cheers for
letting us know the solution. Strange that PDO won't let you use the
"?" syntax as well as named data types in the query.
>
> Here is what is in the Apache log:
>
> [Wed Aug 19 15:07:02 2009] [notice] child pid 31897 exit signal
> Segmentation fault (11)
> [Wed Aug 19 15:07:03 2009] [notice] child pid 4163 exit signal
> Segmentation fault (11)
> [Wed Aug 19 15:07:05 2009] [notice] child pid 4122 exit signal
> Segmentation fault (11)
> [Wed Aug 19 15:07:07 2009] [notice] child pid 4209 exit signal
> Segmentation fault (11)
Yeah that's PHP crashing (explaining why you only get the blank
screen.) If you have the time, maybe report it to the PHP guys
(http://bugs.php.net.) I can't see why you cannot use "?::BIGINT" but
you can use ":name::BIGINT." As far as we as developers are concerned,
the two should be interchangeable (and the documentation certainly
suggests that.)
At any rate even if you couldn't use that syntax, PHP shouldn't segfault.
>
> This was after 5 successive hits using the ?::BIGINT, ?::VARCHAR syntax.
>
Regards,
Andy
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stored procedures, PDO, and PHP issue
2009-08-19 20:20 Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
@ 2009-08-19 21:11 ` Bill Moran <wmoran@potentialtech.com>
2 siblings, 0 replies; 10+ messages in thread
From: Bill Moran @ 2009-08-19 21:11 UTC (permalink / raw)
To: Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>; +Cc: pgsql-php
In response to Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>:
> 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!
I would expect what you're doing to work. It works with the functional
interface.
What do the query logs on the server say? Try turning full query logging
on and see what's actually getting run.
I see in another reply that you're getting a blank white screen. This
tends to suggest that your debugging is configured wrong, or that PHP is
encountering a fatal error and coredumping or something similar. Check
whatever logs are appropriate for evidence of this. Check the HTTP error
log, as well as system error logs.
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Stored procedures, PDO, and PHP issue
@ 2009-09-09 05:24 Ronald D. Willis <webmaster@rdwonline.com>
0 siblings, 0 replies; 10+ messages in thread
From: Ronald D. Willis @ 2009-09-09 05:24 UTC (permalink / raw)
To: pgsql-php
Add this as a PDO option
array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
I bet there's a PDOException being thrown and I would NOT be at all
surprised if it is a HY000 error where PDO is complaining about
unbuffered queries....
On 2009-08-19 20:47, Eric Chamberlain wrote:
> Wow, I apologize. The code is exactly like what yours is. In my rush
> I missed the correct code.
>
> Eric
>
> > >>> $stmt->bindParam(1, $userId, $hashPass, PDO::PARAM_INT);
> >
> >
> > You should be calling bindParam once for each parameter
> > $stmt->bindParam(1, $userId, PDO::PARAM_INT);
> > $stmt->bindParam(2, $hashPass, PDO::PARAM_STR);
> >
> >
> > Nick
> >
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2009-09-09 05:24 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-08-19 20:20 Stored procedures, PDO, and PHP issue Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2009-08-19 20:42 ` Hajek, Nick <Nick.Hajek@Vishay.com>
2009-08-19 20:47 ` Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2009-08-19 20:44 ` Andy Shellam <andy-lists@networkmail.eu>
2009-08-19 20:58 ` Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2009-08-19 21:15 ` Andy Shellam <andy-lists@networkmail.eu>
2009-08-19 22:25 ` Eric Chamberlain <Eric.Chamberlain@zonarsystems.com>
2009-08-19 22:36 ` Andy Shellam <andy-lists@networkmail.eu>
2009-08-19 21:11 ` Bill Moran <wmoran@potentialtech.com>
2009-09-09 05:24 Re: Stored procedures, PDO, and PHP issue Ronald D. Willis <webmaster@rdwonline.com>
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