PHP oci_bind_by_name函数代码示例

 public function readCursor($storedProcedure, $binds)
 {
     //
     // This function needs two parameters:
     //
     // $storedProcedure - the name of the stored procedure to call a chamar. Ex:
     //  my_schema.my_package.my_proc(:param)
     //
     // $binds - receives an array of associative arrays with: parameter names,
     // values and sizes
     //
     // WARNING: The first parameter must be consistent with the second one
     $conn = oci_connect('SECMAN', 'SECMAN', '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.10.24)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = cisqa)))');
     if ($conn) {
         // Create the statement and bind the variables (parameter, value, size)
         $stid = oci_parse($conn, 'begin :cursor := ' . $storedProcedure . '; end;');
         foreach ($binds as $variable) {
             oci_bind_by_name($stid, $variable["parameter"], $variable["value"], $variable["size"]);
         }
         // Create the cursor and bind it
         $p_cursor = oci_new_cursor($conn);
         oci_bind_by_name($stid, ':cursor', $p_cursor, -1, OCI_B_CURSOR);
         // Execute the Statement and fetch the data
         oci_execute($stid);
         oci_execute($p_cursor, OCI_DEFAULT);
         oci_fetch_all($p_cursor, $data, null, null, OCI_FETCHSTATEMENT_BY_ROW);
         // Return the data
         return $data;
     }
 }

 public function getDetalle()
 {
     $sql = "SELECT trigger_name, trigger_type, triggering_event, table_name, status, description, trigger_body FROM user_triggers WHERE trigger_name = UPPER(:v_trigger_name)";
     $stmt = oci_parse($this->getConnection(), $sql);
     oci_bind_by_name($stmt, ":v_trigger_name", $this->objectName);
     if (!@oci_execute($stmt)) {
         $e = oci_error($stmt);
         $this->setMensaje("Error al obtener los datos del trigger '{$this->objectName}' de la tabla user_triggers - {$e['message']}");
         return false;
     }
     $row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS);
     if (empty($row)) {
         $this->setMensaje("No se pudo encontrar el trigger especificado en la tabla user_triggers");
         return false;
     }
     $this->triggerType = $row['TRIGGER_TYPE'];
     $this->triggeringEvent = $row['TRIGGERING_EVENT'];
     $this->affectedTable = $row['TABLE_NAME'];
     $this->triggerStatus = $row['STATUS'];
     $this->description = $row['DESCRIPTION'];
     $this->triggerSql = $row['TRIGGER_BODY'];
     if ($this->triggerStatus != 'ENABLED') {
         $this->setMensaje("El trigger se encuentra inhabilitado");
         return false;
     }
     return true;
 }

 /**
  * Executes a prepared statement.
  *
  * @param array $args an array of values with as many elements as there are bound parameters in the statement
  * @return boolean returns true on success or false on failure
  */
 public function execute($args = array())
 {
     foreach ($args as $key => $value) {
         oci_bind_by_name($this->statement, ":" . $key, $args[$key]);
     }
     return oci_execute($this->statement, OCI_DEFAULT);
 }

 /**
  * Obtiene el SQL de la funcion especificada
  * @return String or false
  */
 protected function getObjectSql()
 {
     if ($this->remote) {
         $sql = "select line, text from all_source where name = UPPER(:v_function_name) and type = :v_object_type order by name, type, line";
     } else {
         $sql = "select line, text from user_source where name = UPPER(:v_function_name) and type = :v_object_type order by name, type, line";
     }
     $stmt = oci_parse($this->getConnection(), $sql);
     oci_bind_by_name($stmt, ":v_function_name", $this->objectName);
     oci_bind_by_name($stmt, ":v_object_type", $this->objectType);
     if (!@oci_execute($stmt)) {
         $e = oci_error($stmt);
         $this->setMensaje("Error al obtener el SQL del objeto {$this->objectType} '{$this->objectName}' - {$e['message']}");
         return false;
     }
     $sqlResult = '';
     while ($row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS)) {
         $sqlResult .= $row['TEXT'];
     }
     $this->sourceSql = $sqlResult;
     if (empty($sqlResult)) {
         $this->setMensaje("No se pudo obtener el SQL del objeto {$this->objectType} '{$this->objectName}'");
         return false;
     }
     return $this->sourceSql;
 }

function get_filteredGames($data)
{
    // The connection string is loooooooong. It's easiest to copy/paste this line. Remember to replace 'username' and 'password'!
    $conn = oci_connect('malz', '1Qaz2wsx', '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=db1.chpc.ndsu.nodak.edu)(Port=1521)))(CONNECT_DATA=(SID=cs)))');
    if ($data === 'all') {
        $results = array();
        $query = 'select * from Game';
        $stid = oci_parse($conn, $query);
        oci_bind_by_name($stid, ':data', $data);
        oci_execute($stid);
        //iterate through each row
        while ($row = oci_fetch_array($stid, OCI_ASSOC)) {
            $results[] = $row;
        }
        echo json_encode($results);
        oci_free_statement($stid);
        oci_close($conn);
    } else {
        $results = array();
        $data = $data . '%';
        $query = 'select * from Game where gameName like :data';
        $stid = oci_parse($conn, $query);
        oci_bind_by_name($stid, ':data', $data);
        oci_execute($stid);
        //iterate through each row
        while ($row = oci_fetch_array($stid, OCI_ASSOC)) {
            $results[] = $row;
        }
        echo json_encode($results);
        oci_free_statement($stid);
        oci_close($conn);
    }
}

 protected function getDetalle()
 {
     $sql = "SELECT synonym_name, table_owner, table_name, db_link FROM user_synonyms WHERE synonym_name = UPPER(:v_synonym_name)";
     $stmt = oci_parse($this->getConnection(), $sql);
     oci_bind_by_name($stmt, ":v_synonym_name", $this->objectName);
     if (!@oci_execute($stmt)) {
         $e = oci_error($stmt);
         $this->setMensaje("Error al obtener los datos del sinónimo '{$this->objectName}' de la tabla user_synonyms - {$e['message']}");
         $this->setEstado(false);
         return false;
     }
     $row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS);
     if (empty($row)) {
         $sqlPublic = "SELECT * FROM all_synonyms WHERE synonym_name = UPPER(:v_synonym_name) AND owner = 'PUBLIC'";
         $stmt2 = oci_parse($this->getConnection(), $sqlPublic);
         oci_bind_by_name($stmt2, ":v_synonym_name", $this->objectName);
         if (!@oci_execute($stmt2)) {
             $e = oci_error($stmt2);
             $this->setMensaje("Error al obtener los datos del sinónimo '{$this->objectName}' de la tabla all_synonyms - {$e['message']}");
             $this->setEstado(false);
             return false;
         }
         $row = oci_fetch_array($stmt2, OCI_ASSOC | OCI_RETURN_NULLS);
         if (empty($row)) {
             $this->setMensaje("No se encontró el sinónimo '{$this->objectName}' en la tabla user_synonyms");
             $this->setEstado(false);
             return false;
         }
     }
     $this->tableOwner = $row['TABLE_OWNER'];
     $this->tableName = $row['TABLE_NAME'];
     $this->dbLinkName = $row['DB_LINK'];
     $this->setEstado(true);
     return true;
 }

function insertImage($conn, $photo_id, $owner_name, $descriptive_info, $thumbnail, $photo)
{
    $photo_blob = oci_new_descriptor($conn, OCI_D_LOB);
    $thumbnail_blob = oci_new_descriptor($conn, OCI_D_LOB);
    $subject = $descriptive_info[0];
    $place = $descriptive_info[1];
    $date_time = $descriptive_info[2];
    $description = $descriptive_info[3];
    $permitted = $descriptive_info[4];
    $sql = 'INSERT INTO images (photo_id, owner_name, permitted, subject, place,
		timing, description, thumbnail, photo) VALUES (:photoid, :ownername,
		:permitted, :subject, :place, TO_DATE(:datetime, \'MM/DD/YYYY\'), :description, empty_blob(),
		empty_blob()) returning thumbnail, photo into :thumbnail, :photo';
    $stid = oci_parse($conn, $sql);
    oci_bind_by_name($stid, ':photoid', $photo_id);
    oci_bind_by_name($stid, ':ownername', $owner_name);
    oci_bind_by_name($stid, ':permitted', $permitted);
    oci_bind_by_name($stid, ':subject', $subject);
    oci_bind_by_name($stid, ':place', $place);
    oci_bind_by_name($stid, ':datetime', $date_time);
    oci_bind_by_name($stid, ':description', $description);
    oci_bind_by_name($stid, ':thumbnail', $thumbnail_blob, -1, OCI_B_BLOB);
    oci_bind_by_name($stid, ':photo', $photo_blob, -1, OCI_B_BLOB);
    $res = oci_execute($stid, OCI_DEFAULT);
    if ($thumbnail_blob->save($thumbnail) && $photo_blob->save($photo)) {
        oci_commit($conn);
    } else {
        oci_rollback($conn);
    }
    oci_free_statement($stid);
    $photo_blob->free();
    $thumbnail_blob->free();
}

 function dispCart()
 {
     $cartLen = count($_SESSION['cart']);
     if ($cartLen < 1) {
         echo 'You have no items in your cart.<br><a href="main.php">Keep shopping</a><br>';
         die;
     }
     //for loop to iterate through cart items
     for ($i = 0; $i < $cartLen; $i++) {
         if ($cartLen > 0) {
             $newconn = conndb();
             //sql
             $s = oci_parse($newconn, "select * from PRODUCT where PRODUCTID=:pid_prefix");
             $plook = $_SESSION['cart'][$i]['productid'];
             oci_bind_by_name($s, ':pid_prefix', $plook);
             oci_execute($s);
             //fetch a single row depending on product id
             $res = oci_fetch_assoc($s);
             echo "Product name: ", $res['PRODUCTNAME'], " Price: ", $res['PRODUCTPRICE'];
             echo '<a href="cart.php?del=' . $i . '"> Remove item</a><br>';
         }
     }
     if ($cartLen > 0) {
         echo '<a href="checkout.php">Proceed to checkout</a><br>';
     }
 }

 public static function bindValues($stmt, $binds, $types)
 {
     foreach ($binds as $key => $field) {
         switch ($types[$key]) {
             case 'numeric':
             case 'string':
             case 'date':
             case 'time':
             case 'datetime':
                 oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1);
                 break;
             case 'int':
                 oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1, SQLT_INT);
                 break;
             case 'boolean':
                 oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1);
                 break;
             case 'blob':
                 oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1, SQLT_BLOB);
                 break;
             case 'custom':
                 oci_bind_by_name($stmt, ":" . ($key + 1), $binds[$key], -1);
                 break;
         }
     }
     return true;
 }

 public function buscarPorProcedimiento()
 {
     $sql = "SELECT job, to_char(last_date, 'DD/MM/YYYY') last_date, last_sec, to_char(next_date, 'DD/MM/YYYY') next_date, next_sec, interval, failures, what from user_jobs WHERE UPPER(what) LIKE UPPER('%' || :v_procedure_name || '%')";
     $stmt = oci_parse($this->getConnection(), $sql);
     oci_bind_by_name($stmt, ":v_procedure_name", $this->objectName);
     if (!@oci_execute($stmt)) {
         $e = oci_error($stmt);
         $this->setMensaje("Error al obtener los datos del job que ejecuta el proceso '{$this->objectName}' de la tabla user_jobs - {$e['message']}");
         $this->setEstado(false);
     }
     $row = oci_fetch_array($stmt, OCI_ASSOC | OCI_RETURN_NULLS);
     if (empty($row)) {
         $this->setEstado(false);
     }
     $this->jobId = $row['JOB'];
     $this->lastDate = $row['LAST_DATE'];
     $this->lastSec = $row['LAST_SEC'];
     $this->nextDate = $row['NEXT_DATE'];
     $this->nextSec = $row['NEXT_SEC'];
     $this->interval = $row['INTERVAL'];
     $this->failures = $row['FAILURES'];
     $this->jobSql = $row['WHAT'];
     $this->setEstado(true);
     return $this->getEstado();
 }

function actualizarPassword($newpassword, $token)
{
    $conex = DataBase::getInstance();
    $stid = oci_parse($conex, "UPDATE FISC_USERS SET \n\t\t\t\t\t\tpassword=:newpassword\n\t\t\t\t    WHERE token=:token");
    if (!$stid) {
        oci_free_statement($stid);
        oci_close($conex);
        return false;
    }
    // Realizar la lógica de la consulta
    oci_bind_by_name($stid, ':token', $token);
    oci_bind_by_name($stid, ':newpassword', $newpassword);
    $r = oci_execute($stid, OCI_NO_AUTO_COMMIT);
    if (!$r) {
        oci_free_statement($stid);
        oci_close($conex);
        return false;
    }
    $r = oci_commit($conex);
    if (!$r) {
        oci_free_statement($stid);
        oci_close($conex);
        return false;
    }
    oci_free_statement($stid);
    // Cierra la conexión Oracle
    oci_close($conex);
    return true;
}

 public function bindByName($bvName, &$variable, $maxLength = -1, $type = SQLT_CHR)
 {
     set_error_handler(static::getErrorHandler());
     $isSuccess = oci_bind_by_name($this->resource, $bvName, $variable, $maxLength, $type);
     restore_error_handler();
     return $isSuccess;
 }

 /**
  * Excecutes a statement
  *
  * @return boolean
  */
 public function query($sql, array $params = array())
 {
     $this->numRows = 0;
     $this->numFields = 0;
     $this->rowsAffected = 0;
     $this->arrayResult = null;
     $this->result = $stid = oci_parse($this->dbconn, $sql);
     # Bound variables
     if (count($params)) {
         foreach ($params as $var => $value) {
             oci_bind_by_name($stid, $var, $value);
         }
     }
     $r = $this->transac_mode ? @oci_execute($stid, OCI_NO_AUTO_COMMIT) : @oci_execute($stid, OCI_COMMIT_ON_SUCCESS);
     if (!$r) {
         $error = oci_error($this->result);
         $this->error($error["code"], $error["message"]);
         if (count($this->errors)) {
             throw new Exception($error["message"], $error["code"]);
         } else {
             throw new Exception("Unknown error!");
         }
     }
     # This should be before of getArrayResult() because oci_fetch() is incremental.
     $this->rowsAffected = oci_num_rows($stid);
     $rows = $this->getArrayResult();
     $this->numRows = count($rows);
     $this->numFields = oci_num_fields($stid);
     if ($this->transac_mode) {
         $this->transac_result = is_null($this->transac_result) ? $this->result : $this->transac_result && $this->result;
     }
     return $this->result;
 }

 public function getById($id)
 {
     $this->conex = DataBase::getInstance();
     $stid = oci_parse($this->conex, "SELECT *\n\t\t\tFROM FISC_CIUDADANO WHERE ID_CIUDADANO=:id");
     if (!$stid) {
         $e = oci_error($this->conex);
         trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
     }
     // Realizar la lógica de la consulta
     oci_bind_by_name($stid, ':id', $id);
     $r = oci_execute($stid);
     if (!$r) {
         $e = oci_error($stid);
         trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
     }
     // Obtener los resultados de la consulta
     $alm = new FiscCiudadano();
     while ($fila = oci_fetch_array($stid, OCI_ASSOC + OCI_RETURN_NULLS)) {
         $it = new ArrayIterator($fila);
         while ($it->valid()) {
             $alm->__SET(strtolower($it->key()), $it->current());
             $it->next();
         }
     }
     //Libera los recursos
     oci_free_statement($stid);
     // Cierra la conexión Oracle
     oci_close($this->conex);
     //retorna el resultado de la consulta
     return $alm;
 }

 public function prepareStatement(AdapterInterface $adapter, StatementContainerInterface $statementContainer)
 {
     /* init */
     $this->hasAutoincrement = false;
     /* parent prep */
     $result = parent::prepareStatement($adapter, $statementContainer);
     /* oci8 with autoincrement */
     if ($statementContainer instanceof \Zend\Db\Adapter\Driver\Oci8\Statement && $this->autoincrement !== null) {
         /* get sequence */
         if ($this->sequence === null) {
             $this->sequence = 'SEQ_' . $this->table;
         }
         /* replace ai field with sequence & move ai field binding to the end with returning */
         $count = 0;
         $sql = preg_replace('/:' . $this->autoincrement . '\\s*/', $this->sequence . '.NEXTVAL', $statementContainer->getSql(), 1, $count) . ' RETURNING "' . $this->autoincrement . '" INTO :' . $this->autoincrement;
         /* anything replaced? */
         if ($count > 0) {
             /* prep statement to prep resource */
             $statementContainer->setSql($sql);
             $statementContainer->prepare();
             /* unset ai field */
             $statementContainer->getParameterContainer()->offsetUnset($this->autoincrement);
             /* get ai field position on values */
             $position = array_search($this->autoincrement, $this->columns);
             $this->values[$position] = 0;
             $this->hasAutoincrement = true;
             oci_bind_by_name($statementContainer->getResource(), $this->autoincrement, $this->values[$position], -1, SQLT_INT);
         }
     }
     //oci8 AI
     return $result;
 }