Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 36CD16337B6 for ; Wed, 19 Aug 2009 19:25:51 -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 92619-05 for ; Wed, 19 Aug 2009 22:25:41 +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 CAF346340FD for ; Wed, 19 Aug 2009 19:25:40 -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 15:27:03 -0700 CC: "pgsql-php@postgresql.org" Message-ID: From: Eric Chamberlain To: Andy Shellam In-Reply-To: <4A8C6B7D.7020806@networkmail.eu> Content-Type: multipart/alternative; boundary="Apple-Mail-56--316897451" MIME-Version: 1.0 (Apple Message framework v936) Subject: Re: Stored procedures, PDO, and PHP issue Date: Wed, 19 Aug 2009 15:25:25 -0700 References: <4A8C6432.2050500@networkmail.eu> <2AE0AB18-0F9D-4A32-92D3-4D86F176D432@zonarsystems.com> <4A8C6B7D.7020806@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/15 X-Sequence-Number: 4056 --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
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:

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

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


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