PHP sqlsrv_prepare函数代码示例

 /**
  * Returns a prepared statement which can be executed multiple times
  * @param string $sql
  * @param array  $params Values to bind to placeholders in the query string
  * @return Statement
  * @throws SqlException if an error occurs
  */
 public function prepare($sql, array $params = [])
 {
     if (!($stmt = sqlsrv_prepare($this->connection, $sql, $params))) {
         throw new SqlException('Query failed', sqlsrv_errors(), $sql, $params);
     }
     return new Statement($stmt, true, $sql, $params);
 }

function SaveLecturerMarks($marks)
{
    $status = array();
    include_once 'db_Connection.php';
    $conn = sqlsrv_connect($serverName, $connectionInfo);
    if ($conn) {
        $login = "0";
        $subjectid = 0;
        $subjtype = 0;
        $lectid = 0;
        $qid = 0;
        $mark = 0;
        $ldescription = "0";
        $sqlstr = " insert into [dbo].[MarkLecturer] ([Login],[subjid],[subjtype],[lectid],[qid],[mark],[ldescription]) " . " values (?,?,?,?,?,?,?) ";
        $params = array(&$login, &$subjectid, &$subjtype, &$lectid, &$qid, &$mark, &$ldescription);
        $stmt = sqlsrv_prepare($conn, $sqlstr, $params);
        if ($stmt) {
            foreach ($marks as &$data) {
                $login = $data->login;
                $subjectid = $data->subjectid;
                $subjtype = $data->subjtype;
                $lectid = $data->lectid;
                $qid = $data->qid;
                $mark = $data->mark;
                $ldescription = $data->description;
                if (!sqlsrv_execute($stmt)) {
                    array_push($status, "not inserted-L" . $login . "-S" . $subjectid . "-T" . $subjtype . "-L" . $lectid . "-Q" . $qid);
                }
            }
        }
        sqlsrv_close($conn);
    }
    return $status;
}

function processWalking($results, $fileRow)
{
    global $csvData;
    global $sqlDB;
    global $plat;
    //this is where I'm going to do things
    foreach ($results as $result) {
        $startDate = $result['DateBegan'];
        $endDate = $result['DateStopped'];
        $diff = date_diff($startDate, $endDate);
        $days = $diff->format("%a");
        $currentDate = $result['DateBegan'];
        $formattedDate = date_format($currentDate, 'm/d/y');
        $tempData = array();
        while ($currentDate <= $endDate) {
            $dayDate = $formattedDate . ' ' . "00:00:00";
            $endDay = $formattedDate . ' ' . "23:59:59";
            $querry = "SELECT TOP 1 RecID FROM vPhoneData1\n\t\t\t\tWHERE (TaskSetName = '1 PING' or TaskSetName =\n\t\t\t\t '3 REPORT CGI_OR_H2RL') AND LocalTimetag1 > ? and\n\t\t\t\t LocalTimetag1 < ? and cast(FORMDATA as varchar(max)) like\n\t\t\t\t '%" . $plat . "%'";
            $stmt = sqlsrv_prepare($sqlDB, $querry, array(&$dayDate, &$endDay));
            sqlsrv_execute($stmt);
            $fetched = sqlsrv_fetch_array($stmt);
            if (!empty($fetched)) {
                $formattedDate = date_format($currentDate, 'm/d/y');
                $fileRow['Survey Dt'] = $formattedDate;
                $fileRow['Completed By'] = $result['TechName'];
                $fileRow['Instrument'] = $result['Instrument'];
                $fileRow['Rate'] = $result['RateCode'];
                $fileRow['Survey Method'] = 'WALK';
                $tempData[] = $fileRow;
            }
            $currentDate = date_add($currentDate, date_interval_create_from_date_string('1 days'));
            $formattedDate = date_format($currentDate, 'm/d/y');
            $fetched = null;
        }
        if (!empty($tempData)) {
            $daycount = count($tempData);
            if ($result[6] > 0) {
                $dailyTotal = floor($result[6] / $daycount);
                $firstTotal = $dailyTotal + $result[6] % $daycount;
                $isFirst = TRUE;
            } else {
                $dailyTotal = 0;
            }
            foreach ($tempData as $tempRow) {
                if ($isFirst === TRUE) {
                    $tempRow['Quantity'] = $firstTotal;
                    $isFirst = FALSE;
                } elseif ($dailyTotal > 0) {
                    $tempRow['Quantity'] = $dailyTotal;
                } else {
                    $tempRow['Quantity'] = 0;
                }
                if ($tempRow['Quantity'] > 0) {
                    $csvData[] = $tempRow;
                }
            }
        }
    }
}

 /**
  * Prepares statement handle
  *
  * @param string $sql
  * @return void
  * @throws \Zend\DB\Statement\SQLSRV\Exception
  */
 protected function _prepare($sql)
 {
     $connection = $this->_adapter->getConnection();
     $this->_stmt = sqlsrv_prepare($connection, $sql);
     if (!$this->_stmt) {
         throw new Exception(sqlsrv_errors());
     }
     $this->_originalSQL = $sql;
 }

 /**
  * Prepares statement handle
  *
  * @param string $sql
  * @return void
  * @throws Zend_Db_Statement_Sqlsrv_Exception
  */
 protected function _prepare($sql)
 {
     $connection = $this->_adapter->getConnection();
     $this->_stmt = sqlsrv_prepare($connection, $sql);
     if (!$this->_stmt) {
         require_once 'include/Zend/Db/Statement/Sqlsrv/Exception.php';
         throw new Zend_Db_Statement_Sqlsrv_Exception(sqlsrv_errors());
     }
     $this->_originalSQL = $sql;
 }

 public static function prepare($conn, $sqlElement, $params, $bind = true)
 {
     if ($conn && strlen($sqlElement) > 0) {
         if (is_array($params) && count($params) > 0) {
             for ($i = 0; $i < count($params); $i++) {
                 $aprm[$i] =& $params[$i];
             }
         } else {
             $aprm = $params;
         }
         $sql = sqlsrv_prepare($conn, (string) $sqlElement, $aprm);
         if (!$sql) {
             print_r(sqlsrv_errors(), true);
         }
         return $sql;
     }
     return false;
 }

 /**
  * (non-PHPdoc)
  * @see PreparedStatement::preparePreparedStatement()
  */
 public function preparePreparedStatement($msg = '')
 {
     if (empty($this->parsedSQL)) {
         $this->DBM->registerError($msg, "Empty SQL query");
         return false;
     }
     $GLOBALS['log']->info('QueryPrepare: ' . $this->parsedSQL);
     $num_args = count($this->fieldDefs);
     $this->bound_vars = array_fill(0, $num_args, null);
     $params = array();
     for ($i = 0; $i < $num_args; $i++) {
         $params[$i] =& $this->bound_vars[$i];
     }
     $this->stmt = sqlsrv_prepare($this->dblink, $this->parsedSQL, $params);
     if ($this->DBM->checkError(" QueryPrepare Failed: {$msg} for sql: {$this->parsedSQL} ::") || !$this->stmt) {
         return false;
     }
     return $this;
 }

							</tr>
							<tr>
								<?php 
//telefoon gebruiker
$sqlTel = "SELECT * FROM Gebruikerstelefoon where Gebruiker='{$user}'";
$paramsTel = array();
$optionsTel = array("Scrollable" => SQLSRV_CURSOR_KEYSET);
$stmtTel = sqlsrv_query($conn, $sqlTel, $paramsTel, $optionsTel);
$rowCountTel = sqlsrv_num_rows($stmtTel);
$actual_link = "http://{$_SERVER['HTTP_HOST']}{$_SERVER['REQUEST_URI']}";
$getFirstEnd = end(explode('?', $actual_link));
$getSecondEnd = substr($getFirstEnd, 0, 6);
if ($getSecondEnd == 'remove') {
    $end = end(explode('?remove=', $actual_link));
    $sqlDelete = "DELETE FROM Gebruikerstelefoon WHERE Telefoonnummer = '{$end}' AND Gebruiker = '{$user}'";
    $stmtDelete = sqlsrv_prepare($conn, $sqlDelete);
    sqlsrv_execute($stmtDelete);
}
while ($rowTel = sqlsrv_fetch_array($stmtTel, SQLSRV_FETCH_ASSOC)) {
    $Teltel = $rowTel['Telefoonnummer'];
    echo '<tr> </td><td><td><input type="text" name="telefoon" placeholder="' . $Teltel . '" disabled><a style="float: right;" href="?remove=' . $Teltel . '">Verwijder&nbsp;</a></td></tr>';
    //$counter ++;
    //if ($counter == $rowCountTel || $counter <= 1){
    //}
}
echo '<tr><td> </td><td><a class="right" href="addPhoneNumber.php">Telefoonnummer toevoegen</a></td></tr>';
?>
								
							</tr>
						</table>
						</div>

 /**
  * Executes a prepared statement
  *
  * If the prepared statement included parameter markers, you must either:
  * call PDOStatement->bindParam() to bind PHP variables to the parameter markers:
  * bound variables pass their value as input and receive the output value,
  * if any, of their associated parameter markers or pass an array of input-only
  * parameter values
  *
  *
  * @param array $params             An array of values with as many elements as there are
  *                                  bound parameters in the SQL statement being executed.
  * @return boolean                  Returns TRUE on success or FALSE on failure.
  */
 public function execute($params = null)
 {
     // bind values
     if (is_array($params)) {
         foreach ($params as $var => $value) {
             $this->bindValue($var + 1, $value);
         }
     }
     // prepare statement
     $this->statement = sqlsrv_prepare($this->connection, $query, $this->bindParams);
     if ($this->statement == false) {
         $this->handleError();
     }
     $result = sqlsrv_execute($this->statement);
     if ($result === false) {
         $this->handleError();
         return false;
     }
     return true;
 }

 public function p_query($sql, $params)
 {
     $prep_params = array();
     for ($x = 0; $x < count($params); $x++) {
         $prep_params[] =& $params[$x];
     }
     $stmt = sqlsrv_prepare($this->dbh, $sql, $prep_params);
     if ($stmt === false) {
         return false;
     }
     return sqlsrv_execute($stmt);
 }

 public function prepare($query, $params = array())
 {
     global $conn;
     set_error_handler(function ($errno, $errstr, $errfile, $errline, array $errcontext) {
         // error was suppressed with the @-operator
         if (0 === error_reporting()) {
             return false;
         }
         throw new ErrorException($errstr, 0, $errno, $errfile, $errline);
     });
     try {
         $ret = sqlsrv_prepare($conn, $query, $params);
         if (!$ret) {
             error_out(var_export(sqlsrv_errors(), true));
         }
         return $ret;
     } catch (Exception $e) {
         //
     }
     restore_error_handler();
 }

 /**
  * {@inheritdoc}
  */
 public function execute($params = null)
 {
     if ($params) {
         $hasZeroIndex = array_key_exists(0, $params);
         foreach ($params as $key => $val) {
             $key = $hasZeroIndex && is_numeric($key) ? $key + 1 : $key;
             $this->bindValue($key, $val);
         }
     }
     if (!$this->stmt) {
         $stmt = sqlsrv_prepare($this->conn, $this->sql, $this->params);
         if (!$stmt) {
             throw SQLSrvException::fromSqlSrvErrors();
         }
         $this->stmt = $stmt;
     }
     if (!sqlsrv_execute($this->stmt)) {
         throw SQLSrvException::fromSqlSrvErrors();
     }
     if ($this->lastInsertId) {
         sqlsrv_next_result($this->stmt);
         sqlsrv_fetch($this->stmt);
         $this->lastInsertId->setId(sqlsrv_get_field($this->stmt, 0));
     }
 }

 /**
  * (non-PHPdoc)
  * @see classes/connectionengine/Connect_STH#procedure($procedureName, $params)
  */
 public function &procedure($procedureName = 0, $params = array())
 {
     $objReturned = null;
     $result = null;
     if ($procedureName != 0) {
         throw new ConnectionengineException('$procedureName is null.', __FILE__, __LINE__, sqlsrv_errors());
     }
     $callString = sprintf('EXEC %s ', $procedureName);
     if (count($params) > 0) {
         //$callString .= ' (';
         for ($index = 0, $indexMax = count($params); $index < $indexMax; $index++) {
             if ($index != $indexMax - 1) {
                 $callString .= $params[$index][0] . ',';
             } else {
                 $callString .= $params[$index][0];
             }
         }
         // foreach
         //$callString .= ' )';
     }
     // if
     //$callString .= ' }';
     /* Create the statement. */
     $stmt = sqlsrv_prepare($this->connection, $callString, null);
     if (!$stmt) {
         throw new ConnectionengineException('Error in preparing statement.', __FILE__, __LINE__, sqlsrv_errors());
     }
     $result = sqlsrv_execute($stmt);
     if ($this->debug == true) {
         echo Logger::Log($callString);
     }
     // Traitement erreurs
     if ($result === false && $procedureName != 'sp_start_job') {
         print_r(sqlsrv_errors());
         //echo "Error in query preparation/execution.\n";
         throw new ConnectionengineException('Error on query (KO):' . $callString, __FILE__, __LINE__, sqlsrv_errors());
     } else {
         $objReturned = true;
     }
     //	$objReturned = new Sqlsrv_result_STH ( & $result , $this->defaultAssocType, $query );
     //	$objReturned->debug = $this->debug;
     return $objReturned;
 }

 /**
  * Get the record from the specified table using the given $tableName, $searchFieldName and $searchValue
  *  and set the "Active" field to $value
  * @param $tableName :  string - name of the table
  * @param $searchFieldName :  The fieldname of the record you want to do the search on
  * @param $searchValue : either integer(only) or alphanumeric
  * @param $Active : Sets the 'Active' field to TRUE or FALSE
  * @return bool: Returns TRUE or FALSE.
  */
 function setActive($tableName, $searchFieldName, $searchValue, $Active)
 {
     if ($Active === true) {
         $active = 'TRUE';
     } else {
         $active = 'FALSE';
     }
     if (is_numeric($searchValue)) {
         // Finds whether a variable is a number or a numeric string
         $sql = 'UPDATE' . $tableName . 'SET Active =' . $active . 'WHERE' . $searchFieldName . ' = ' . $searchValue;
     } else {
         $sql = 'UPDATE' . $tableName . 'SET Active =' . $active . 'WHERE' . $searchFieldName . " = '" . $searchValue . "'";
     }
     //prepare statement
     $stmt = sqlsrv_prepare(Database::getConnection(), $sql);
     if (!$stmt) {
         return false;
     }
     $result = sqlsrv_execute($stmt);
     return $result;
 }

function user_is_role($email, $role)
{
    $return_value = 0;
    $user_id = user_exist_sqlsrv($email);
    $role_id = role_to_roleid($role);
    global $conn;
    $qry = "SELECT count(*) AS count FROM user_role WHERE user_id = ? AND role_id = ? AND active = 1";
    $params = array(&$user_id, &$role_id);
    $rst = sqlsrv_prepare($conn, $qry, $params);
    sqlsrv_execute($rst);
    sqlsrv_fetch($rst);
    error_log("checked {$email} for role {$role} using {$user_id} and {$role_id}");
    $return_value = sqlsrv_get_field($rst, 0);
    sql_errors_display("from user is role");
    return $return_value;
}