Problems with lastInsertId ()

Question

Problems with lastInsertId ()

Navigation

#1 by (0 votes)

0

In a Windows Server 2008 installation, PostgreSQL database, I have the following piece of code in PHP:

$this->con->beginTransaction();
try {
    $stmt = $this->con->prepare(
        'INSERT INTO sedoc.proc_dest(id_categoria, ci)'     
        . ' VALUES (:categoria, :ci)'
    );
    $stmt->bindValue(':ci', trim($procdestModel->getCI())); 
    $stmt->bindValue(':categoria', $categoria);
    $stmt->execute();
    $ultimo = $this->con->lastInsertId('proc_dest_id_proc_dest_seq');
    this->con->commit();
    return $ultimo;
} catch (Exception $e) {
    $this->con->rollBack();
    Message::setMessage('registro', 'Error!', 'error');
    return false;
}

Successfully insert the record in the table, but lastInsertId returns nothing. The issue is that I need that courage to continue working with him. The name of the sequence is correct ... It is BEFORE the commit ... if I remove the transaction, it continues with the same behavior. Any idea what is happening?

php pdo

asked by Maron 28.03.2017 в 00:12

source

1 answer

How to send data from an html table in nodejs? Recover Files from the SQL Server Database

score 0 · Answer 1

It seems to me that lastInsertId works for MySQL but not for Postgres, although it is documented that passing it the name of the sequence does work. Could it be that it does not work in transactions? I could not say it.

What I can say is that Postgres has the clause RETURNING . If your autoincremental is called proc_dest_id , then it would be:

$this->con->beginTransaction();

$stmt = $this->con->prepare(
    'INSERT INTO sedoc.proc_dest(id_categoria, ci)'     
    . ' VALUES (:categoria, :ci)'
    . ' RETURNING proc_dest_id'
);
$stmt->bindValue(':ci', trim($procdestModel->getCI())); 
$stmt->bindValue(':categoria', $categoria);
$stmt->execute();
$this->con->commit();
$ultimo = $stmt->fetchColumn();

How can that be to use fetch after an INSERT? Well, it's because the insert is returning something, so you can read the result instead of just checking that it was successful.