Обсуждение: [GENERAL] Type cast in PHP PDO (does not work like in Java?)

Поиск
Список
Период
Сортировка

[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

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
rod@iol.ie