PHP pdo links to postgresql database. Json is used in where conditions to report errors.

  • has recently been using the json format of postgresql database and found that pdo query reported an error
  • the source code is as follows
<?php

    // PDO
    $pdo = new PDO("pgsql:host=127.0.0.1;port=5432;dbname=postgres","postgres","");
    $statement = $pdo->prepare("select * from test where account::jsonb ? "111"");
    $statement->execute();
    var_dump($statement->errorInfo());
    $rs = $statement->fetch();
    var_dump($rs);
    
    //  
    $p = pg_connect("host=127.0.0.1 port=5432 dbname=postgres user=yluchao password=""");
    $rs = pg_query($p, "select * from test where account::jsonb ? "111"");
    var_dump(pg_fetch_all($rs));



/*create table test
(
  id bigserial primary key,
  account jsonb not null default "{}",
  name varchar(255) not null default ""
);*/

if the figure uses pdo query to report errors, you can query

using native pgsql links.
Mar.16,2021

    PDO test 
    // PDO
    $pdo = new PDO("pgsql:host=127.0.0.1;port=5432;dbname=postgres","postgres","");
    $statement = $pdo->prepare("select * from test where account = ?");
    $statement->execute(array(111));
    var_dump($statement->errorInfo());
    $rs = $statement->fetch();
    var_dump($rs);

including ? in PDO::prepare in PHP means it is a placeholder. You won't have this problem by using PDO::query directly.

in fact, the official reply to this question has long been: https://bugs.php.net/bug.php?.

.

solution: turn off local simulation preprocessing

$pdo = new PDO(...);
$pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES ,false);

or

$pdo->prepare($sql,[\PDO::ATTR_EMULATE_PREPARES=>false]);

http://php.net/manual/en/pdo.

PHP enables local emulation by default. When ATTR_EMULATE_PREPARES is closed, the prepare operation is sent to the database server to perform the operation.

Menu