PDO and PostgreSQL Function

Question

I have function in my PostgreSQL Server

CREATE OR REPLACE FUNCTION edit_senti_meta_translation(_senti_id bigint, _meta_id bigint, _lang_code character, _meta_title character varying, _meta_note text)
  RETURNS boolean AS
$BODY$
BEGIN
    PERFORM
        senti.is_draft
    FROM
        senti, senti_meta
    WHERE
        senti.senti_id=_senti_id
    AND senti_meta.senti_id=senti.senti_id
    AND senti_meta.senti_id=_senti_id
    AND senti_meta.meta_id=_meta_id;
    IF FALSE THEN
        RETURN FALSE;
    END IF;
    BEGIN
        UPDATE senti_meta_translation
        SET meta_title=_meta_title, meta_note=_meta_note
        WHERE meta_id=_meta_id AND lang_code=_lang_code;
        IF FOUND THEN
            UPDATE senti_meta SET last_update=now() WHERE senti_id=_senti_id AND meta_id=_meta_id;
            RETURN TRUE;
        ELSE
            RETURN FALSE;
        END IF;
    END;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Well, It's working if i try in pgadmin3, and My record was updated(Work 100% as i expected) if i call this function

SELECT edit_senti_meta_translation(1, 41, 'eng', 'a', 'a');

But When i try to call the function using PHP(PDO Driver) It give me the result true or false, but The UPDATE is not running, My record is not updated.

$stmt = Database::getInstance()->prepare('SELECT * FROM edit_senti_meta_translation(:senti_id, :meta_id, :lang_code, :meta_title, :meta_note);');
$stmt->bindParam(':senti_id', $senti_id, PDO::PARAM_INT);
$stmt->bindParam(':meta_id', $meta_id, PDO::PARAM_INT);
$stmt->bindParam(':lang_code', $lang_code, PDO::PARAM_STR);
$stmt->bindParam(':meta_title', $meta_title, PDO::PARAM_STR);
$stmt->bindParam(':meta_note', $meta_note, PDO::PARAM_STR);
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->execute();
return $stmt->fetchColumn(0);

Even when i change the first line in my php code

$stmt = Database::getInstance()->prepare("SELECT edit_senti_meta_translation(1, 41, 'eng', 'a', 'a');");

It give me the result(true/false) but the record is not updated. This make me so confused

For info, i'm using PostgreSQL 8.4

One thing that makes me confused is when i call the function directly in pgadmin3, it give me the correct return with record updated, but when call it from PHP(PDO), it only give me return, but the record is not updated.

Should IF FALSE THEN be IF NOT FOUND THEN? Also, the UPDATE statement later doesn't set the value of FOUND. Search for "OUTPUTS" in the docs. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Feb 2, 2013 at 11:31
Maybe you need SELECT senti.is_draft INTO v_is_draft FROM ... and IF NOT v_is_draft THEN RETURN FALSE ? — Ihor Romanchenko
– Ihor Romanchenko, Commented Feb 2, 2013 at 11:37
@Catcall : I want to UPDATE when is_draft is returning FALSE, because i already do a validation regarding is the id found or not. so i should use 'IF FALSE THEN' — Ahmad
– Ahmad, Commented Feb 2, 2013 at 11:39
@Catcall UPDATE statement does set FOUND variable. see this manual. Part 39.5.5. — Ihor Romanchenko
– Ihor Romanchenko, Commented Feb 2, 2013 at 11:43
It might be related with transactions, if, for example, this code runs in a transaction, and there is no commit after it. — dmirkitanov
– dmirkitanov, Commented Feb 2, 2013 at 14:44

Ahmad · Accepted Answer · 2013-02-02 15:04:19Z

Well, after struggling with the problem, And @dmirkitanov reply with the idea why this may happen.

It might be related with transactions, if, for example, this code runs in a transaction, and there is no commit after it

I notice that on the PDO Singleton Class, some one in the project adding a new line. *See the commented line of code

<?php

include('config.database.php');

class Database {

    protected static $instance = null;

    final private function __construct() {}
    final private function __destruct() {
        //self::$instance->commit();
        self::$instance = null;
    }

    final private function __clone() {}

    public static function getInstance() {
        if (self::$instance === null) {
            try {
                self::$instance = new PDO(
                    'pgsql:host='   . DatabaseConfig::HOST . 
                    ';port='        . DatabaseConfig::PORT . 
                    ';dbname='      . DatabaseConfig::DBNAME . 
                    ';user='        . DatabaseConfig::USER . 
                    ';password='    . DatabaseConfig::PASSWORD
                );
                self::$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                self::$instance->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
                //self::$instance->beginTransaction();
            } catch (PDOException $e) {
                self::$instance->rollBack();
                die('Database connection could not be established.');
            }
        }

        return self::$instance;
    }
    public static function __callStatic($method, $args) {
        return call_user_func_array(array(self::instance(), $method), $args);
    }
}
?>

So as you can see, beginTransaction() is set in the getInstance(), but the commit() is set in __destruct()

Collectives™ on Stack Overflow

PDO and PostgreSQL Function

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