CODEWITHSUNDEEP
phppostgresqlpdo
Flavio Vinho
Flavio Vinho

Reputation: 23

Error in INSERT statement with PostgreSql and PHP

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

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.

Upvotes: 0

Views: 596

Answers (1)

Daniel V&#233;rit&#233;
Daniel V&#233;rit&#233;

Reputation: 61506

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);

Upvotes: 2

Related Questions