ID: 33876
Comment by: php dot net at sharpdreams dot com
Reported By: php at sagi dot org
Status: Assigned
Bug Type: PDO related
Operating System: Linux
PHP Version: 5CVS-2005-07-27 (dev)
Assigned To: wez
New Comment:
I "fixed" this by binding the parameters as integers:
$bool = false;
$st = $db->prepare( "insert into foo ( bar ) values ( :bar )" );
$st->bindParam( ":bar", $boo, PDO_PARAM_INT );
$st->execute();
Add the following to your pgsql:
-- begin
CREATE OR REPLACE FUNCTION int_to_bool(int4)
RETURNS bool AS
$BODY$
select case
when $1 <> 0 then TRUE
else FALSE
end;
$BODY$
LANGUAGE 'sql' VOLATILE;
CREATE CAST (int4 AS bool)
WITH FUNCTION int_to_bool(int4)
AS ASSIGNMENT;
-- end
PDO converts true/false to 1/0 which pass into the cast function.
Couldn't get an implicit ''::bool cast working.
e.g.,
CREATE OR REPLACE FUNCTION text_to_bool(text)
RETURNS bool AS
$BODY$
select case
when $1 <> '' then TRUE
else FALSE
end;
$BODY$
LANGUAGE 'sql' VOLATILE;
CREATE CAST (text AS bool)
WITH FUNCTION text_to_bool(text)
AS ASSIGNMENT;
And then doing "select ''::bool;" didn't work. Doin "select
''::text::bool" did work, though, so you could factor that into your
PDO statements, too:
insert into foo(bar) values(:bar::text);
which will call the above cast ('' -> text -> bool).
Previous Comments:
------------------------------------------------------------------------
[2005-07-27 17:15:10] php at sagi dot org
For what it's worth, its seems like the pgsql _client_ library that my
installation is compiled against is v7.4.7, even though the server is
running v8.0.
So I guess it never used native prepared statements and the workaround
that you suggested had no affect - they're already disabled.
------------------------------------------------------------------------
[2005-07-27 16:40:54] php at sagi dot org
Nope, still get the same exception and the same query is being executed
according to the server log.
Still using the same php5-200507261230 snapshot.
The exact code:
$res = $db->prepare(
'SELECT id,name,trial FROM shops WHERE trial = ?',
array(PDO_PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT =>
true)
);
$res->execute(array(false));
And the result:
Fatal error: Uncaught exception 'PDOException' with message
'SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input
syntax for type boolean: ""' in /home/shopy/dev/tmp/test.php:12
Stack trace:
#0 /home/shopy/dev/tmp/test.php(12): PDOStatement->execute(Array)
#1 {main}
thrown in /home/shopy/dev/tmp/test.php on line 12
------------------------------------------------------------------------
[2005-07-27 16:25:22] [EMAIL PROTECTED]
Try this as a workaround for now:
$res = $db->prepare('SELECT ...', array(
PDO_PGSQL_ATTR_DISABLE_NATIVE_PREPARED_STATEMENT => true
));
You can blame the pretty poor native prepared statement API in pgsql
for this one; it just doesn't tell PDO anything about the parameter
types so it can't make an informed decision about how to treat the
parameters.
------------------------------------------------------------------------
[2005-07-27 15:56:26] php at sagi dot org
I know how string casting works, but this is not a string.
PDO knows, for example, how to convert PHP NULL to SQL NULL and not
string('') (like string casting does). Why can't it cast bool values to
an integer instead?
This behavior is bad. PDO knows how to cast the value to real php bool
when selecting, but cannot cast it back when inserting/updating, which
means a simple attempt to re-insert a row that has just been selected,
using the same object, fails.
------------------------------------------------------------------------
[2005-07-27 15:16:02] [EMAIL PROTECTED]
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.php.net/manual/ and the instructions on how to report
a bug at http://bugs.php.net/how-to-report.php
This is expected behaviour, when cast to a string bool false is
converted to "" while bool true converted to "1".
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/33876
--
Edit this bug report at http://bugs.php.net/?id=33876&edit=1
