Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Views

Difference between revisions of "Migrating from MDB2 to PDO"

From IHRIS Wiki

Jump to: navigation, search
(Created page with "==Server Changes== For Ubuntu 14.04, you must replace the default PHP MySQL driver: <source lang="bash"> sudo apt install php5-mysqlnd </source> ==PHP Code Changes== ===Updat...")
(No difference)

Revision as of 16:03, 22 September 2017

Server Changes

For Ubuntu 14.04, you must replace the default PHP MySQL driver:

sudo apt install php5-mysqlnd

PHP Code Changes

Update Database Reference

In the below examples, $db will refer to the database object that was returned from MDB2::singleton() or I2CE::PDO() or it may refer to those strings exactly. You may see $db->prepare(...) or MDB2::singleton()->prepare(...).

Change:
MDB2::singleton()
to:
I2CE::PDO()

Update Error Checks (for all database queries)

Change pearError blocks to try catch:

$result = $db->query($qry);
if ( I2CE::pearError( $result, "MESSAGE" ) ) {
    return false;
}

becomes:

try {
    $result = $db->query($qry);
    // Do something with $result

    // Free result
    unset( $result );  // in most cases
    $result = null;    // in big loops or very frequent functions
} catch( PDOException $e ) {
    I2CE::pdoError( $e, "MESSAGE" );
    return false;
}

Prepared Statements

Remove extra arguments for field types from the call to prepare().

Prepared statements also act as the result. Note that in many cases the prepare statement will be in a different place than the execute and fetch calls.

$stmt = $db->prepare( $qry, $field_types, MDB2_PREPARE_RESULT );
if ( I2CE::pearError( $stmt, "MESSAGE" ) ) {
    // handle failure or exit
}

...

$result = $stmt->execute();
if ( I2CE::pearError( $result, "MESSAGE" ) ) {
    // handle failure
} else {
    while ( $data = $result->fetchRow() ) {
        // Do something
    }
}

becomes:

try {
    $stmt = $db->prepare( $qry );
catch ( PDOException $e ) {
    I2CE::pdoError( $e, "MESSAGE" );
    // handle failure or exit
}

...

try {
    $stmt->execute();
    while( $data = $stmt->fetch() ) {
        // Do something
    }
    $stmt->closeCursor();
} catch ( PDOException $e ) {
    I2CE::pdoError( $e, "MESSAGE" );
    // handle failure
}

execParam method

Change:

$db->execParam( $qry, $params, $types );

to:

try {
    I2CE_PDO::execParam( $qry, $params );
} catch ( PDOException $e ) {
    I2CE::pdoError( $e, "MESSAGE" );
}

getRow method

Change:

$row = $db->getRow( $qry, $types, $params, $param_types );
if ( I2CE::pearError( $row, "ERROR " ) ) {
    return false;
}

To:

try {
    $row = I2CE_PDO::getRow( $qry, $params );
} catch( PDOException $e ) {
    I2CE::pdoError( $e, "ERROR" );
    return false;
}

getBeforeID/getAfterID (sequence) methods

Change:

$new_id = $this->db->getBeforeID( $table, $col, true, true );
$this->db->exec( $stmt );
$new_id = $this->db->getAfterID( $new_id, $table, $col );

To:

$this->db->exec( $stmt );
$new_id = $this->db->lastInsertId();

queryCol method

Change:

$var = $db->queryCol( $qry, # )

to:

$result = $pdo->query( $qry );
$var = $result->fetchAll( PDO::FETCH_COLUMN, # );

queryAll method

Change:

$var = $db->queryAll( $qry )

to:

$result = $pdo->query( $qry );
$var = $result->fetchAll();

getOne method

Change:

$res = $db->getOne( $qry );

to:

$result = $pdo->query( $qry );
$res = $result->fetchColumn();

mysql_real_escape_string function

If there is no other option to replace mysql_real_escape_string then do the following. Note that $db->quote() will return a string in quotes that is also escaped. Change:

"'" . mysql_real_escape_string($var) . "'"

to:

$db->quote( $var )

If you don't need it to return quotes, then change:

mysql_real_escape_string()

to:

I2CE_PDO::escape_string()

Method and Field changes

Change To
numRows() rowCount()
fetchRow() fetch()
in_transaction inTransaction()
$db->database_name I2CE_PDO::details('dbname')
$db->dsn['username'] I2CE_PDO::details('user')
$db->dsn['password'] I2CE_PDO::details('pass')
$db->getOption('result_buffering') $db->getAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY)