CODEWITHSUNDEEP
phpooppdodatatablesoracle-call-interface
user4499154
user4499154

Reputation: 11

Converting PHP script that uses OCI to use PDO

I have been using DataTables with the below PHP script that uses OCI however since I re-wrote the whole site to utilize PDO I now need to update this PHP script to also utilize PDO.

The problem is that there are no error messages reported (I have error reporting enabled) but the DataTable is empty.

Is there a noticeable mistake in how I converted it to utilize PDO?

OCI - works!

// Database connection information
$sql = array(
    'user'     => 'user',
    'password' => 'pass',
    'server'   => '192.168.0.1',
    'db'       => 'A.world'
);

// Oracle connection
$conn = oci_connect($sql['user'], $sql['password'], $sql['server'].'/'.$sql['db']);//$connection_string);

if (!$conn) {
    $e = oci_error();
    trigger_error( htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR );
}


// Action
$out = dt_oci_ssp( $conn, $query, $_POST );

echo json_encode( $out );



/**
 * DataTables 1.10+ server-side processing function for Oracle using oci_*
 * methods
 * @param  resource $conn  Connection from `oci_connect`
 * @param  array $query    Information for the query to be performed
 * @param  array $data     Data from the server-side processing request
 * @return array           Data returned for server-side processing
 */
function dt_oci_ssp( $conn, $query, $data ) {
    $bindings = array();

    /*
     * Ordering
     */
    $orderBy = array();

    if ( isset( $data['order'] ) ) {
        for ( $i=0, $size=count( $data['order'] ) ; $i<$size ; ++$i ) {
            $sort = $data['order'][$i];

            //Add to the order by clause
            $orderBy[] = dt_oci_field( $query, $sort['column'] ) .' '. (
                $sort['dir'] === 'asc' ?
                    'asc' :
                    'desc'
                );
        }
    }

    $orderBy = count( $orderBy ) ?
        'ORDER BY '.implode(', ', $orderBy) :
        '';


    // Conditions
    $where = array();
    $whereJoinOnly = array();

    $globalWhere = array();

    for ( $i=0, $size=count( $data['columns'] ) ; $i<$size ; ++$i ) {
        $column = $data['columns'][$i];

        if ( $column['searchable'] && $data['search']['value'] ) {
            $bindings[':globalSearch'.$i] = '%'.$data['search']['value'].'%';
            $globalWhere[] = dt_oci_field( $query, $i ) ." LIKE :globalSearch".$i;
        }
    }

    if ( count( $globalWhere ) ) {
        $where[] .= '('.implode(' OR ', $globalWhere).')';
    }


    // Column filter
    for ( $i=0, $size=count( $data['columns'] ) ; $i<$size ; ++$i ) {
        $column = $data['columns'][$i];

        if ( $column['searchable'] && $column['search']['value'] ) {
            $bindings[':columnSearch'.$i] = '%'.$column['search']['value'].'%';
            $where[] = dt_oci_field( $query, $i ) ." LIKE :columnSearch".$i;
        }
    }


    // Joins
    for ( $i=0, $size=count( $query['conditions'] ) ; $i<$size ; ++$i ) {
        $where[] = $query['conditions'][$i];
        $whereJoinOnly[] = $query['conditions'][$i];
    }

    $where = count( $where ) ?
        'WHERE '.implode( ' AND ', $where ) :
        '';

    $whereJoinOnly = count( $whereJoinOnly ) ?
        'WHERE '.implode( ' AND ', $whereJoinOnly ) :
        '';


    $tables = implode( ', ', $query['tables'] );

    $select = array();
    for ( $i=0, $size=count( $query['fields'] ) ; $i<$size ; ++$i ) {
        $select[] = dt_oci_field( $query, $i, 'select' ) .' as "'. dt_oci_field( $query, $i ).'"'; 
    }

    $select = implode( ', ', $select );


    $betweenLower = intval($data['start']);
    $betweenUpper = intval($data['start'] + $data['length']);

    if ( $data['length'] != -1 ) {
        $dataOut = dt_oci_exec( $conn, $bindings, <<<EOD
            SELECT a.*
            FROM (
                SELECT rownum rnum, b.*
                FROM (
                    SELECT $select
                    FROM $tables
                    $where
                    $orderBy
                ) b
                WHERE rownum <= $betweenUpper
            ) a
            WHERE rnum > $betweenLower
EOD
        );
    }
    else {
        // All records
        $dataOut = dt_oci_exec( $conn, $bindings, <<<EOD
            SELECT a.*
            FROM (
                SELECT rownum rnum, b.*
                FROM (
                    SELECT $select
                    FROM $tables
                    $where
                    $orderBy
                ) b
            ) a
EOD
        );
    }

    $filterCount = dt_oci_exec( $conn, $bindings, <<<EOD
        SELECT COUNT(*) as COUNT
        FROM $tables
        $where
        $orderBy
EOD
    );

    $fullCount = dt_oci_exec( $conn, $bindings, <<<EOD
        SELECT COUNT(*) as COUNT
        FROM $tables
        $whereJoinOnly
EOD
    );


    // Output
    $out = array(
        'draw'            => intval( $data['draw'] ),
        'data'            => array(),
        'recordsFiltered' => intval($filterCount['COUNT'][0]),
        'recordsTotal'    => intval($fullCount['COUNT'][0])
    );

    for ( $i=0, $ien=count($dataOut['RNUM']) ; $i<$ien ; ++$i ) {
        $row = array();

        for ( $j=0, $jen=count($data['columns']) ; $j<$jen ; ++$j ) {
            $row[] = $dataOut[ dt_oci_field( $query, $j ) ][$i];
        }

        $out['data'][] = $row;
    }

    return $out;
}


/**
 * Get a field property for a query
 * @param  array   $query Query information
 * @param  integer $idx   Field index
 * @param  string  $type  `name` or `select`
 * @return string         column name
 */
function dt_oci_field ( $query, $idx, $type='name' ) {
    if ( is_array( $query['fields'][$idx] ) ) {
        return $query['fields'][$idx][$type];
    }

    return $query['fields'][$idx];
}


/**
 * Perform a SQL command, returning the full result set
 * @param  resource $conn     Connection from `oci_connect`
 * @param  array    $bindings Binding information to use
 * @param  string   $query    SQL query
 * @return array              Results
 */
function dt_oci_exec( $conn, $bindings, $query )
{
    $stmt = oci_parse( $conn, trim($query) );

    foreach ($bindings as $key => $value) {
        if ( strpos( $query, $key) ) {
            oci_bind_by_name( $stmt, $key, $value );
        }
    }

    oci_execute( $stmt );
    oci_fetch_all( $stmt, $data );
    oci_free_statement( $stmt );

    return $data;

PDO - doesn't work! No error messages, connection is ok but no data passed back to DataTables :<

// Oracle connection
$dbh = new PDO('oci:dbname=//192.168.0.1:1521/A.world', 'user', 'pass', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));


// Action
$out = dt_oci_ssp( $dbh, $query, $_POST );

echo json_encode( $out );



/**
 * DataTables 1.10+ server-side processing function for Oracle using oci_*
 * methods
 * @param  resource $dbh  Connection from `oci_connect`
 * @param  array $query    Information for the query to be performed
 * @param  array $data     Data from the server-side processing request
 * @return array           Data returned for server-side processing
 */
function dt_oci_ssp( $dbh, $query, $data ) {
    $bindings = array();

    /*
     * Ordering
     */
    $orderBy = array();

    if ( isset( $data['order'] ) ) {
        for ( $i=0, $size=count( $data['order'] ) ; $i<$size ; ++$i ) {
            $sort = $data['order'][$i];

            //Add to the order by clause
            $orderBy[] = dt_oci_field( $query, $sort['column'] ) .' '. (
                $sort['dir'] === 'asc' ?
                    'asc' :
                    'desc'
                );
        }
    }

    $orderBy = count( $orderBy ) ?
        'ORDER BY '.implode(', ', $orderBy) :
        '';


    // Conditions
    $where = array();
    $whereJoinOnly = array();

    $globalWhere = array();

    for ( $i=0, $size=count( $data['columns'] ) ; $i<$size ; ++$i ) {
        $column = $data['columns'][$i];

        if ( $column['searchable'] && $data['search']['value'] ) {
            $bindings[':globalSearch'.$i] = '%'.$data['search']['value'].'%';
            $globalWhere[] = dt_oci_field( $query, $i ) ." LIKE :globalSearch".$i;
        }
    }

    if ( count( $globalWhere ) ) {
        $where[] .= '('.implode(' OR ', $globalWhere).')';
    }


    // Column filter
    for ( $i=0, $size=count( $data['columns'] ) ; $i<$size ; ++$i ) {
        $column = $data['columns'][$i];

        if ( $column['searchable'] && $column['search']['value'] ) {
            $bindings[':columnSearch'.$i] = '%'.$column['search']['value'].'%';
            $where[] = dt_oci_field( $query, $i ) ." LIKE :columnSearch".$i;
        }
    }


    // Joins
    for ( $i=0, $size=count( $query['conditions'] ) ; $i<$size ; ++$i ) {
        $where[] = $query['conditions'][$i];
        $whereJoinOnly[] = $query['conditions'][$i];
    }

    $where = count( $where ) ?
        'WHERE '.implode( ' AND ', $where ) :
        '';

    $whereJoinOnly = count( $whereJoinOnly ) ?
        'WHERE '.implode( ' AND ', $whereJoinOnly ) :
        '';


    $tables = implode( ', ', $query['tables'] );

    $select = array();
    for ( $i=0, $size=count( $query['fields'] ) ; $i<$size ; ++$i ) {
        $select[] = dt_oci_field( $query, $i, 'select' ) .' as "'. dt_oci_field( $query, $i ).'"'; 
    }

    $select = implode( ', ', $select );


    $betweenLower = intval($data['start']);
    $betweenUpper = intval($data['start'] + $data['length']);

    if ( $data['length'] != -1 ) {
        $dataOut = dt_oci_exec( $dbh, $bindings, <<<EOD
            SELECT a.*
            FROM (
                SELECT rownum rnum, b.*
                FROM (
                    SELECT $select
                    FROM $tables
                    $where
                    $orderBy
                ) b
                WHERE rownum <= $betweenUpper
            ) a
            WHERE rnum > $betweenLower
EOD
        );
    }
    else {
        // All records
        $dataOut = dt_oci_exec( $dbh, $bindings, <<<EOD
            SELECT a.*
            FROM (
                SELECT rownum rnum, b.*
                FROM (
                    SELECT $select
                    FROM $tables
                    $where
                    $orderBy
                ) b
            ) a
EOD
        );
    }

    $filterCount = dt_oci_exec( $dbh, $bindings, <<<EOD
        SELECT COUNT(*) as COUNT
        FROM $tables
        $where
        $orderBy
EOD
    );

    $fullCount = dt_oci_exec( $dbh, $bindings, <<<EOD
        SELECT COUNT(*) as COUNT
        FROM $tables
        $whereJoinOnly
EOD
    );


    // Output
    $out = array(
        'draw'            => intval( $data['draw'] ),
        'data'            => array(),
        'recordsFiltered' => intval($filterCount['COUNT'][0]),
        'recordsTotal'    => intval($fullCount['COUNT'][0])
    );

    for ( $i=0, $ien=count($dataOut['RNUM']) ; $i<$ien ; ++$i ) {
        $row = array();

        for ( $j=0, $jen=count($data['columns']) ; $j<$jen ; ++$j ) {
            $row[] = $dataOut[ dt_oci_field( $query, $j ) ][$i];
        }

        $out['data'][] = $row;
    }

    return $out;
}


/**
 * Get a field property for a query
 * @param  array   $query Query information
 * @param  integer $idx   Field index
 * @param  string  $type  `name` or `select`
 * @return string         column name
 */
function dt_oci_field ( $query, $idx, $type='name' ) {
    if ( is_array( $query['fields'][$idx] ) ) {
        return $query['fields'][$idx][$type];
    }

    return $query['fields'][$idx];
}


/**
 * Perform a SQL command, returning the full result set
 * @param  resource $dbh      Connection from `oci_connect`
 * @param  array    $bindings Binding information to use
 * @param  string   $query    SQL query
 * @return array              Results
 */
function dt_oci_exec( $dbh, $bindings, $query )
{
 try {
  $stmt = $dbh->prepare($query);

  foreach ($bindings as $key => $value) {
   if ( strpos( $query, $key) ) {
    $stmt->bindParam(":$key", $value);
   }
  }

  $stmt->execute();
  $data = $stmt->fetchAll();
  $stmt = null;

  return $data;
 } catch (PDOException $e) {
  echo 'Connection failed: ' . $e->getMessage();
 }
}

I have sat and looked at both code for a number of hours now and as far as I can tell I have converted it to use the correct PDO commands etc.

Upvotes: 0

Views: 1079

Answers (1)

Marc B
Marc B

Reputation: 360672

Wrong quotes:

$stmt->bindParam(':$key', $value);
                 ^-----^--

'-quoted strings do not interpolate variable values, so you're binding a placeholder with the literal name $, k, e, etc... which doesn't exist in your query. So the bind fails, and since you have no error handling in your code, you never see the error messages PDO could be giving you.

NEVER assume success. Always assume failure, check for that failure, and treat success as a pleasant surprise.

Try

$stmt->bindParam(":$key", $value);
                 ^-----^---

instead.

Upvotes: 1

Related Questions