PHP OCIExecute函数代码示例

 function uploadImageData($db, $file, $currentPictureId, $table, $id)
 {
     // insert the new record into the media's table and load the
     // corresponding blob with the media's data
     // (we use oracle's pseudo column rowid which identifies a row
     // within a table (but not within a database) to refer to the
     // right record later on)
     $sql = "DECLARE\n                        obj ORDSYS.ORDImage;\n                        iblob BLOB;\n                BEGIN\n                        SELECT image INTO obj FROM {$table}\n                        WHERE {$id} = {$currentPictureId} FOR UPDATE;\n\n                        iblob := obj.source.localData;\n                        :extblob := iblob;\n\n                        UPDATE {$table} SET image = obj WHERE {$id} = {$currentPictureId};\n                END;";
     // the function OCINewDescriptor allocates storage to hold descriptors or
     // lob locators.
     // see http://www.php.net/manual/en/function.ocinewdescriptor.php
     $blob = OCINewDescriptor($db, OCI_D_LOB);
     $sql = strtr($sql, chr(13) . chr(10), " ");
     $stmt = OCIParse($db, $sql);
     // the function OCIBindByName binds a PHP variable to a oracle placeholder
     // (whether the variable will be used for input or output will be determined
     // run-time, and the necessary storage space will be allocated)
     // see http://www.php.net/manual/en/function.ocibindbyname.php
     OCIBindByName($stmt, ':extblob', $blob, -1, OCI_B_BLOB);
     echo "{$this->log} - {$sql} <br />";
     OCIExecute($stmt, OCI_DEFAULT);
     // read the files data and load it into the blob
     $blob->savefile($file);
     OCIFreeStatement($stmt);
     $blob->free();
 }

 function query($sql)
 {
     $query = OCIParse($this->conn, $sql);
     OCIExecute($query);
     OCIcommit($this->conn);
     return $query;
 }

 function executePlainSQL($cmdstr)
 {
     //takes a plain (no bound variables) SQL command and executes it
     //echo "<br>running ".$cmdstr."<br>";
     global $db_conn, $success;
     $statement = OCIParse($db_conn, $cmdstr);
     //There is a set of comments at the end of the file that describe some of the OCI specific functions and how they work
     if (!$statement) {
         echo "<br>Cannot parse the following command: " . $cmdstr . "<br>";
         $e = OCI_Error($db_conn);
         // For OCIParse errors pass the
         // connection handle
         echo htmlentities($e['message']);
         $success = False;
     }
     $r = OCIExecute($statement, OCI_DEFAULT);
     if (!$r) {
         echo "<br>Cannot execute the following command: " . $cmdstr . "<br>";
         $e = oci_error($statement);
         // For OCIExecute errors pass the statementhandle
         echo htmlentities($e['message']);
         $success = False;
     } else {
     }
     return $statement;
 }

function executeBoundSQL($cmdstr, $list)
{
    /* Sometimes a same statement will be excuted for severl times, only
    	 the value of variables need to be changed.
    	 In this case you don't need to create the statement several times; 
    	 using bind variables can make the statement be shared and just 
    	 parsed once. This is also very useful in protecting against SQL injection. See example code below for       how this functions is used */
    global $db_conn, $success;
    $statement = OCIParse($db_conn, $cmdstr);
    if (!$statement) {
        echo "<br>Cannot parse the following command: " . $cmdstr . "<br>";
        $e = OCI_Error($db_conn);
        echo htmlentities($e['message']);
        $success = False;
    }
    foreach ($list as $tuple) {
        foreach ($tuple as $bind => $val) {
            //echo $val;
            //echo "<br>".$bind."<br>";
            OCIBindByName($statement, $bind, $val);
            unset($val);
            //make sure you do not remove this. Otherwise $val will remain in an array object wrapper which will not be recognized by Oracle as a proper datatype
        }
        $r = OCIExecute($statement, OCI_DEFAULT);
        if (!$r) {
            echo "<br>Cannot execute the following command: " . $cmdstr . "<br>";
            $e = OCI_Error($statement);
            // For OCIExecute errors pass the statementhandle
            echo htmlentities($e['message']);
            echo "<br>";
            $success = False;
        }
    }
}

 function num_rows($query, $sql)
 {
     $sql = "select Count(*) NUM from ({$sql})";
     $query = OCIParse($this->conn, $sql);
     OCIExecute($query);
     ocifetchinto($query, $row, OCI_BOTH);
     return $row[NUM];
 }

function da_sql_num_rows($statement, $config)
{
    // Unfortunately we need to fetch the statement as ocirowcount doesn't work on SELECTs
    $rows = OCIFetchStatement($statement, $res);
    if ($config[sql_debug] == 'true') {
        print "<b>DEBUG(SQL,OCI DRIVER): Query Result: Num rows:: " . $rows . "</b><br>\n";
    }
    // Unfortunately we need to re-execute because the statement cursor is reset after OCIFetchStatement :-(
    OCIExecute($statement);
    return $rows;
}

 public function execQuery($name, $query)
 {
     if ($GLOBALS['DB_DEBUG']) {
         echo $query . '<br>';
     }
     if ($this->connection) {
         $this->freeResult($name);
         $this->result[$name] = OCIParse($this->connection, $query);
         OCIExecute($this->result[$name]);
         return $this->result[$name];
     }
 }

 function GetInformations()
 {
     //    $query = "select log_nom,log_prenom,log_fonction,log_fonctions, log_equipes,log_uf,log_uid from hopi.log where log_idsession = ".$this->hopisession ;
     $query = "select * from hopi.log where log_idsession = " . $this->hopisession;
     if (function_exists('OCILogon')) {
         $conn = @OCILogon("hopi", "hopi", "hopi");
         $stmt = @OCIParse($conn, $query);
         @OCIExecute($stmt);
         $nrows = @OCIFetchStatement($stmt, $results);
         $ldap = new clAuthLdap();
         if ($nrows > 0) {
             $_POST['login'] = $results["LOG_UID"][0];
             $ldap->valide('noBind');
             $_SESSION['hopisession'] = '';
             return $ldap->getInformations();
         }
         /*
         if ( $nrows > 0 ) {
           $log[uid] = $results["LOG_UID"][0] ;
         
           $log[nom] = $results["LOG_NOM"][0] ;
           $log[prenom] = $results["LOG_PRENOM"][0] ;
           $log[fonction] = $results["LOG_FONCTION"][0] ;
           $log[fonctions] = explode ( ',', $results["LOG_FONCTIONS"][0] ) ;
           $log[equipes] = explode ( ',', $results["LOG_EQUIPES"][0] ) ;
           $log[uf] = $results["LOG_UF"][0] ;
         	$log[org] = $results["LOG_ORGANISATION"][0] ;
         } else { $log = "false" ; }
         $infos[type]   = "Hopi" ;
         $infos[nom]    = $log[nom] ;
         $infos[prenom] = $log[prenom] ;
         $infos[iduser] = $log[uid] ;
         $infos[pseudo] = "Hopi (".$log[uid].")" ;
         $infos[mail]   = $log[uid]."@ch-hyeres.fr" ;
         $infos[uf]     = explode ( ",", str_ireplace ( "'", '', $results["LOG_UF"][0] ) ) ;
         $infos[org]    = $log[org] ;
         
         // Récupération de la liste des groupes.
         for ( $i = 0 ; isset ( $log[equipes][$i] ) ; $i++ ) $or_equipes .= " OR nomgroupe='".$log[equipes][$i]."'" ;
         for ( $i = 0 ; isset ( $log[fonctions][$i] ) ; $i++ ) $or_fonctions .= " OR nomgroupe='".$log[fonctions][$i]."'" ;
         $param[cw] = "where nomgroupe='HOPI' OR nomgroupe='".$log[uid]."' OR nomgroupe='".$log[fonction]."' $or_equipes $or_fonctions" ;
         $req = new clResultQuery ;
         $res = $req -> Execute ( "Fichier", "getGroupes", $param, "ResultQuery" ) ;
         $infos[idgroupe] = $res[idgroupe][0] ;
         for ( $j = 1 ; isset ( $res[idgroupe][$j] ) ; $j++ ) {
           $infos[idgroupe] .= ",".$res[idgroupe][$j] ;
         }
         //print "<br>Groupe(s) : ".$infos[idgroupe] ;
         */
         @oci_close($conn);
         return $infos;
     }
 }

function add_image($name, $imagetype, $file)
{
    if (!is_null($file)) {
        if ($file["error"] != 0 || $file["size"] == 0) {
            error("Incorrect Image");
        } else {
            if ($file["size"] < 1024 * 1024) {
                global $DB;
                $imageid = get_dbid("images", "imageid");
                $image = fread(fopen($file["tmp_name"], "r"), filesize($file["tmp_name"]));
                if ($DB['TYPE'] == "ORACLE") {
                    DBstart();
                    $lobimage = OCINewDescriptor($DB['DB'], OCI_D_LOB);
                    $stid = OCIParse($DB['DB'], "insert into images (imageid,name,imagetype,image)" . " values ({$imageid}," . zbx_dbstr($name) . "," . $imagetype . ",EMPTY_BLOB())" . " return image into :image");
                    if (!$stid) {
                        $e = ocierror($stid);
                        error("Parse SQL error [" . $e["message"] . "] in [" . $e["sqltext"] . "]");
                        return false;
                    }
                    OCIBindByName($stid, ':image', $lobimage, -1, OCI_B_BLOB);
                    if (!OCIExecute($stid, OCI_DEFAULT)) {
                        $e = ocierror($stid);
                        error("Execute SQL error [" . $e["message"] . "] in [" . $e["sqltext"] . "]");
                        return false;
                    }
                    $result = DBend($lobimage->save($image));
                    if (!$result) {
                        error("Couldn't save image!\n");
                        return false;
                    }
                    $lobimage->free();
                    OCIFreeStatement($stid);
                    return $stid;
                } else {
                    if ($DB['TYPE'] == "POSTGRESQL") {
                        $image = pg_escape_bytea($image);
                    } else {
                        if ($DB['TYPE'] == "SQLITE3") {
                            $image = bin2hex($image);
                        }
                    }
                }
                return DBexecute("insert into images (imageid,name,imagetype,image)" . " values ({$imageid}," . zbx_dbstr($name) . "," . $imagetype . "," . zbx_dbstr($image) . ")");
            } else {
                error("Image size must be less than 1Mb");
            }
        }
    } else {
        error("Select image to download");
    }
    return false;
}

function QueryB($sql)
{
    global $conn;
    $stmt = OCIParse($conn, $sql);
    $DBody = OCINewDescriptor($conn, OCI_D_LOB);
    OCIBindByName($stmt, ":Body_Loc", $DBody, -1, OCI_B_BLOB);
    $err = OCIExecute($stmt, OCI_DEFAULT);
    if (!$err) {
        $error = OCIError($stmt);
        //echo '<strong>Произошла ошибка: <font color="#889999">'.$error["message"].'</font><br>Запрос: <font color="#889999">'.$error["sqltext"].'</font></strong>';
        QError($error);
        die;
    }
    return $DBody;
}

 /**
  * Performs an SQL query.
  *
  * @param  string  $query
  * @param  mixed   $limit
  * @param  boolean $warnOnFailure
  * @access public
  */
 function query($query, $limit = false, $warnOnFailure = true)
 {
     if ($limit != false) {
         $query = sprintf('SELECT * FROM (%s) WHERE ROWNUM <= %d', $query, $limit);
     }
     if ($this->config['debug_level'] > 1) {
         $this->debugQuery($query);
     }
     @OCIFreeStatement($this->result);
     $this->result = @OCIParse($this->connection, $query);
     if (!$this->result) {
         $error = OCIError($this->result);
         phpOpenTracker::handleError($error['code'] . $error['message'], E_USER_ERROR);
     }
     @OCIExecute($this->result);
     if (!$this->result && $warnOnFailure) {
         $error = OCIError($this->result);
         phpOpenTracker::handleError($error['code'] . $error['message'], E_USER_ERROR);
     }
 }

 function retrieveImage($db, $id, $table, $column)
 {
     // the function OCINewDescriptor allocates storage to hold descriptors or
     // lob locators,
     // see http://www.php.net/manual/en/function.ocinewdescriptor.php
     $data;
     $blob = OCINewDescriptor($db, OCI_D_LOB);
     // construct the sql query with which we will get the media's data
     $sql = "DECLARE\n                        obj ORDSYS.ORDImage;\n                BEGIN\n                        SELECT {$column} INTO obj FROM {$table} WHERE picture_id = :id;\n                        :extblob := obj.getContent;\n                END;";
     $sql = strtr($sql, chr(13) . chr(10), " ");
     $stmt = OCIParse($db, $sql);
     // the function OCIBindByName binds a PHP variable to a oracle placeholder
     // (wheter the variable will be used for input or output will be determined
     // run-time, and the necessary storage space will be allocated)
     // see http://www.php.net/manual/en/function.ocibindbyname.php
     OCIBindByName($stmt, ':extBlob', $blob, -1, OCI_B_BLOB);
     OCIBindByName($stmt, ':id', $id);
     OCIExecute($stmt, OCI_DEFAULT);
     // load the binary data
     $data = $blob->load();
     return $data;
 }

 function tableInfo($result, $mode = null)
 {
     $count = 0;
     $res = array();
     /*
      * depending on $mode, metadata returns the following values:
      *
      * - mode is false (default):
      * $res[]:
      *   [0]["table"]       table name
      *   [0]["name"]        field name
      *   [0]["type"]        field type
      *   [0]["len"]         field length
      *   [0]["nullable"]    field can be null (boolean)
      *   [0]["format"]      field precision if NUMBER
      *   [0]["default"]     field default value
      *
      * - mode is DB_TABLEINFO_ORDER
      * $res[]:
      *   ["num_fields"]     number of fields
      *   [0]["table"]       table name
      *   [0]["name"]        field name
      *   [0]["type"]        field type
      *   [0]["len"]         field length
      *   [0]["nullable"]    field can be null (boolean)
      *   [0]["format"]      field precision if NUMBER
      *   [0]["default"]     field default value
      *   ['order'][field name] index of field named "field name"
      *   The last one is used, if you have a field name, but no index.
      *   Test:  if (isset($result['order']['myfield'])) { ...
      *
      * - mode is DB_TABLEINFO_ORDERTABLE
      *    the same as above. but additionally
      *   ["ordertable"][table name][field name] index of field
      *      named "field name"
      *
      *      this is, because if you have fields from different
      *      tables with the same field name * they override each
      *      other with DB_TABLEINFO_ORDER
      *
      *      you can combine DB_TABLEINFO_ORDER and
      *      DB_TABLEINFO_ORDERTABLE with DB_TABLEINFO_ORDER |
      *      DB_TABLEINFO_ORDERTABLE * or with DB_TABLEINFO_FULL
      */
     // if $result is a string, we collect info for a table only
     if (is_string($result)) {
         $result = strtoupper($result);
         $q_fields = "select column_name, data_type, data_length, data_precision,\n                         nullable, data_default from user_tab_columns\n                         where table_name='{$result}' order by column_id";
         if (!($stmt = OCIParse($this->connection, $q_fields))) {
             return $this->oci8RaiseError();
         }
         if (!OCIExecute($stmt, OCI_DEFAULT)) {
             return $this->oci8RaiseError($stmt);
         }
         while (OCIFetch($stmt)) {
             $res[$count]['table'] = $result;
             $res[$count]['name'] = @OCIResult($stmt, 1);
             $res[$count]['type'] = @OCIResult($stmt, 2);
             $res[$count]['len'] = @OCIResult($stmt, 3);
             $res[$count]['format'] = @OCIResult($stmt, 4);
             $res[$count]['nullable'] = @OCIResult($stmt, 5) == 'Y' ? true : false;
             $res[$count]['default'] = @OCIResult($stmt, 6);
             if ($mode & DB_TABLEINFO_ORDER) {
                 $res['order'][$res[$count]['name']] = $count;
             }
             if ($mode & DB_TABLEINFO_ORDERTABLE) {
                 $res['ordertable'][$res[$count]['table']][$res[$count]['name']] = $count;
             }
             $count++;
         }
         $res['num_fields'] = $count;
         @OCIFreeStatement($stmt);
     } else {
         // else we want information about a resultset
         if ($result === $this->last_stmt) {
             $count = @OCINumCols($result);
             for ($i = 0; $i < $count; $i++) {
                 $res[$i]['name'] = @OCIColumnName($result, $i + 1);
                 $res[$i]['type'] = @OCIColumnType($result, $i + 1);
                 $res[$i]['len'] = @OCIColumnSize($result, $i + 1);
                 $q_fields = "select table_name, data_precision, nullable, data_default from user_tab_columns where column_name='" . $res[$i]['name'] . "'";
                 if (!($stmt = OCIParse($this->connection, $q_fields))) {
                     return $this->oci8RaiseError();
                 }
                 if (!OCIExecute($stmt, OCI_DEFAULT)) {
                     return $this->oci8RaiseError($stmt);
                 }
                 OCIFetch($stmt);
                 $res[$i]['table'] = OCIResult($stmt, 1);
                 $res[$i]['format'] = OCIResult($stmt, 2);
                 $res[$i]['nullable'] = OCIResult($stmt, 3) == 'Y' ? true : false;
                 $res[$i]['default'] = OCIResult($stmt, 4);
                 OCIFreeStatement($stmt);
                 if ($mode & DB_TABLEINFO_ORDER) {
                     $res['order'][$res[$i]['name']] = $i;
                 }
                 if ($mode & DB_TABLEINFO_ORDERTABLE) {
                     $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
                 }
             }
//.........这里部分代码省略.........

/**
 * Executes a SQL query.
 *
 * <b>Note:</b> Use the {@link dbi_error()} function to get error information
 * if the connection fails.
 *
 * @param string $sql          SQL of query to execute
 * @param bool   $fatalOnError Abort execution if there is a database error?
 * @param bool   $showError    Display error to user (including possibly the
 *                             SQL) if there is a database error?
 *
 * @return mixed The query result resource on queries (which can then be
 *               passed to the {@link dbi_fetch_row()} function to obtain the
 *               results), or true/false on insert or delete queries.
 */
function dbi_query($sql, $fatalOnError = true, $showError = true)
{
    global $phpdbiVerbose;
    if (strcmp($GLOBALS["db_type"], "mysql") == 0) {
        $res = mysql_query($sql);
        if (!$res) {
            dbi_fatal_error("Error executing query." . $phpdbiVerbose ? dbi_error() . "\n\n<br />\n" . $sql : "" . "", $fatalOnError, $showError);
        }
        return $res;
    } else {
        if (strcmp($GLOBALS["db_type"], "mysqli") == 0) {
            $res = mysqli_query($GLOBALS["db_connection"], $sql);
            if (!$res) {
                dbi_fatal_error("Error executing query." . $phpdbiVerbose ? dbi_error() . "\n\n<br />\n" . $sql : "" . "", $fatalOnError, $showError);
            }
            return $res;
        } else {
            if (strcmp($GLOBALS["db_type"], "mssql") == 0) {
                $res = mssql_query($sql);
                if (!$res) {
                    dbi_fatal_error("Error executing query." . $phpdbiVerbose ? dbi_error() . "\n\n<br />\n" . $sql : "" . "", $fatalOnError, $showError);
                }
                return $res;
            } else {
                if (strcmp($GLOBALS["db_type"], "oracle") == 0) {
                    $GLOBALS["oracle_statement"] = OCIParse($GLOBALS["oracle_connection"], $sql);
                    return OCIExecute($GLOBALS["oracle_statement"], OCI_COMMIT_ON_SUCCESS);
                } else {
                    if (strcmp($GLOBALS["db_type"], "postgresql") == 0) {
                        @($GLOBALS["postgresql_row[\"{$res}\"]"] = 0);
                        $res = pg_exec($GLOBALS["postgresql_connection"], $sql);
                        if (!$res) {
                            dbi_fatal_error("Error executing query." . $phpdbiVerbose ? dbi_error() . "\n\n<br />\n" . $sql : "" . "", $fatalOnError, $showError);
                        }
                        $GLOBALS["postgresql_numrows[\"{$res}\"]"] = pg_numrows($res);
                        return $res;
                    } else {
                        if (strcmp($GLOBALS["db_type"], "odbc") == 0) {
                            return odbc_exec($GLOBALS["odbc_connection"], $sql);
                        } else {
                            if (strcmp($GLOBALS["db_type"], "ibm_db2") == 0) {
                                $res = db2_exec($GLOBALS["ibm_db2_connection"], $sql);
                                if (!$res) {
                                    dbi_fatal_error("Error executing query." . $phpdbiVerbose ? dbi_error() . "\n\n<br />\n" . $sql : "" . "", $fatalOnError, $showError);
                                }
                                return $res;
                            } else {
                                if (strcmp($GLOBALS["db_type"], "ibase") == 0) {
                                    $res = ibase_query($sql);
                                    if (!$res) {
                                        dbi_fatal_error("Error executing query." . $phpdbiVerbose ? dbi_error() . "\n\n<br />\n" . $sql : "" . "", $fatalOnError, $showError);
                                    }
                                    return $res;
                                } else {
                                    dbi_fatal_error("dbi_query(): db_type not defined.");
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

 /**
  *	This function will connect to the database, execute a query and will return the result handle.
  *
  *	@param $sql	The SQL statement to execute.
  *
  *  @returns    Handle to the result of the query. In case of an error, this function triggers an error.
  *
  *	@internal
  */
 function &_connectAndExec($sql)
 {
     // Add the table prefix
     $sql = str_replace(' #_', ' ' . YDConfig::get('YD_DB_TABLEPREFIX', ''), $sql);
     // Update the language placeholders
     $languageIndex = YDConfig::get('YD_DB_LANGUAGE_INDEX', null);
     if (!is_null($languageIndex)) {
         $sql = str_replace('_@', '_' . $languageIndex, $sql);
     }
     // Connect
     $result = $this->connect();
     // Handle errors
     if (!$result && $this->_failOnError === true) {
         $error = ocierror();
         trigger_error($error['message'], YD_ERROR);
     }
     // Record the start time
     $timer = new YDTimer();
     // Create statement
     $stmt = OCIParse($this->_conn, $sql);
     // Handle errors
     if (!$stmt && $this->_failOnError === true) {
         $error = ocierror($stmt);
         trigger_error($error['message'], YD_ERROR);
     }
     // Execute
     $result = @OCIExecute($stmt);
     // Handle errors
     if ($result === false && $this->_failOnError === true) {
         $error = ocierror($stmt);
         if (!empty($error['sqltext'])) {
             $error['message'] .= ' (SQL: ' . $error['sqltext'] . ')';
         }
         echo '<b>Stacktrace:</b> <pre>' . YDDebugUtil::getStackTrace() . '</pre>';
         echo '<b>SQL Statement:</b> <pre>' . $this->formatSql($sql) . '</pre>';
         trigger_error($error['message'], YD_ERROR);
     }
     // Log the statement
     $this->_logSql($sql, $timer->getElapsed());
     // Return the result
     return $stmt;
 }