function SaveLecturerMarks($marks)
{
$status = array();
include_once 'db_Connection.php';
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn) {
$login = "******";
$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;
}
}
}
}
}
public static function execute($psql, $prm = null)
{
$ret = false;
if ($psql) {
$ret = sqlsrv_execute($psql);
}
return $ret;
}
/**
* (non-PHPdoc)
* @see PreparedStatement::executePreparedStatement()
*/
public function executePreparedStatement(array $data, $msg = '')
{
if (!$this->prepareStatementData($data, count($this->fieldDefs), $msg)) {
return false;
}
$res = sqlsrv_execute($this->stmt);
return $this->finishStatement($res, $msg);
}
public function query($sql)
{
$this->curStmt = sqlsrv_prepare($this->linkID, $query, array());
return sqlsrv_execute($this->curStmt);
}
$adres = $_POST['adres'];
$town = $_POST['town'];
$zip = $_POST['zip'];
$land = $_POST['land'];
$birthdate = $_POST['birthdate'];
$email = $_POST['email'];
$username = $_SESSION['username'];
$sql = "SELECT * FROM GEBRUIKER WHERE GEBRUIKERSNAAM='{$username}'";
$params = array();
$options = array("Scrollable" => SQLSRV_CURSOR_KEYSET);
$stmt = sqlsrv_query($conn, $sql, $params, $options);
$rowCount = sqlsrv_num_rows($stmt);
if ($rowCount == 1) {
$sqlUpdate = "UPDATE GEBRUIKER SET Voornaam='{$name}', Achternaam='{$lastname}', Adresregel='{$adres}', Postcode='{$zip}', Plaatsnaam='{$town}', Land='{$land}', GeboorteDag='{$birthdate}', Mailbox='{$email}' WHERE GEBRUIKERSNAAM='{$username}'";
$stmtUpdate = sqlsrv_prepare($conn, $sqlUpdate);
sqlsrv_execute($stmtUpdate);
header('location: profiel.php');
}
}
sqlsrv_close($conn);
} else {
die(print_r(sqlsrv_errors(), true));
}
?>
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" type="text/css" href="stylesheet.css"/>
<meta charset="utf-8">
<title>Eenmaal Andermaal</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
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);
}
/**
* Execute
*
* @param array|ParameterContainer $parameters
* @throws Exception\RuntimeException
* @return Result
*/
public function execute($parameters = null)
{
/** END Standard ParameterContainer Merging Block */
if (!$this->isPrepared) {
$this->prepare();
}
/** START Standard ParameterContainer Merging Block */
if (!$this->parameterContainer instanceof ParameterContainer) {
if ($parameters instanceof ParameterContainer) {
$this->parameterContainer = $parameters;
$parameters = null;
} else {
$this->parameterContainer = new ParameterContainer();
}
}
if (is_array($parameters)) {
$this->parameterContainer->setFromArray($parameters);
}
if ($this->parameterContainer->count() > 0) {
$this->bindParametersFromContainer();
}
if ($this->profiler) {
$this->profiler->profilerStart($this);
}
$resultValue = sqlsrv_execute($this->resource);
if ($this->profiler) {
$this->profiler->profilerFinish();
}
if ($resultValue === false) {
$errors = sqlsrv_errors();
// ignore general warnings
if ($errors[0]['SQLSTATE'] != '01000') {
throw new Exception\RuntimeException($errors[0]['message']);
}
}
$result = $this->driver->createResult($this->resource);
return $result;
}
/**
* @param string $sql
* @return bool|MssqlResult
* @throws DBUnexpectedError
*/
protected function doQuery($sql)
{
global $wgDebugDumpSql;
if ($wgDebugDumpSql) {
wfDebug("SQL: [{$sql}]\n");
}
$this->offset = 0;
// several extensions seem to think that all databases support limits
// via LIMIT N after the WHERE clause well, MSSQL uses SELECT TOP N,
// so to catch any of those extensions we'll do a quick check for a
// LIMIT clause and pass $sql through $this->LimitToTopN() which parses
// the limit clause and passes the result to $this->limitResult();
if (preg_match('/\\bLIMIT\\s*/i', $sql)) {
// massage LIMIT -> TopN
$sql = $this->LimitToTopN($sql);
}
// MSSQL doesn't have EXTRACT(epoch FROM XXX)
if (preg_match('#\\bEXTRACT\\s*?\\(\\s*?EPOCH\\s+FROM\\b#i', $sql, $matches)) {
// This is same as UNIX_TIMESTAMP, we need to calc # of seconds from 1970
$sql = str_replace($matches[0], "DATEDIFF(s,CONVERT(datetime,'1/1/1970'),", $sql);
}
// perform query
// SQLSRV_CURSOR_STATIC is slower than SQLSRV_CURSOR_CLIENT_BUFFERED (one of the two is
// needed if we want to be able to seek around the result set), however CLIENT_BUFFERED
// has a bug in the sqlsrv driver where wchar_t types (such as nvarchar) that are empty
// strings make php throw a fatal error "Severe error translating Unicode"
if ($this->mScrollableCursor) {
$scrollArr = array('Scrollable' => SQLSRV_CURSOR_STATIC);
} else {
$scrollArr = array();
}
if ($this->mPrepareStatements) {
// we do prepare + execute so we can get its field metadata for later usage if desired
$stmt = sqlsrv_prepare($this->mConn, $sql, array(), $scrollArr);
$success = sqlsrv_execute($stmt);
} else {
$stmt = sqlsrv_query($this->mConn, $sql, array(), $scrollArr);
$success = (bool) $stmt;
}
if ($this->mIgnoreDupKeyErrors) {
// ignore duplicate key errors, but nothing else
// this emulates INSERT IGNORE in MySQL
if ($success === false) {
$errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
$success = true;
foreach ($errors as $err) {
if ($err['SQLSTATE'] == '23000' && $err['code'] == '2601') {
continue;
// duplicate key error
} elseif ($err['SQLSTATE'] == '01000' && $err['code'] == '3621') {
continue;
// generic "the statement has been terminated" error
}
$success = false;
// getting here means we got an error we weren't expecting
break;
}
if ($success) {
$this->mAffectedRows = 0;
return true;
}
}
}
if ($success === false) {
return false;
}
// remember number of rows affected
$this->mAffectedRows = sqlsrv_rows_affected($stmt);
return $stmt;
}
public function execute($bind_params = false)
{
$ret_val = false;
$arg_list = func_get_args();
$lazy = isset($arg_list[1]) && $arg_list[1] ? true : false;
//----------------------------------------------
// Prepare SQL Statement
//----------------------------------------------
$prepare_status = $this->_prepare($this->curr_query, $bind_params, $lazy);
if (!$prepare_status) {
if ($this->check_and_print_error()) {
return false;
}
$this->print_error('Query prepare failed.');
return false;
}
if (!$this->stmt) {
return false;
}
//----------------------------------------------
// Execute Query
//----------------------------------------------
$exec_status = @sqlsrv_execute($this->stmt);
if (!$exec_status) {
if ($this->check_and_print_error()) {
return false;
}
$this->print_error('Query execution failed.');
return false;
}
//----------------------------------------------
// Create Data Result Object if Necessary
//----------------------------------------------
if ($this->stmt && gettype($this->stmt) != 'boolean') {
//----------------------------------------------
// Affected Rows
//----------------------------------------------
$this->affected_rows = sqlsrv_rows_affected($this->stmt);
$ret_val = $this->affected_rows;
//----------------------------------------------
// Create Data Result Object
//----------------------------------------------
$has_rows = sqlsrv_has_rows($this->stmt);
$this->data_result = new data_result($this->stmt, $this->data_src);
//----------------------------------------------
// Last Insert ID
//----------------------------------------------
$this->last_id = null;
}
//----------------------------------------------
// Return Data Result Object if it exists
//----------------------------------------------
if ($this->data_result) {
$this->num_rows = $this->data_result->num_rows();
$this->num_fields = $this->data_result->num_fields();
$ret_val = $this->data_result;
}
//----------------------------------------------
// Check for Errors
//----------------------------------------------
if ($this->check_and_print_error()) {
return false;
}
return $ret_val;
}
/**
* Execute the prepared SQL query.
*
* @throws \Pop\Db\Adapter\Exception
* @return void
*/
public function execute()
{
if (null === $this->statement) {
throw new Exception('Error: The database statement resource is not currently set.');
}
sqlsrv_execute($this->statement);
}
$consulta = " INSERT INTO META_RECURSO (RECU_TITULO,RECU_TEMA,RECU_DESC,RECU_FUENTE,RECU_LENGUAJE,RECU_RELACION,RECU_COBERTURA,DOCU_ID) \n\t\tVALUES ('{$titulo}','{$tema}','{$desc}','{$fuente}','{$lenguaje}','{$relacion}','{$cobertura}',{$idDoc}) ";
$recurso = sqlsrv_prepare($conn, $consulta);
//$ejecutar_con = sqlsrv_query( $conn, $consulta);
if (sqlsrv_execute($recurso)) {
$mensaje = ":)";
} else {
echo "CTM!!";
}
$consulta = " INSERT INTO META_INSTANCIA ( INST_FECHA,INST_TIPO,INST_FORMATO,INST_IDENTIFICADOR,DOCU_ID) \n\t\tVALUES ('{$fecha}','{$tipo}','{$formato}','{$ident}',{$idDoc}) ";
$recurso = sqlsrv_prepare($conn, $consulta);
//$ejecutar_con = sqlsrv_query( $conn, $consulta);
if (sqlsrv_execute($recurso)) {
$mensaje = ":)";
}
$consulta = " INSERT INTO META_PROPIEDAD ( PROP_AUTOR,PROP_EDITOR,PROP_COLABORADOR,PROP_DEPARTAMENTO,PROP_DERECHOS,DOCU_ID) \n\t\tVALUES ('{$autor}','{$editor}','{$colaborador}','{$departamento}','{$derechos}',{$idDoc}) ";
$recurso = sqlsrv_prepare($conn, $consulta);
//$ejecutar_con = sqlsrv_query( $conn, $consulta);
if (sqlsrv_execute($recurso)) {
$mensaje = ":)";
sqlsrv_close($conn);
header("Location: ../../index.php?op=meta&{$mensaje}");
}
} else {
sqlsrv_close($conn);
$mensaje = "Ya se ingreso el documento";
//header("Location: ../../index.php?op=meta&$mensaje");
}
?>
/**
* Execute the query
* this method is private
* @param string $query
* @param resource $connection
* @return resource|boolean Returns a statement resource on success and FALSE if an error occurred.
*/
function __query($query, $connection)
{
$_param = array();
if (count($this->param)) {
foreach ($this->param as $k => $o) {
if ($o->isColumnName()) {
continue;
}
if ($o->getType() == 'number') {
$value = $o->getUnescapedValue();
if (is_array($value)) {
$_param = array_merge($_param, $value);
} else {
$_param[] = $o->getUnescapedValue();
}
} else {
$value = $o->getUnescapedValue();
if (is_array($value)) {
foreach ($value as $v) {
$_param[] = array($v, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING('utf-8'));
}
} else {
$_param[] = array($value, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STRING('utf-8'));
}
}
}
}
// Run the query statement
$result = false;
if (count($_param)) {
$args = $this->_getParametersByReference($_param);
$stmt = sqlsrv_prepare($connection, $query, $args);
} else {
$stmt = sqlsrv_prepare($connection, $query);
}
if (!$stmt) {
$result = false;
} else {
$result = sqlsrv_execute($stmt);
}
// Error Check
if (!$result) {
$this->setError(print_r(sqlsrv_errors(), true));
}
$this->param = array();
return $stmt;
}
<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 </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>
<div class="omschrijving">
private function &callWithSqlSrv($qry)
{
$connectionInfo = array("UID" => $this->username, "PWD" => $this->password, "Database" => $this->dbname);
$conn = sqlsrv_connect($this->servername, $connectionInfo);
$params = array();
foreach ($this->params as $param) {
$params[] =& $param[0];
}
if (!($stmt = sqlsrv_prepare($conn, $qry, $params))) {
throw new Exception(sqlsrv_errors());
return false;
}
if (!sqlsrv_execute($stmt)) {
throw new Exception(sqlsrv_errors());
return false;
}
return $stmt;
if (!sqlsrv_fetch($stmt)) {
return false;
}
foreach ($this->columnBindings as $k => $col) {
if ($col[1]) {
$col[0] = sqlsrv_get_field($stmt, $k, SQLSRV_PHPTYPE_STREAM($col[1]));
} else {
$col[0] = sqlsrv_get_field($stmt, $k);
}
}
// successfully created connection and executed query, return true.
// the user should now be calling "movenext"
$this->sqlsrvConnection =& $conn;
$this->sqlsrvStatement =& $stmt;
return true;
}
/**
* 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;
}
/**
* {@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;
}
/**
* @param String $tableName - The name of the table. Returns all rows from the table requested.
* @param Int $Id - The id of the record.
* @param STRING $type - The return type wanted. SQLSRV_FETCH_ASSOC
* OR SQLSRV_FETCH_NUMERIC - See http://www.php.net/manual/en/function.sqlsrv-fetch-array.php
* @return ARRAY - This method will return an associative or numeric array is results are returned.
* By default an associative array will be returned.
*/
public function get_by_id($tableName, $id = null)
{
$sql = "SELECT * FROM {$tableName} WHERE id = {$id}";
$preparedStatement = sqlsrv_prepare($this->connection, $sql);
$result = sqlsrv_execute($preparedStatement);
if ($result === true) {
while ($row = sqlsrv_fetch_array($preparedStatement, SQLSRV_FETCH_ASSOC)) {
$results = $row;
}
return $results;
} else {
return false;
}
}
/**
* Execute
*
* @param array|ParameterContainerInterface $parameters
* @return type
*/
public function execute($parameters = null)
{
if (!$this->isPrepared) {
$this->prepare();
}
if ($parameters !== null) {
if (is_array($parameters)) {
$parameters = new ParameterContainer($parameters);
}
if (!$parameters instanceof ParameterContainerInterface) {
throw new \InvalidArgumentException('ParameterContainer expected');
}
$this->parameterContainer = $parameters;
}
if ($this->parameterContainer) {
$this->bindParametersFromContainer();
}
$resultValue = sqlsrv_execute($this->resource);
if ($resultValue === false) {
$errors = sqlsrv_errors();
// ignore general warnings
if ($errors[0]['SQLSTATE'] != '01000') {
throw new \RuntimeException($errors[0]['message']);
}
}
$result = $this->driver->createResult($this->resource);
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;
}
private function get_result()
{
$output = array();
$stmt1 = sqlsrv_prepare( $this->link, $this->sp_prepared_call, $this->params);
if( !$stmt1 )
$output = -1;
$stmt = sqlsrv_execute($stmt1);
if ($stmt == TRUE){
while ( $aRow = sqlsrv_fetch_array( $stmt1 ) )
{
$output[] = $aRow;
}
} else {
$output = -1;
}
///Free Resource
if( !$stmt1 )
sqlsrv_free_stmt($stmt1);
return $output;
}
public function execute($query, $params = array())
{
$ret = false;
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 {
$stmt = db_prepare($query, $params);
if ($stmt) {
$ret = sqlsrv_execute($stmt);
}
if (!$ret) {
trigger_error(var_export(sqlsrv_errors(), true));
return false;
}
return $ret;
} catch (Exception $e) {
//
}
restore_error_handler();
}
public function execute()
{
if ($this->usedPrepare) {
if (!sqlsrv_execute($this->stmt)) {
throw new SqlException('Failed to execute prepared statement', sqlsrv_errors(), $this->query, $this->params);
}
}
$next = null;
$selectedRows = false;
$this->affected = 0;
// get affected row count from each result (triggers could cause multiple inserts)
do {
$affectedRows = sqlsrv_rows_affected($this->stmt);
if ($affectedRows === false) {
throw new SqlException('Failed to get affected row count', sqlsrv_errors(), $this->query, $this->params);
} elseif ($affectedRows === -1) {
$selectedRows = true;
// reached SELECT result
break;
// so that getIterator will be able to select the rows
} else {
$this->affected += $affectedRows;
}
} while ($next = sqlsrv_next_result($this->stmt));
if ($next === false) {
throw new SqlException('Failed to get next result', sqlsrv_errors(), $this->query, $this->params);
}
if ($selectedRows === false && !$this->usedPrepare) {
$this->close();
// no results, so statement can be closed
}
}
function getResponseSets()
{
global $conn;
$qry = "SELECT title, ResponseSet, id from dbo.q_response_sets ORDER BY title";
$rst = sqlsrv_prepare($conn, $qry);
sqlsrv_execute($rst);
while ($row = sqlsrv_fetch_array($rst)) {
echo $row['title'];
}
}
private function executeQueryWithParameters()
{
$return = false;
if (count($this->parameters) != 0) {
// Prepato los parametros
$params = array();
foreach ($this->parameters as $param => $paramContent) {
$params[] = $paramContent['value'];
}
// Preparo la consulta
$stmt = sqlsrv_prepare($this->link, $this->query, $params);
// Se procede con la ejecucion de la consulta
if ($this->queryType == 'other') {
if (sqlsrv_execute($stmt) === true) {
$return = true;
$this->error = sqlsrv_errors();
}
} else {
if (sqlsrv_execute($stmt) === true) {
// Conteo de registros
if ($this->queryType == 'insert' || $this->queryType == 'update' || $this->queryType == 'delete') {
$this->numRows = sqlsrv_rows_affected($stmt);
$return = true;
} else {
// Se obtiene el numero de filas obtenidas de los metadatos de la consulta
$this->numRows = sqlsrv_num_rows($stmt);
$fetchType = SQLSRV_FETCH_NUMERIC;
if ($this->queryReturn == 'assoc') {
$fetchType = SQLSRV_FETCH_ASSOC;
} elseif ($this->queryReturn == 'both') {
$fetchType = SQLSRV_FETCH_BOTH;
}
$return = array();
while ($row = sqlsrv_fetch_array($stmt, $fetchType)) {
array_push($return, $row);
}
}
$this->error = sqlsrv_errors();
sqlsrv_free_stmt($stmt);
} else {
$this->error = sqlsrv_errors();
}
}
}
return $return;
}
public function update($id, $value, $changeRecord)
{
if (!is_array($changeRecord)) {
return array(printf('A Company Record was expected as a parameter but a %s was received', gettype($changeRecord)));
}
//populate the company array
$this::$BusinessEntity = $changeRecord;
$sqlCommand = sprintf("\n\t\t\t\tBEGIN\n\t\t\t\tUPDATE BusinessEntity\n\t\t\t\tSET\n\t\t\t\t[Name] = '%s',\n\t\t\t\t[BusinessEntityCode] = '%s',\n\t\t\t\t[BusinessEntityDescription] = '%s',\n\t\t\t\t[BusinessEntityParentId] = %s,\n\t\t\t\t[BusinessLevelId] = %d,\n\t\t\t\t[Active]= %d,\n\t\t\t\t[BusinessEntityShortName] = '%s' WHERE %s = '%s' END", $this::$BusinessEntity['Name']['Value'], $this::$BusinessEntity['BusinessEntityCode']['Value'], $this::$BusinessEntity['BusinessEntityDescription']['Value'], $this::$BusinessEntity['BusinessEntityParentId']['Value'], 2, $this::$BusinessEntity['Active']['Value'], $this::$BusinessEntity['BusinessEntityShortName']['Value'], $id, $value);
$stmt = sqlsrv_prepare(Database::getConnection(), $sqlCommand);
// Prepares a Transact-SQL query without executing it. Implicitly binds parameters.
if (!$stmt) {
return array(printf('An error was received when the function sqlsrv_prepare was called.
The error message was: %s', dbGetErrorMsg()));
}
$result = sqlsrv_execute($stmt);
// Executes a prepared statement.
if (!$result) {
return array(printf('An error was received when the function sqlsrv_execute was called.
The error message was: %s', dbGetErrorMsg()));
}
return array();
}
function _query($sql, $inputarr = false)
{
$this->_errorMsg = false;
if (is_array($inputarr)) {
$rez = sqlsrv_query($this->_connectionID, $sql, $inputarr);
} else {
if (is_array($sql)) {
// $inputarr is prepared in sqlsrv_prepare();
$rez = sqlsrv_execute($this->_connectionID, $sql[1]);
} else {
$rez = sqlsrv_query($this->_connectionID, $sql);
}
}
if ($this->debug) {
error_log("<hr>running query: " . var_export($sql, true) . "<hr>input array: " . var_export($inputarr, true) . "<hr>result: " . var_export($rez, true));
}
if (!$rez) {
$rez = false;
}
return $rez;
}
public function list_columns($table, $like = NULL, $add_prefix = TRUE)
{
// Quote the table name
//$table = ($add_prefix === TRUE) ? $this->quote_table($table) : $table;
// Connect to DB if required
$this->_connection or $this->connect();
if (is_string($like)) {
if (($query = sqlsrv_prepare($this->_connection, 'sp_columns @table_name=? @column_name=?', array($table, $like), array('Scrollable' => SQLSRV_CURSOR_KEYSET))) === FALSE) {
// Get the errors
$error = sqlsrv_errors(SQLSRV_ERR_ERRORS);
// Throw an exception
throw new Database_Sqlsrv_Exception(':error [ :query ]', array(':error' => $error[0]['message'], ':query' => 'sp_columns @table_name=? @column_name=?'), $error[0]['code']);
}
if (($result = sqlsrv_execute($query)) === FALSE) {
// Get the errors
$error = sqlsrv_errors(SQLSRV_ERR_ERRORS);
// Throw an exception
throw new Database_Sqlsrv_Exception(':error [ :query ]', array(':error' => $error[0]['message'], ':query' => 'sp_columns @table_name=? @column_name=?'), $error[0]['code']);
}
} else {
if (($result = sqlsrv_query($this->_connection, 'sp_columns @table_name=?', array($table), array('Scrollable' => SQLSRV_CURSOR_KEYSET))) === FALSE) {
// Get the errors
$error = sqlsrv_errors(SQLSRV_ERR_ERRORS);
// Throw an exception
throw new Database_Sqlsrv_Exception(':error [ :query ]', array(':error' => $error[0]['message'], ':query' => 'sp_columns @table_name=?'), $error[0]['code']);
}
}
$columns = array();
while ($result_array = sqlsrv_fetch_array($result)) {
if (is_array($result_array)) {
$column = $this->datatype($result_array['TYPE_NAME']);
$column['column_name'] = $result_array['COLUMN_NAME'];
$column['column_default'] = $result_array['COLUMN_DEF'];
$column['data_type'] = preg_replace('/\\s([a-z]+)/i', '', $result_array['TYPE_NAME']);
$column['is_nullable'] = $result_array['NULLABLE'] == 1;
$column['ordinal_position'] = $result_array['ORDINAL_POSITION'];
switch ($column['data_type']) {
case 'float':
$column['numeric_precision'] = $result_array['PRECISION'];
$column['numeric_scale'] = $result_array['SCALE'];
break;
case 'int':
$column['display'] = $result_array['LENGTH'];
$column['radix'] = $result_array['RADIX'];
break;
case 'string':
switch ($column['data_type']) {
case 'binary':
case 'varbinary':
case 'varbinary(MAX)':
case 'varchar(MAX)':
case 'geography':
case 'geometry':
case 'image':
case 'char':
case 'nchar':
case 'nvarchar':
case 'varchar':
case 'money':
case 'smallmoney':
case 'numeric':
case 'sql_variant':
case 'uniqueidentifier':
case 'decimal':
case 'bigint':
$column['character_maximum_length'] = $result_array['CHAR_OCTET_LENGTH'];
break;
}
break;
}
// MySQL attributes
$column['comment'] = $result_array['REMARKS'];
// NOT SUPPORTED AT THE MOMENT, WILL ALWAYS BE NULL
$column['key'] = strpos($result_array['TYPE_NAME'], ' identity') !== FALSE ? 'identity' : FALSE;
$columns[$result_array['COLUMN_NAME']] = $column;
}
}
sqlsrv_free_stmt($result);
return $columns;
}