Stored procedures, PDO, and PHP issue

Stored procedures, PDO, and PHP issue

am 19.08.2009 22:20:04 von Eric Chamberlain

--Apple-Mail-38--324418512
Content-Type: text/plain; charset="US-ASCII"; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit

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-38--324418512
Content-Type: text/html; charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

-webkit-line-break: after-white-space; ">

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();

T=
his 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!

apple-content-edited=3D"true"> style=3D"border-collapse: separate; color: rgb(0, 0, 0); font-family: =
Helvetica; font-size: medium; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; =
white-space: normal; widows: 2; word-spacing: 0px; =
-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: =
0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0px; ">
break-word; -webkit-nbsp-mode: space; -webkit-line-break: =
after-white-space; "> style=3D"border-collapse: separate; color: rgb(0, 0, 0); font-family: =
Helvetica; font-size: 12px; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; =
widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; =
-webkit-border-vertical-spacing: 0px; =
-webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0px; ">
break-word; -webkit-nbsp-mode: space; -webkit-line-break: =
after-white-space; =
">
Eric
=

=

--Apple-Mail-38--324418512--

Re: Stored procedures, PDO, and PHP issue

am 19.08.2009 22:42:20 von Nick Hajek

This is a multi-part message in MIME format.

------_=_NextPart_001_01CA210D.8CBDDC5B
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

>>> $stmt->bindParam(1, $userId, $hashPass, PDO::PARAM_INT);=20
=20
=20
You should be calling bindParam once for each parameter=20
$stmt->bindParam(1, $userId, PDO::PARAM_INT); =20
$stmt->bindParam(2, $hashPass, PDO::PARAM_STR); =20
=20
=20
Nick
=20

------_=_NextPart_001_01CA210D.8CBDDC5B
Content-Type: text/html;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable



charset=3Dus-ascii">

style=3D"WORD-WRAP: break-word; -webkit-nbsp-mode: space; =
-webkit-line-break: after-white-space">


class=3D408193620-19082009>

class=3D255104020-19082009>>>> $stmt->bindParam(1, =
$userId,=20
$hashPass, PDO::PARAM_INT); class=3D520373820-19082009> 
>

face=3DArial=20
color=3D#0000ff size=3D2> class=3D408193620-19082009>
 

face=3DArial=20
color=3D#0000ff size=3D2> class=3D408193620-19082009>
 

size=3D2>You=20
should be calling bindParam once for each parameter

color=3D#0000ff> face=3DArial>$stmt->bindParam(1, $userId, PDO::PARAM_INT); class=3D408193620-19082009>  class=3D408193620-19082009>     =

face=3DArial=20
color=3D#0000ff size=3D2> class=3D408193620-19082009>$stmt->bindParam(2, $hashPass,=20
PDO::PARAM_STR); color=3D#0000ff=20
size=3D2> 
class=3D408193620-19082009>    =20

face=3DArial=20
color=3D#0000ff size=3D2> class=3D408193620-19082009> class=3D408193620-19082009>
  >
face=3DArial=20
color=3D#0000ff size=3D2> class=3D408193620-19082009> class=3D408193620-19082009>
  >
class=3D408193620-19082009> class=3D408193620-19082009> face=3DArial=20
color=3D#0000ff size=3D2>Nick

face=3DArial=20
color=3D#0000ff size=3D2> class=3D408193620-19082009>
 
>


------_=_NextPart_001_01CA210D.8CBDDC5B--

Re: Stored procedures, PDO, and PHP issue

am 19.08.2009 22:44:34 von Andy Shellam

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
>

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: Stored procedures, PDO, and PHP issue

am 19.08.2009 22:47:06 von Eric Chamberlain

--Apple-Mail-44--322796677
Content-Type: text/plain; charset="US-ASCII"; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit

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
>


--Apple-Mail-44--322796677
Content-Type: text/html; charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

-webkit-line-break: after-white-space; ">Wow, I apologize.  The =
code is exactly like what yours is.  In my rush I missed the =
correct code.


class=3D"Apple-style-span" style=3D"border-collapse: separate; color: =
rgb(0, 0, 0); font-family: Helvetica; font-size: medium; font-style: =
normal; font-variant: normal; font-weight: normal; letter-spacing: =
normal; line-height: normal; orphans: 2; text-align: auto; text-indent: =
0px; text-transform: none; white-space: normal; widows: 2; word-spacing: =
0px; -webkit-border-horizontal-spacing: 0px; =
-webkit-border-vertical-spacing: 0px; =
-webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0px; ">
break-word; -webkit-nbsp-mode: space; -webkit-line-break: =
after-white-space; "> style=3D"border-collapse: separate; color: rgb(0, 0, 0); font-family: =
Helvetica; font-size: 12px; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; =
widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; =
-webkit-border-vertical-spacing: 0px; =
-webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0px; ">
break-word; -webkit-nbsp-mode: space; -webkit-line-break: =
after-white-space; =
">
Eric

class=3D"Apple-interchange-newline">
style=3D"WORD-WRAP: break-word; -webkit-nbsp-mode: space; =
-webkit-line-break: after-white-space">
=
class=3D"408193620-19082009"> =
color=3D"#0000ff"> class=3D"255104020-19082009">>>> $stmt->bindParam(1, =
$userId, $hashPass, PDO::PARAM_INT); class=3D"520373820-19082009"> 
n>
class=3D"408193620-19082009"> size=3D"2"> class=3D"408193620-19082009"> 
=
face=3D"Arial" color=3D"#0000ff" size=3D"2"> class=3D"408193620-19082009"> class=3D"408193620-19082009"> 
=
color=3D"#0000ff" size=3D"2">You should be calling bindParam once for =
each parameter
class=3D"408193620-19082009"> color=3D"#0000ff">$stmt->bindParam(1, =
$userId, PDO::PARAM_INT); class=3D"408193620-19082009">  class=3D"408193620-19082009">     =
class=3D"408193620-19082009"> size=3D"2"> class=3D"408193620-19082009">$stmt->bindParam(2, $hashPass, =
PDO::PARAM_STR); color=3D"#0000ff" size=3D"2">  class=3D"408193620-19082009">     =
align=3D"left"> color=3D"#0000ff" size=3D"2"> class=3D"408193620-19082009"> class=3D"408193620-19082009">  v>
class=3D"408193620-19082009"> size=3D"2"> class=3D"408193620-19082009"> class=3D"408193620-19082009">  v>
class=3D"408193620-19082009"> class=3D"408193620-19082009"> class=3D"255104020-19082009"> size=3D"2">Nick
dir=3D"ltr" align=3D"left"> face=3D"Arial" color=3D"#0000ff" size=3D"2"> class=3D"408193620-19082009"> class=3D"408193620-19082009"> 
n>

=

--Apple-Mail-44--322796677--

Re: Stored procedures, PDO, and PHP issue

am 19.08.2009 22:58:04 von Eric Chamberlain

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

-webkit-line-break: after-white-space; ">

apple-content-edited=3D"true"> style=3D"border-collapse: separate; color: rgb(0, 0, 0); font-family: =
Helvetica; font-size: medium; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; =
white-space: normal; widows: 2; word-spacing: 0px; =
-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: =
0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0px; ">
break-word; -webkit-nbsp-mode: space; -webkit-line-break: =
after-white-space; "> style=3D"border-collapse: separate; color: rgb(0, 0, 0); font-family: =
Helvetica; font-size: 12px; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; =
widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; =
-webkit-border-vertical-spacing: 0px; =
-webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0px; ">
break-word; -webkit-nbsp-mode: space; -webkit-line-break: =
after-white-space; ">
style=3D"font-size: medium;"> size=3D"3"> 12px;">
v>
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)");
class=3D"Apple-style-span" color=3D"#000000"> class=3D"Apple-style-span" =
color=3D"#144FAE">

>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();

type=3D"cite">
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.

type=3D"cite">
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.

type=3D"cite">
Regards,
Andy


Eric Chamberlain =
wrote:
I'm having an issue calling a =
specific stored proc using PHP and PDO.
type=3D"cite"> 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
type=3D"cite">get it to call the integer, varchar =
proc.
type=3D"cite">
Stored =
procedure definitions:
boolean =
is_password_expired(i_user varchar, i_pass =
varchar)
boolean =
is_password_expired(i_user_id integer, i_pass =
varchar)
type=3D"cite">
$stmt =3D =
$db->prepare("SELECT is_password_expired(?, =
?)");
$stmt->bindValue(1, =
$userId, $hashPass, PDO::PARAM_INT);
type=3D"cite">$stmt->execute();
type=3D"cite">
This always =
returns false because it's passing the $userId, of say =

"1", to the varchar, varchar =
proc.  I've tried using the following:
type=3D"cite">
$stmt =3D =
$db->prepare("SELECT is_password_expired(?::integer, =

type=3D"cite">?::varchar)");
type=3D"cite">
This completely =
fails.
type=3D"cite">
type=3D"cite">$stmt->bindParam(1, $userId, $hashPass, =
PDO::PARAM_INT);
type=3D"cite">
Same as =
bindValue result.
type=3D"cite">
type=3D"cite">$stmt->execute(array($userId, =
$hashPass));
type=3D"cite">
Same =
result.
type=3D"cite">
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!
type=3D"cite">
type=3D"cite">Eric
type=3D"cite">

=

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

Re: Stored procedures, PDO, and PHP issue

am 19.08.2009 23:11:27 von Bill Moran

In response to Eric Chamberlain :

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

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: Stored procedures, PDO, and PHP issue

am 19.08.2009 23:15:41 von Andy Shellam

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

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: Stored procedures, PDO, and PHP issue

am 20.08.2009 00:25:25 von Eric Chamberlain

--Apple-Mail-56--316897451
Content-Type: text/plain; charset="US-ASCII"; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit

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.
--Apple-Mail-56--316897451
Content-Type: text/html; charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

-webkit-line-break: after-white-space; ">

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:

class=3D"Apple-style-span" style=3D"font-family: 'Lucida Grande'; color: =
rgb(20, 20, 20); line-height: 14px; "> ">These are the conversions PDO does during binding. 
style=3D"word-wrap: break-word; "> "> 
break-word; ">    * PDO::PARAM_STR converts whatever you give =
it to a string 

style=3D"word-wrap: break-word; ">    * PDO::PARAM_INT =
converts bools into longs 

">    * PDO::PARAM_BOOL =
converts longs into bools 

"> 
break-word; ">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.

class=3D"Apple-interchange-newline">
Hi =
Eric,


type=3D"cite">Unfortunately this does not work or I maybe doing it =
wrong.  New code:
$stmt =3D=
$db->prepare("SELECT is_password_expired($1::integer, =

type=3D"cite">$2::varchar);
type=3D"cite">$stmt->bindValue(1, settype($userId, "integer"), =
PDO::PARAM_INT);
type=3D"cite">$stmt->bindValue(2, $hashPass, =
PDO::PARAM_STR);
type=3D"cite">$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 =3D "2"; // =
string
settype($userId, 'integer'); // $userId is now an =
integer
$stmt->bindValue(1, $userId, =
PDO::PARAM_INT);

type=3D"cite">
I get a blank =
screen.  I've tried setting the error reporting level =
to:

type=3D"cite">error_reporting(E_ALL);
type=3D"cite">
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
type=3D"cite">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.


type=3D"cite">
Lastly have you tried "named" parameters?  =46rom =
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 =3D $dbh->prepare("INSERT =
INTO REGISTRY (name, value) VALUES (:name, =
:value)");
$stmt->bindParam(':name', =
$name);
$stmt->bindParam(':value', =
$value);|

Tried this.  No =
go.
=

--Apple-Mail-56--316897451--

Re: Stored procedures, PDO, and PHP issue

am 20.08.2009 00:36:39 von Andy Shellam

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

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php

Re: Stored procedures, PDO, and PHP issue

am 09.09.2009 07:24:32 von webmaster

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

--
Sent via pgsql-php mailing list (pgsql-php@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-php