The scenario
Two systems(not server) running PHP and PostgreSQL with the following versions
PHP
PHP 5.3.13 (cli) (built: May 9 2012 14:38:35)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies
pdo_pgsql
PostgreSQL(libpq) Version 9.0.7
Module version 1.0.2
PostgreSQL
PostgreSQL 9.1.4
With CITEXT extension enabled.
PHP
PHP 5.4.6 (cli) (built: Aug 16 2012 12:50:09)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2012 Zend Technologies
pdo_pgsql
PostgreSQL(libpq) Version 9.1.4
Module version 1.0.2
PostgreSQL
PostgreSQL 9.1.4
With CITEXT extension enabled.
when a simple query such as
select column1 from schema1.table1 where column1= ?
Where column1 is of the type CITEXT, is executed through PHP PDO
I am guessing the newer version of PHP PDO libs are doing something similar to this:
select column1 from schema1.table1 where column1= 'value'::text;
during binding.
LOG: execute pdo_stmt_00000001: select column1 from schema1.table1 where column1 = $1
DETAIL: parameters: $1 = 'value'
LOG: statement: DEALLOCATE pdo_stmt_00000001
where the actual value of the column column1
is VALUE
.
Still comes back with 0 elements.
When the statement
select column1 from schema1.table1 where column1 = 'value';
is executed directly on the PSQL
prompt comes back with a single row.
column1
---------
VALUE
(1 row)
So, the type casting does not take place! I still am not able to understand the behaviour of PDO
/postgresql
.
Here is the code that was used to test :
try {
$db = new PDO('pgsql:dbname=database1;user=user;password=pass;host=localhost');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT column1 from schema1.column1 where column1 = 'value'::citext ";
$retval=$db->query($sql);
foreach ($retval as $row) {
print $row['uname'] . '<br>';
}
}catch (PDOException $PDOerr) {
echo 'An error occured : <br>';
var_dump($PDOerr);
exit;
//some thing went wrong while performing the action on db.
}
I get the error :
object(PDOException)#10 (8) { ["message":protected]=> string(211) "SQLSTATE[42704]: \
Undefined object: 7 ERROR: type "citext" does not exist LINE 1: ...
I do not understand why citext
is not getting detected!
When the statement is executed directly on the PSQL
prompt, everything works fine as mentioned above.
It was a search path problem for the user I was trying to login as. I am guessing I had created the user when I had done a session-wise set search_path
to some other schema and instead of the default '"$user",public' it was set to the other schema. The user did not have any access to public schema at all. Thanks to Daniel Vérité
for pointing me in the right direction.
BTW, used ALTER ROLE user SET search_path TO "$user",public;
to set the search path. Although, $user is useless as I do not have any schema named after the current users.
Try something like the following as a workaround.
c = (?)::citext
I'm guessing you'll be out of luck because it looks like the type constants are shared for all dbs
http://www.php.net/manual/en/pdo.constants.php
Congratulations to the PDO team for a design choice that ranks with making your filesystem library only support upper-case 8.3 to maintain compatibility was ms-dos 6.22
The explicit DEALLOCATE suggests there was a PREPARE issued earlier. That will take a list of typed parameters so presumably the text type is being set there.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With