PHP odbc_prepare函数代码示例

/**
 * PHP Template.
 */
function updateSF($tableName, $rowID, $sfID)
{
    $odbc = odbcConnect();
    $stmt = odbc_prepare($odbc, "INSERT into SalesForceUpdateQueue (creationDate, mysqlTableName, mysqlRowID, salesForceID) VALUES(CURRENT_TIMESTAMP(), ?, ?, ?)");
    $rs = odbc_execute($stmt, array($tableName, $rowID, $sfID));
    odbc_close($odbc);
}

 protected function _exec($sql, $params = array())
 {
     $this->_stmt = odbc_prepare($this->_con, $sql);
     if ($this->_stmt === false) {
         $this->raiseError($this->_stmt, $params);
     }
     $res = odbc_execute($this->_stmt, $params);
     if ($res === false) {
         $this->raiseError($sql, $params);
     }
     return $res;
 }

 /**
  * Prepare a query, execute it and return an ODBC result identifier
  * @param string $query
  * @param array $params
  * @return bool|resource
  * @throws Exception
  */
 protected function executePreparedStatement($query, $params)
 {
     $res = odbc_prepare($this->_lnk, $query);
     if (!$res) {
         $error = odbc_errormsg($this->_lnk);
         $this->log('Prepare failed: ' . $error);
         throw new Exception('Preparing query failed ' . $error, self::PREPARE_FAILED);
     }
     $res = odbc_execute($res, $params);
     if (!$res) {
         $error = odbc_errormsg($this->_lnk);
         $this->log('Prepared query execution failed: ' . $error);
         throw new Exception('Executing prepared query failed ' . $error, self::QUERY_FAILED);
     }
     return $res;
 }

 /**
  * Executes the supplied SQL statement and returns
  * the result of the call.
  * 
  * @access  public
  *  
  * @param   string  SQL to execute
  */
 function exec()
 {
     if (func_num_args() > 1) {
         $args = func_get_args();
         $sql = $args[0];
         unset($args[0]);
         // remove the sql
         $args = array_values($args);
         // and reset the array index
     } else {
         $sql = func_get_arg(0);
     }
     $this->ensureConnection();
     if (isset($args)) {
         $result = odbc_prepare($this->connection, $sql);
         if (!odbc_execute($result, $args)) {
             throw new Exception(odbc_errormsg($this->connection));
         }
         return odbc_num_rows($result);
     } else {
         return odbc_exec($this->connection, $sql);
     }
 }

 /**
  * Internal function to call native ODBC prepare/execute functions.
  */
 protected function _execute($sql, $params, $fetchmode, $isupdate)
 {
     // Set any params passed directly
     if ($params) {
         for ($i = 0, $cnt = count($params); $i < $cnt; $i++) {
             $this->set($i + 1, $params[$i]);
         }
     }
     // Trim surrounding quotes added from default set methods.
     // Exception: for LOB-based parameters, odbc_execute() will
     // accept a filename surrounded by single-quotes.
     foreach ($this->boundInVars as $idx => $var) {
         if ($var instanceof Lob) {
             $file = $isupdate ? $var->getInputFile() : $var->getOutputFile();
             $this->boundInVars[$idx] = "'{$file}'";
         } else {
             if (is_string($var)) {
                 $this->boundInVars[$idx] = trim($var, "\"\\'");
             }
         }
     }
     if ($this->resultSet) {
         $this->resultSet->close();
         $this->resultSet = null;
     }
     $this->updateCount = null;
     $stmt = @odbc_prepare($this->conn->getResource(), $sql);
     if ($stmt === FALSE) {
         throw new SQLException('Could not prepare query', $this->conn->nativeError(), $sql);
     }
     $ret = @odbc_execute($stmt, $this->boundInVars);
     if ($ret === FALSE) {
         @odbc_free_result($stmt);
         throw new SQLException('Could not execute query', $this->conn->nativeError(), $sql);
     }
     return $this->conn->createResultSet(new ODBCResultResource($stmt), $fetchmode);
 }

 function get_report(client $client, $table_name, $show, $rownum)
 {
     if ($table_name == null) {
         return "Bad table name.";
     }
     //TODO check table_name is one word
     //compile query
     $colnames = odbc_exec($client->get_connection(), "SELECT column_name, data_type, data_length FROM ALL_TAB_COLUMNS WHERE table_name = '" . strtoupper($table_name) . "';");
     if ($colnames === false) {
         return "Unable to get table fields.";
     }
     $query = "SELECT ";
     $i = 0;
     while (odbc_fetch_row($colnames)) {
         if (isset($show) && isset($show[$i]) && $show[$i] == true) {
             if ($query != "SELECT ") {
                 $query .= ", ";
             }
             $query .= odbc_result($colnames, 1);
         }
         $i += 1;
     }
     $query .= " FROM " . $table_name . " WHERE rownum <= ?;";
     //prepare statement
     $statement = odbc_prepare($client->get_connection(), $query);
     if ($statement === false) {
         return $query . "\n\n" . get_odbc_error();
     }
     $items = array();
     $items[] = (int) $rownum;
     $result = odbc_execute($statement, $items);
     if ($result === false) {
         return $query . "\n\n" . get_odbc_error();
     }
     return $statement;
 }

function deleteServer($id)
{
    $odbc = odbcConnect();
    $stmt = odbc_prepare($odbc, "DELETE FROM servers WHERE id = ?");
    $rs = odbc_execute($stmt, array($id));
    odbc_close($odbc);
    return $rs;
}

 public function prepare($query, $logComponent)
 {
     Timer::start($logComponent . '::odbc_prepare');
     $result = @odbc_prepare($this->con, $query);
     Timer::stop($logComponent . '::odbc_prepare');
     if (false === $result) {
         $errornr = odbc_error($this->con);
         $err = odbc_errormsg($this->con);
         //echo $err.$errornr;die;
         switch ($errornr) {
             case 37000:
                 Logger::warn($logComponent . "::odbc_prepare failed: query length = " . strlen($query) . "\n" . $err);
                 break;
             case '08S01':
                 Logger::warn($logComponent . "::odbc_prepare: lost connection to server going into loop" . $err);
                 Logger::warn("Waiting at: ***********\n" . substr($query, 0, self::cutstring));
                 Logger::warn("Previous was:**********\n" . $this->previous);
                 do {
                     Logger::warn('Currently looping last odbc_prepare, waiting ' . self::wait . ' and retrying');
                     sleep(self::wait);
                     $this->connect(true);
                     $result = @odbc_prepare($this->con, $query);
                     $errornr = odbc_error($this->con);
                 } while (false === $result && $errornr == '08S01');
                 break;
             case 40001:
             case 'SR172':
                 Logger::warn($logComponent . "::odbc_prepare: Transaction deadlocked, going into loop" . $err);
                 Logger::warn("Waiting at: ***********\n" . substr($query, 0, self::cutstring));
                 Logger::warn("Previous was:**********\n" . $this->previous);
                 do {
                     Logger::warn('Currently looping last odbc_prepare, waiting ' . self::wait . ' and retrying');
                     sleep(self::wait);
                     $this->connect(true);
                     $result = @odbc_prepare($this->con, $query);
                     $errornr = odbc_error($this->con);
                 } while (false === $result && $errornr == 'SR172');
                 break;
             default:
                 Logger::warn($logComponent . "::odbc_prepare failed: \n" . $query . "\nnumber: " . $errornr . "\nerror: " . $err);
         }
     } else {
         Logger::debug($logComponent . ":: successfully prepared  ({$query}): ");
     }
     $this->setPrevious($query);
     return $result;
 }

}
if (!odbc_autocommit($conn_id, FALSE)) {
    exit("Failed to turn off AutoCommit mode\n");
}
if (!odbc_exec($conn_id, "DROP TABLE tsttbl IF EXISTS")) {
    exit("DROP command failed\n");
}
if (!odbc_exec($conn_id, "CREATE TABLE tsttbl(\n    id BIGINT generated BY DEFAULT AS IDENTITY,\n    vc VARCHAR(20),\n    entrytime TIMESTAMP DEFAULT current_timestamp NOT NULL\n)")) {
    exit("CREATE TABLE command failed\n");
}
# First do a non-parameterized insert
if (!odbc_exec($conn_id, "INSERT INTO tsttbl(id, vc) VALUES(1, 'one')")) {
    exit("Insertion of first row failed\n");
}
# Now parameterized inserts
$stmt = odbc_prepare($conn_id, "INSERT INTO tsttbl(id, vc) VALUES(?, ?)");
if (!$stmt) {
    exit("Preparation of INSERT statement failed \n");
}
# With (default) debug mode, the following statements will generate
# annoying "cursor updatability" warnings.
$rv = odbc_execute($stmt, array(2, 'two'));
if ($rv != 1) {
    exit("2nd Insertion failed with  value {$rv}\n");
}
$rv = odbc_execute($stmt, array(3, 'three'));
if ($rv != 1) {
    exit("3rd Insertion failed with  value {$rv}\n");
}
$rv = odbc_execute($stmt, array(4, 'four'));
if ($rv != 1) {

 function _query($sql, $inputarr = false)
 {
     global $php_errormsg;
     $php_errormsg = '';
     $this->_error = '';
     if ($inputarr) {
         if (is_resource($sql)) {
             $stmtid = $sql;
         } else {
             $stmtid = odbc_prepare($this->_connectionID, $sql);
         }
         if ($stmtid == false) {
             $this->_errorMsg = $php_errormsg;
             return false;
         }
         //print_r($inputarr);
         if (!odbc_execute($stmtid, $inputarr)) {
             @odbc_free_result($stmtid);
             return false;
         }
     } else {
         $stmtid = odbc_exec($this->_connectionID, $sql);
     }
     if ($stmtid) {
         odbc_binmode($stmtid, $this->binmode);
         odbc_longreadlen($stmtid, $this->maxblobsize);
     }
     $this->_errorMsg = $php_errormsg;
     return $stmtid;
 }

 /**
  * Compile Bindings
  *
  * @param	string	$sql	SQL statement
  * @param	array	$binds	An array of values to bind
  * @return	string
  */
 public function compile_binds($sql, $binds)
 {
     if (empty($binds) or empty($this->bind_marker) or strpos($sql, $this->bind_marker) === FALSE) {
         return $sql;
     } elseif (!is_array($binds)) {
         $binds = array($binds);
         $bind_count = 1;
     } else {
         // Make sure we're using numeric keys
         $binds = array_values($binds);
         $bind_count = count($binds);
     }
     // We'll need the marker length later
     $ml = strlen($this->bind_marker);
     // Make sure not to replace a chunk inside a string that happens to match the bind marker
     if ($c = preg_match_all("/'[^']*'|\"[^\"]*\"/i", $sql, $matches)) {
         $c = preg_match_all('/' . preg_quote($this->bind_marker, '/') . '/i', str_replace($matches[0], str_replace($this->bind_marker, str_repeat(' ', $ml), $matches[0]), $sql, $c), $matches, PREG_OFFSET_CAPTURE);
         // Bind values' count must match the count of markers in the query
         if ($bind_count !== $c) {
             return $sql;
         }
     } elseif (($c = preg_match_all('/' . preg_quote($this->bind_marker, '/') . '/i', $sql, $matches, PREG_OFFSET_CAPTURE)) !== $bind_count) {
         return $sql;
     }
     if ($this->bind_marker !== '?') {
         do {
             $c--;
             $sql = substr_replace($sql, '?', $matches[0][$c][1], $ml);
         } while ($c !== 0);
     }
     if (FALSE !== ($this->odbc_result = odbc_prepare($this->conn_id, $sql))) {
         $this->binds = array_values($binds);
     }
     return $sql;
 }

 /**
  * Sets up a prepared statement
  * 
  * @internal
  * 
  * @param  fDatabase $database            The database object this result set was created from
  * @param  string    $query               MSSQL only: the character set to transcode from since MSSQL doesn't do UTF-8
  * @param  array     $placeholders        The data type placeholders
  * @param  string    $untranslated_query  If the SQL was translated - only relevant for Oracle
  * @return fResult
  */
 public function __construct($database, $query, $placeholders, $untranslated_sql)
 {
     if (!$database instanceof fDatabase) {
         throw new fProgrammerException('The database object provided does not appear to be a descendant of fDatabase');
     }
     $this->database = $database;
     $this->placeholders = $placeholders;
     $this->sql = vsprintf($query, $placeholders);
     $this->untranslated_sql = $untranslated_sql;
     $extension = $this->database->getExtension();
     switch ($extension) {
         // These database extensions don't have prepared statements
         case 'mssql':
         case 'mysql':
         case 'sqlite':
             break;
         case 'oci8':
             $named_placeholders = array();
             for ($i = 1; $i <= sizeof($placeholders); $i++) {
                 $named_placeholders[] = ':p' . $i;
             }
             $query = vsprintf($query, $named_placeholders);
             break;
         case 'ibm_db2':
         case 'mysqli':
         case 'odbc':
         case 'pdo':
         case 'sqlsrv':
             $question_marks = array();
             if (sizeof($placeholders)) {
                 $question_marks = array_fill(0, sizeof($placeholders), '?');
             }
             $query = vsprintf($query, $question_marks);
             break;
         case 'pgsql':
             $dollar_placeholders = array();
             for ($i = 1; $i <= sizeof($placeholders); $i++) {
                 $dollar_placeholders[] = '$' . $i;
             }
             $query = vsprintf($query, $dollar_placeholders);
             break;
     }
     $connection = $this->database->getConnection();
     $old_level = error_reporting(error_reporting() & ~E_WARNING);
     switch ($extension) {
         // These database extensions don't have prepared statements
         case 'mssql':
         case 'mysql':
         case 'sqlite':
             $statement = $query;
             break;
         case 'ibm_db2':
             $statement = db2_prepare($connection, $query, array('cursor' => DB2_FORWARD_ONLY));
             break;
         case 'mysqli':
             $statement = mysqli_prepare($connection, $query);
             break;
         case 'oci8':
             $statement = oci_parse($connection, $query);
             break;
         case 'odbc':
             $statement = odbc_prepare($connection, $query);
             break;
         case 'pdo':
             $statement = $connection->prepare($query);
             break;
         case 'pgsql':
             static $statement_number = 0;
             $statement_number++;
             $this->identifier = 'fstmt' . $statement_number;
             $statement = pg_prepare($connection, $this->identifier, $query);
             break;
         case 'sqlsrv':
             $params = array();
             for ($i = 0; $i < sizeof($placeholders); $i++) {
                 $this->bound_params[$i] = NULL;
                 $params[$i] =& $this->bound_params[$i];
             }
             $statement = sqlsrv_prepare($connection, $query, $params);
             break;
     }
     error_reporting($old_level);
     if (!$statement) {
         switch ($extension) {
             case 'ibm_db2':
                 $message = db2_stmt_errormsg($statement);
                 break;
             case 'mysqli':
                 $message = mysqli_error($connection);
//.........这里部分代码省略.........

 function _query($sql, $inputarr = false)
 {
     global $php_errormsg;
     if (isset($php_errormsg)) {
         $php_errormsg = '';
     }
     $this->_error = '';
     if ($inputarr) {
         if (is_array($sql)) {
             $stmtid = $sql[1];
         } else {
             $stmtid = odbc_prepare($this->_connectionID, $sql);
             if ($stmtid == false) {
                 $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : '';
                 return false;
             }
         }
         if (!odbc_execute($stmtid, $inputarr)) {
             //@odbc_free_result($stmtid);
             if ($this->_haserrorfunctions) {
                 $this->_errorMsg = odbc_errormsg();
                 $this->_errorCode = odbc_error();
             }
             if ($this->_errorCode == '00000') {
                 // MS SQL Server sometimes returns this in combination with the FreeTDS
                 $this->_errorMsg = '';
                 // driver and UnixODBC under Linux. This fixes the bogus "error"
                 $this->_errorCode = 0;
                 // <karsten@typo3.org>
                 return true;
             }
             return false;
         }
     } else {
         if (is_array($sql)) {
             $stmtid = $sql[1];
             if (!odbc_execute($stmtid)) {
                 //@odbc_free_result($stmtid);
                 if ($this->_haserrorfunctions) {
                     $this->_errorMsg = odbc_errormsg();
                     $this->_errorCode = odbc_error();
                 }
                 if ($this->_errorCode == '00000') {
                     // MS SQL Server sometimes returns this in combination with the FreeTDS
                     $this->_errorMsg = '';
                     // driver and UnixODBC under Linux. This fixes the bogus "error"
                     $this->_errorCode = 0;
                     // <karsten@typo3.org>
                     return true;
                 }
                 return false;
             }
         } else {
             $stmtid = odbc_exec($this->_connectionID, $sql);
         }
     }
     $this->_lastAffectedRows = 0;
     if ($stmtid) {
         if (@odbc_num_fields($stmtid) == 0) {
             $this->_lastAffectedRows = odbc_num_rows($stmtid);
             $stmtid = true;
         } else {
             $this->_lastAffectedRows = 0;
             odbc_binmode($stmtid, $this->binmode);
             odbc_longreadlen($stmtid, $this->maxblobsize);
         }
         if ($this->_haserrorfunctions) {
             $this->_errorMsg = '';
             $this->_errorCode = 0;
         } else {
             $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : '';
         }
     } else {
         if ($this->_haserrorfunctions) {
             $this->_errorMsg = odbc_errormsg();
             $this->_errorCode = odbc_error();
         } else {
             $this->_errorMsg = isset($php_errormsg) ? $php_errormsg : '';
         }
     }
     return $stmtid;
 }

 /**
  * Execute an sql query
  */
 public function query($query, array $params = null)
 {
     # If the next query should be cached then run the cache function instead
     if ($this->cacheNext) {
         $this->cacheNext = false;
         return $this->cache($query, $params);
     }
     # Ensure we have a connection to run this query on
     $this->connect();
     $this->query = $query;
     $this->params = null;
     $this->preparedQuery = false;
     if (is_array($params)) {
         $this->params = $params;
     }
     $this->quoteChars($query);
     $this->functions($query);
     $this->limit($query);
     $this->tableNames($query);
     $this->namedParams($query, $params);
     $this->paramArrays($query, $params);
     $this->convertNulls($params);
     $preparedQuery = $this->prepareQuery($query, $params);
     $this->preparedQuery = $preparedQuery;
     if ($this->output) {
         if ($this->htmlMode) {
             echo "<pre>";
         }
         echo $preparedQuery;
         if ($this->htmlMode) {
             echo "<hr>";
         } else {
             echo "\n";
         }
     }
     switch ($this->mode) {
         case "mysql":
             if (!($result = $this->server->query($preparedQuery))) {
                 $this->error();
             }
             break;
         case "postgres":
         case "redshift":
             $tmpQuery = $query;
             $query = "";
             $noParams = false;
             if ($this->mode == "redshift" && count($params) > 32767) {
                 $noParams = true;
             }
             $i = 1;
             reset($params);
             while ($pos = strpos($tmpQuery, "?")) {
                 if ($noParams) {
                     $query .= substr($tmpQuery, 0, $pos) . "'" . pg_escape_string(current($params)) . "'";
                     next($params);
                 } else {
                     $query .= substr($tmpQuery, 0, $pos) . "\$" . $i++;
                 }
                 $tmpQuery = substr($tmpQuery, $pos + 1);
             }
             $query .= $tmpQuery;
             $params = Helper::toArray($params);
             if (!($result = pg_query_params($this->server, $query, $params))) {
                 $this->error();
             }
             break;
         case "odbc":
             if (!($result = odbc_prepare($this->server, $query))) {
                 $this->error();
             }
             $params = Helper::toArray($params);
             if (!odbc_execute($result, $params)) {
                 $this->error();
             }
             break;
         case "sqlite":
             if (!is_array($params)) {
                 if (!($result = $this->server->query($preparedQuery))) {
                     $this->error();
                 }
                 # If we have some parameters then we must convert them to the sqlite format
             } else {
                 $newQuery = "";
                 foreach ($params as $key => $val) {
                     $pos = strpos($query, "?");
                     $newQuery .= substr($query, 0, $pos);
                     $query = substr($query, $pos + 1);
                     $newQuery .= ":var" . $key;
                 }
                 $newQuery .= $query;
                 if (!($result = $this->server->prepare($newQuery))) {
                     $this->error();
                 }
                 foreach ($params as $key => $val) {
                     switch (gettype($val)) {
                         case "boolean":
                         case "integer":
//.........这里部分代码省略.........

 public function fetch($sql, $bind, callable $callback)
 {
     $stmt = odbc_prepare($this->connection, $sql);
     odbc_execute($stmt, $this->repairBinding($bind));
     while ($row = odbc_fetch_array($stmt)) {
         $callback($row);
     }
     odbc_free_result($stmt);
 }