Re: [GENERAL] Type cast in PHP PDO (does not work like in Java?)

[Raymond O'Donnell]

On 05/08/17 16:58, Alexander Farber wrote:

Good evening,

with PostgreSQL 9.6.3 and JDBC 42.1.1.jre7 types can be casted when 
calling a stored function:


 final String sql = "SELECT words_buy_vip(?::text, ?::int, 
?::text, ?::text, ?::float, ?::inet)";


 try (Connection db = DriverManager.getConnection(DATABASE_URL, 
DATABASE_USER, DATABASE_PASS);

 PreparedStatement st = db.prepareStatement(sql)) {
  st.setString(2, sid);
  st.setInt(1, SOCIAL_FACEBOOK);
  // etc.
  st.executeQuery();
 }

But with PHP 5.4.16 on CentOS 7 Linux the types can not be casted (and 
strangely the statement is just not being executed without any error 
being reported) and the "::text", "::int" and "::inet" should be removed 
from the placeholders as in:


I think PDO uses a colon in named parameters, so maybe that's causing 
problems. You could try casting like this:


  select words_buy_vip(cast(? as text), cast(? as int), .);

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie


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

[GENERAL] Type cast in PHP PDO (does not work like in Java?)

[Alexander Farber]

Good evening,

with PostgreSQL 9.6.3 and JDBC 42.1.1.jre7 types can be casted when calling
a stored function:

final String sql = "SELECT words_buy_vip(?::text, ?::int, ?::text,
?::text, ?::float, ?::inet)";

try (Connection db = DriverManager.getConnection(DATABASE_URL,
DATABASE_USER, DATABASE_PASS);
PreparedStatement st = db.prepareStatement(sql)) {
 st.setString(2, sid);
 st.setInt(1, SOCIAL_FACEBOOK);
 // etc.
 st.executeQuery();
}

But with PHP 5.4.16 on CentOS 7 Linux the types can not be casted (and
strangely the statement is just not being executed without any error being
reported) and the "::text", "::int" and "::inet" should be removed from the
placeholders as in:

$sql = 'SELECT words_buy_vip(?, ?, ?, ?, ?, ?)';   // can not use
type casts here?

try {
   $options = array(PDO::ATTR_ERRMODE =>
PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_CASE =>
PDO::CASE_LOWER);
   $dbh = new PDO(sprintf('pgsql:host=%s;port=%u;dbname=%s',
DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options);
   $sth = $dbh->prepare($sql);
   $params = array($sid, SOCIAL_FACEBOOK, $payment_id,
$product, $price, $_SERVER['REMOTE_ADDR']);
$sth->execute($params);
} catch (PDOException $ex) {
error_log("PDO exception $ex");
}

Is that so or do I overlook something?

And when reading the PHP docs
http://php.net/manual/en/pdostatement.bindvalue.php
and http://php.net/manual/en/pdo.constants.php
then there is no constant to use for the "::inet"

Thank you for any comments
Alex