Using anonimous functions and closures to abstract try-catch statements in sql queries

Question

I am using php in a project without a framework. I am using PDO. Now, I am declaring lots of sql queries to be used all over the application, like this.

function f($parameters) {
    try {
        $query = "";
        $stmt = $this->con->prepare($query);
        $stmt->execute(); // might have parameters
        // return true if not a select statement
        return $stmt->fetchAll(); // only fetch if looking for only one row
    } catch(exception $e) {
        $this->errors[] = "SQL failed: " . $e->getMessage();
        return false;
    }
    }
}

I am doing this lots of times, as a result the code is extremely long. So I decided to rapped the try-catch code into a function that takes a closure as an argument.

protected function exec_query($statement) {
    try {
        return $statement();
    } catch (Exception $e) {
        $this->errors[] = "SQL failed: " . $e->getMessage();
        return false;
    }
}

I am using it like this:

return parent::exec_query(function() use() {
    // any sql statement
});

this one is for transactions.

protected function exec_transaction($statements) {
    try {
        $this->con->beginTransaction();
        $statements();
        // if we are still inside a transaction. some $statement might rollback the running transaction, so just return false in such a case.
        if ($this->con->inTransaction()) {
            $this->con->commit();
        } else {
            return false;
        }
    } catch (Exception $e) {
        $this->con->rollback();
        $this->errors[] = "SQL failed: " . $e->getMessage();
        return false;
    }
    return true;
}

I'll use it same as with exec_query, but with more statements to be executed inside a transaction.

Regarding the inTransaction() check, consider this example: I have to execute three insert statements. Each of those insert statements belongs into separate functions. This is because they might be reused outside transactions. The thing is inside each function is all the validation required before the insert statements get executed (E.G. if user exist, throw an error). For example,

function add() {
    if (exist) {
        // if there is a running transaction, rollback it.
        if ($this->con->inTransaction()) {
            $this->con->rollback();
            return false;
        }
    } else {
    // insert
    }
}

All of the three functions are like this, because they might be executed separately from each other.

Is what I am doing good practice? Is there some cases they might lead me in trubble in the future by doing this in all of my sql queries?


Show source
| pdo   | php   | sql   | closures   | transactions   2017-01-07 06:01 0 Answers

Answers ( 0 )

◀ Go back