Error in INSERT statement with PostgreSql and PHP

Question

I'm using postgresql, with php and pdo. I'm getting a error and I dont know what I'm doing wrong.

My environment is:

PostgreSQL 9.3.6

Ubuntu 14.04.2 LTS

PHP 5.6.7

Apache/2.4.12 (Ubuntu)

The table which I'm using is this:

-- Table: acesso.usuario

-- DROP TABLE acesso.usuario;

CREATE TABLE acesso.usuario
(
  usuarioidentificador serial NOT NULL,
  usuariopessoafisicaidentificador integer,
  usuarionome character varying(30) NOT NULL,
  usuarionomecompleto character varying(90) NOT NULL,
  usuariodescricao text,
  usuariosenha character varying(32) NOT NULL,
  usuariosenhaalterar bit(1) NOT NULL,
  usuariosituacao bit(1) NOT NULL
)
WITH (
  OIDS=FALSE
);

If I execute this at pgadmin3

INSERT INTO acesso.usuario(
            usuariopessoafisicaidentificador, usuarionome, 
            usuarionomecompleto, usuariodescricao, usuariosenha, usuariosenhaalterar, 
            usuariosituacao)
    VALUES (null, '', '', 
            null, '', '1', '1'
            );

I got this Query returned successfully: one row affected, 82 ms execution time.

So I'm trying the same with PHP:

try {
            $query ="
            INSERT INTO acesso.usuario(
            usuariopessoafisicaidentificador, usuarionome, 
            usuarionomecompleto, usuariodescricao, usuariosenha, usuariosenhaalterar, 
            usuariosituacao)
    VALUES (null, '', '', 
            null, '', '1', '1'
            );
";

            $this->statement = $query;
            $conn = $this->getConnection();

            $query = $conn->prepare($this->statement, [\PDO::ATTR_CURSOR => \PDO::CURSOR_SCROLL]);

            $result = $query->execute();

        } catch (\Exception $e) {
            echo $e->getMessage() . "  <br> " . $e->getCode();

            var_dump($query);
            die();
            return false;
        }

I'm getting this output:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "INSERT" LINE 2: INSERT INTO acesso.usuario( ^ 42601object(PDOStatement)#14 (1) { ["queryString"]=> string(291) " INSERT INTO acesso.usuario( usuariopessoafisicaidentificador, usuarionome, usuarionomecompleto, usuariodescricao, usuariosenha, usuariosenhaalterar, usuariosituacao) VALUES (null, '', '', null, '', '1', '1' ); " }

I'm trying to find out what is happening.

Do you know it?

Thank you anyway.

Daniel Vérité · Accepted Answer · 2015-04-14 21:44:33Z

2

As the query is an INSERT (as opposed to a SELECT), it can't be mapped onto a cursor. The problem is in the attributes passed to prepare:

 [\PDO::ATTR_CURSOR => \PDO::CURSOR_SCROLL]

Internally, PDO generates a query starting like this for postgres:

DECLARE pdo_crsr_00000001 SCROLL CURSOR WITH HOLD FOR
  INSERT INTO acesso.usuario(...

and as documented in DECLARE, the query that follows must be a SELECT or VALUES command, which is why the parser yields a syntax error when it finds an INSERT there instead.

The solution is simply to remove the second argument:

$query = $conn->prepare($this->statement);

answered Apr 14, 2015 at 21:44

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error in INSERT statement with PostgreSql and PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related