#35809 [Fbk->Csd]: PDO problem with insert transaction with prepared query and currval - Postgres

From: Date: Mon, 26 Dec 2005 19:22:20 +0000
Subject: #35809 [Fbk->Csd]: PDO problem with insert transaction with prepared query and currval - Postgres
References: 1  Groups: php.bugs 
Request: Send a blank email to php-bugs+get-91085@lists.php.net to get a copy of this message 
 ID:               35809
 User updated by:  edrozenberg at pobox dot com
 Reported By:      edrozenberg at pobox dot com
-Status:           Feedback
+Status:           Closed
 Bug Type:         PDO related
 Operating System: WindowsXP SP2
 PHP Version:      5.1.1
 New Comment:

Turns out to have been an error between chair and desk -

currval('mysequence') cannot be a value for a prepared parameter.  It
needs to be in the text of the prepared query.    

So the part of the query that said this:

insert into t_doc (company_id, doctype, private_flag)
                   values (?, ?, ?)'

instead needs to say this:

insert into t_doc (company_id, doctype, private_flag)
                   values (currval(\'mysequence\'), ?, ?)'

Notice that the currval is now in the body of the query
itself, it is not being substituted for a ? placeholder.

Thanks!


Previous Comments:
------------------------------------------------------------------------

[2005-12-26 18:23:40] tony2001@php.net

Please try using this CVS snapshot:

  http://snaps.php.net/php5.1-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php5.1-win32-latest.zip



------------------------------------------------------------------------

[2005-12-26 18:20:02] edrozenberg at pobox dot com

Description:
------------
Created a Database class containing an insert function.
The function takes an array of statements and values
and executes each one within a transaction.

I test it using two statements.  The 2nd statement uses
currval('mysequence') to insert an ID that is the same 
as the one generated by the first statement.

Problem is the inserts do not take place, and there is
also no exception raised so it looks like the transaction
committed just fine.

Reproduce code:
---------------
Below is the function from the Database.php class:

function insert($inserts)
{
    $this->dbh->beginTransaction();
    try {
        foreach ($inserts as $insert) {
            $stmt = $this->dbh->prepare($insert['query']);
            $stmt->execute($insert['values']);
        }
    } catch (Exception $e) {
        $this->dbh->rollBack();
        echo "Failed: " . $e->getMessage();
        return 0;
    }
    $this->dbh->commit();
    return 1;
}

======================================================
I call the Database.php class from here:

<?php
require ('./lib/Database.php');

$db = new Database(NULL);
$inserts[] = array("query"=>'insert into t_company (name, url,
description)  values (?, ?, ?)',
"values"=>array('IBM','www.ibm.com','The IBM Company'));
$inserts[] = array("query"=>'insert into t_doc     (company_id,
doctype,   private_flag) values (?, ?, ?)',
"values"=>array('currval(\'t_company_company_id_seq\')','patent','f'));
$db->insert($inserts)

?>


Expected result:
----------------
The company IBM should be inserted into table t_company.

A row should be inserted into the table t_doc with the
column company_id pointing to the IBM row in t_company.



------------------------------------------------------------------------


-- 
Edit this bug report at http://bugs.php.net/?id=35809&edit=1

Thread (4 messages)

« previous php.bugs (#91085) next »