function dbcall()
{
session_start();
$seubid = (string) session_id();
$server = "bamsql2";
$options = array("UID" => "genes", "PWD" => "Genes12", "Database" => "genes");
$conn = sqlsrv_connect($server, $options);
if ($conn === false) {
die("<pre>" . print_r(sqlsrv_errors(), true));
}
$rno = $_POST['Rnumber'];
$name = $_POST['name'];
$email = $_POST['email'];
$gender = $_POST['gender'];
$sql = "insert INTO dbo.contactinfo values('{$rno}','{$name}','{$email}','{$gender}')";
$query = sqlsrv_query($conn, $sql);
if ($query === false) {
exit("<pre>" . print_r(sqlsrv_errors(), true));
}
#while ($row = sqlsrv_fetch_array($query))
# { echo "<p>Hello, $row[ascore]!</p>";
#}
sqlsrv_free_stmt($query);
sqlsrv_close($conn);
}
public function query($sql)
{
LogMaster::log($sql);
if ($this->start_from) {
$res = sqlsrv_query($this->connection, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_STATIC));
} else {
$res = sqlsrv_query($this->connection, $sql);
}
if ($res === false) {
$errors = sqlsrv_errors();
$message = array();
foreach ($errors as $error) {
$message[] = $error["SQLSTATE"] . $error["code"] . $error["message"];
}
throw new Exception("SQLSrv operation failed\n" . implode("\n\n", $message));
}
if ($this->insert_operation) {
sqlsrv_next_result($res);
$last = sqlsrv_fetch_array($res);
$this->last_id = $last["dhx_id"];
sqlsrv_free_stmt($res);
}
if ($this->start_from) {
$data = sqlsrv_fetch($res, SQLSRV_SCROLL_ABSOLUTE, $this->start_from - 1);
}
return $res;
}
function dbGetErrorMsg()
{
$retVal = sqlsrv_errors();
$retVal = $retVal[0]["message"];
$retVal = preg_replace('/\\[Microsoft]\\[SQL Server Native Client [0-9]+.[0-9]+](\\[SQL Server\\])?/', '', $retVal);
return $retVal;
}
/**
*
* @param resource $conn
* Recurso que contiene la conexión SQL.
* @param string $tabla
* @param boolean $comprobar
* Si está a true (valor por defecto) siempre hace la comprobación.
* Si se pone el valor a false sólo hace la comprobación cuando es
* día 1.
* @return mixed array si hay un error SQL. Si la tabla no existe false. Si la
* tabla existe true.
*/
function existeTabla($conn, $tabla, $comprobarTabla = 1)
{
$hoy = getdate();
if ($hoy["mday"] == 1 or $comprobarTabla) {
global $respError;
$sql = "select * from dbo.sysobjects where id = object_id(N'{$tabla}')";
// Ejecutar una consulta SQL para saber si existe la tabla de auditoría
//////////////////////////////////////////////////////
$stmt = sqlsrv_query($conn, $sql);
if ($stmt === false) {
if (($errors = sqlsrv_errors()) != null) {
$SQLSTATE = $errors[0]["SQLSTATE"];
$Cerror = $errors[0]["code"];
$Merror = utf8_encode($errors[0]["message"]);
if ($farmacia == FARMACIA_DEBUG or strlen(FARMACIA_DEBUG) == 0) {
if (DEBUG & DEBUG_ERROR_SQL) {
$mensaje = "--[" . date("c") . "] código: {$Cerror} mensaje: {$Merror} \n";
$mensaje .= "--Error en el fichero: " . __FILE__ . ", en la línea: " . __LINE__;
error_log($mensaje . $sql . "\r\n", 3, DIRECTORIO_LOG . __FUNCTION__ . "_" . date("YmdH") . ".log");
}
}
// Error al hacer la consulta
return $respError->errorSQL($SQLSTATE, $Cerror, $Merror);
}
}
if (sqlsrv_has_rows($stmt) === false) {
// La tabla no existe.
return false;
}
}
// La tabla existe o no hay que comprobarlo
return true;
}
public function AttemptConnection()
{
if (empty($this->type)) {
return false;
} elseif ($this->type == "MySQL") {
$this->connection = mysqli_connect($this->address, $this->username, $this->password, $this->database);
if (!$this->connection) {
$this->lastErrorMessage = "Error: Unable to connect to MySQL." . PHP_EOL . "Debugging errno: " . mysqli_connect_errno() . PHP_EOL . "Debugging error: " . mysqli_connect_error() . PHP_EOL;
return false;
}
//SQL Server
} elseif ($this->type == "MSSQL") {
$connectionInfo = array("Database" => $this->database, "UID" => $this->username, "PWD" => $this->password);
$this->connection = sqlsrv_connect($this->address, $connectionInfo);
if (!$this->connection) {
$errorString = "";
if (($errors = sqlsrv_errors()) != null) {
foreach ($errors as $error) {
$errorString .= PHP_EOL . "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" . PHP_EOL;
$errorString .= "SQLSTATE: " . $error['SQLSTATE'] . PHP_EOL;
$errorString .= "code: " . $error['code'] . PHP_EOL;
$errorString .= "message: " . $error['message'] . PHP_EOL;
}
}
$this->lastErrorMessage = "Error: Unable to connect to SQL Server." . PHP_EOL . "Debugging error: " . $errorString . PHP_EOL;
$this->lastError = $errorString;
return false;
}
}
return $this->connection;
}
function begin_trans()
{
if (sqlsrv_begin_transaction($this->conn) == false) {
echo "Could not begin transaction.\n";
die(print_r(sqlsrv_errors(), true));
}
$this->in_trans = true;
}
function begin_trans()
{
if (mysql_query("BEGIN") == false) {
echo "Could not begin transaction.\n";
die(print_r(sqlsrv_errors(), true));
}
$this->in_trans = true;
}
/**
* Enviar query para servidor SQL
*
* @param string $query sql query
*
* @return void
*/
public function query($query)
{
$query = filter_var($query, FILTER_SANITIZE_STRING);
$this->_query = sqlsrv_query($this->conn, $query);
if (!$this->_query) {
die(print_r(sqlsrv_errors(), true));
}
}
/**
* Returns all errors as a string
*
* @return string
*/
protected function errors()
{
$string = '';
foreach (sqlsrv_errors() as $error) {
$string .= '[' . $error[0] . ':' . $error[1] . ']: ' . $error[2] . ', ';
}
return substr($string, 0, -2);
}
public function query($sql)
{
$this->connect();
$this->stmt = sqlsrv_query($this->conn, $sql);
if ($this->stmt === false) {
die(print_r(sqlsrv_errors(), true));
}
}
public function loadEventsByLocation(int $location)
{
$query = 'SELECT event.name AS name, event.description AS description FROM event INNER JOIN location ON event.fk_location = location.id_location WHERE location.id_location = ?';
$stmt = sqlsrv_query($this->connection, $query, [$location]);
if (sqlsrv_errors()) {
http_response_code(500);
}
return $stmt;
}
public function __construct($dbName = "ecs")
{
$dbHost = ".";
$connectionInfo = array("Database" => $dbName, "UID" => "sa", "PWD" => "sa");
$this->conn = sqlsrv_connect($dbHost, $connectionInfo);
if (!$this->conn) {
exit('Connect Error (' . sqlsrv_errors() . ') ' . sqlsrv_errors());
}
}
/**
* Lädt die Orte, welche den mitgegebenen String im Namen enthalten.
* Offset: Beim wievielten Datensatz das Laden beginnt
* Rows: Wie viele Datensätze geladen werden
* @param int $offset
* @param int $rows
* @param string $location
* @return bool|resource
*/
public function loadLocationsByIdAndName(int $offset, int $rows, string $location)
{
$query = "SELECT \n id_location AS id_location,\n name AS name, \n description AS description\n FROM location\n WHERE location.name LIKE ?\n ORDER BY id_location\n OFFSET {$offset} ROWS \n FETCH NEXT {$rows} ROWS ONLY";
$stmt = sqlsrv_query(Database::getConnection(), $query, ['%' . $location . '%']);
if (sqlsrv_errors()) {
http_response_code(500);
}
return $stmt;
}
/**
* Executes the SQL query.
* @param string SQL statement.
* @return IDibiResultDriver|NULL
* @throws DibiDriverException
*/
public function query($sql)
{
$this->resultSet = sqlsrv_query($this->connection, $sql);
if ($this->resultSet === FALSE) {
$info = sqlsrv_errors();
throw new DibiDriverException($info[0]['message'], $info[0]['code'], $sql);
}
return is_resource($this->resultSet) ? clone $this : NULL;
}
/**
* Get SQL errors
*
* @return string
*/
public function getErrors()
{
$errors = null;
$errorAry = sqlsrv_errors();
foreach ($errorAry as $key => $value) {
$errors .= 'SQLSTATE: ' . $value['SQLSTATE'] . ', CODE: ' . $value['code'] . ' => ' . stripslashes($value['message']) . PHP_EOL;
}
return $errors;
}
/**
* Prepares and executes a single SQL Server query
* @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 query($sql, array $params = [])
{
if (!($stmt = sqlsrv_query($this->connection, $sql, $params))) {
throw new SqlException('Query failed', sqlsrv_errors(), $sql, $params);
}
$statement = new Statement($stmt, false, $sql, $params);
$statement->execute();
return $statement;
}
/**
* @param $idLocation
* @return bool|resource
*/
public function getImages(int $idLocation)
{
$query = 'SELECT id_image from image where fk_location = ?';
$stmt = sqlsrv_query(Database::getConnection(), $query, array($idLocation));
if (sqlsrv_errors()) {
http_response_code(500);
}
return $stmt;
}
public function load(string $username)
{
$query = 'SELECT * FROM person WHERE username = ?';
$stmt = sqlsrv_query(Database::getConnection(), $query, array($username));
if (sqlsrv_errors()) {
http_response_code(500);
}
return sqlsrv_fetch_array($stmt);
}
function db_exec($conn, $tsql, $params)
{
$stmt = sqlsrv_query($conn, $tsql, $params);
if (!$stmt) {
echo "exec failed.\n";
die(print_r(sqlsrv_errors(), true));
}
$member = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
return $member;
}
function error($method_name = "")
{
$out = false;
$errs = sqlsrv_errors();
if ($errs != null) {
foreach ($errs as $err) {
$out .= $method_name . " -> " . $err['message'];
}
}
return $out;
}
/**
* Ejecuta una query update.
*
* @param resource $conn
* @param string $tabla
* La tabla a la que queremos hacerle la query.
* @param array $set
* Los campos que irán en la cláusula <b><i>SET</i></b>. <br>Donde
* <b><i>key</i></b> el el nombre del campo y <b><i>value</i></b> es
* el valor a comparar.
* @param aray $where
* Nombre de los campos que queremos mostrar en la cláusula
* <b><i>WHERE</i></b>.
* @param string $SQLserverName
* @return mixed array si hay un error. String columnas afectadas.
*/
function queryUpdate($conn, $tabla, $set, $where, $SQLserverName)
{
$updateString = "UPDATE {$tabla} SET ";
$primerElemento = true;
foreach ($set as $key => $value) {
if (strlen($value) == 0) {
unset($set[$key]);
continue;
}
if ($primerElemento) {
$updateString .= " {$key} = ?";
$primerElemento = false;
} else {
$updateString .= ", {$key} = ?";
}
}
$whereString = " WHERE ";
$primerElemento = true;
foreach ($where as $key => $value) {
if ($primerElemento) {
$whereString .= " ({$key} = ?)";
$primerElemento = false;
} else {
$whereString .= " and ({$key} = ?)";
}
}
$sql = $updateString . $whereString;
$parametros = array_merge($set, $where);
$parametros = array_values($parametros);
// Ejecutar la query
//////////////////////////////////////////////////////
if ($farmacia == FARMACIA_DEBUG or strlen(FARMACIA_DEBUG) == 0) {
if (DEBUG & DEBUG_QUERY) {
$mensaje = "--[" . date("c") . "] Farmacia: {$farmacia} \n";
$mensaje .= "--Query hecha en el fichero: " . __FILE__ . ", en la línea: " . __LINE__ . "\n";
error_log($mensaje . verQuery($sql, $parametros) . "\r\n", 3, DIRECTORIO_LOG . __FUNCTION__ . "_" . date("YmdH") . ".log");
}
}
$stmt = sqlsrv_query($conn, $sql, $parametros);
if ($stmt === false) {
if (($errors = sqlsrv_errors()) != null) {
$Merror = $errors[0]["message"];
/* Guardamos en un fichero el error y la query que lo ha generado para poder procesarlo después.
* Sólo en este caso no nos fijamos en el valor de la constante DEBUG ya que nos interesa guardarlo.*/
$mensaje = "--[" . date("c") . "] farmacia: {$farmacia} mensaje: {$Merror} \n";
$mensaje .= "--Error en el fichero: " . __FILE__ . ", en la línea: " . __LINE__;
error_log($mensaje . verQuery($sql, $parametros) . "\r\n", 3, DIRECTORIO_LOG . $tabla . "_" . $SQLserverName . "_" . date("YmdH") . ".log");
return $errors;
}
}
// Desplegar la información del resultado
//////////////////////////////////////////////////////
return sqlsrv_rows_affected($stmt);
}
/**
* This function initializes the class.
*
* @access public
* @override
* @param DB_Connection_Driver $connection the connection to be used
* @param string $sql the SQL statement to be queried
* @param integer $mode the execution mode to be used
* @throws Throwable_SQL_Exception indicates that the query failed
*
* @see http://php.net/manual/en/function.sqlsrv-query.php
*/
public function __construct(DB_Connection_Driver $connection, $sql, $mode = NULL)
{
$resource = $connection->get_resource();
$command = @sqlsrv_query($resource, $sql);
if ($command === FALSE) {
$errors = @sqlsrv_errors(SQLSRV_ERR_ALL);
$reason = (is_array($errors) and isset($errors[0]['message'])) ? $errors[0]['message'] : 'Unable to perform command.';
throw new Throwable_SQL_Exception('Message: Failed to query SQL statement. Reason: :reason', array(':reason' => $reason));
}
$this->command = $command;
$this->record = FALSE;
}
/**
* Helper method to turn sql server errors into exception.
*
* @return SQLSrvException
*/
public static function fromSqlSrvErrors()
{
$errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
$message = "";
foreach ($errors as $error) {
$message .= "SQLSTATE [" . $error['SQLSTATE'] . ", " . $error['code'] . "]: " . $error['message'] . "\n";
}
if (!$message) {
$message = "SQL Server error occured but no error message was retrieved from driver.";
}
return new self(rtrim($message));
}
public function StartDbConnection()
{
$this->SetServer();
$this->SetDatabase();
$this->SetConnectionString();
if ($this->connection) {
return true;
} else {
return false;
die(print_r(sqlsrv_errors(), true));
}
}
/**
* Executes the SQL query.
* @param string SQL statement.
* @return IDibiResultDriver|NULL
* @throws DibiDriverException
*/
public function query($sql)
{
$this->affectedRows = FALSE;
$res = sqlsrv_query($this->connection, $sql);
if ($res === FALSE) {
$info = sqlsrv_errors();
throw new DibiDriverException($info[0]['message'], $info[0]['code'], $sql);
} elseif (is_resource($res)) {
$this->affectedRows = sqlsrv_rows_affected($res);
return $this->createResultDriver($res);
}
}
/**
* Fügt einen neuen User der Person hinzu.
* @param string $username
* @param string $name
* @param string $surname
* @param string $mail
* @param string $password
* @param string $secret
* @return array|false|null
*/
public function update(string $username, string $name, string $surname, string $mail, $password, $secret)
{
$connection = Database::getConnection();
$hashedPassword = $password != null ? password_hash($password, PASSWORD_BCRYPT) : $this->session->getCurrentUser()['password'];
$personId = $this->session->getCurrentUser()['id_person'];
$query = "UPDATE person SET username = ?, password = ?, surname = ?, name = ?, mail = ?, secret = ? WHERE id_person = ?";
//Execute Query
sqlsrv_query($connection, $query, array($username, $hashedPassword, $surname, $name, $mail, $secret, $personId));
if (sqlsrv_errors()) {
http_response_code(500);
}
}
function initializeConnection()
{
$server = "tcp:s6ucn2tes4.database.windows.net";
$user = "******";
$pwd = "1Microsoft";
$db = "urimg";
global $conn;
$conn = sqlsrv_connect($server, array("UID" => $user, "PWD" => $pwd, "Database" => $db));
if (!isset($conn) && $conn === false) {
$sqlError = sqlsrv_errors();
return $this->returnCheck(false, $sqlError['message']);
}
}
public function __construct($database = "", $serverName = "PERSONAL-PC\\SQLEXPRESS", $userid = "sa", $password = "")
{
$this->dbname = $database;
$this->uid = $userid;
$this->pwd = $password;
$this->svr = $serverName;
$this->info = array("UID" => $this->uid, "PWD" => $this->pwd, "Database" => $this->dbname, "CharacterSet" => "UTF-8");
$this->conn = sqlsrv_connect($this->svr, $this->info);
if ($this->conn === false) {
echo "Unable to connect.</br>";
die(print_r(sqlsrv_errors(), true));
}
}
function StoreRecord($dto)
{
// Alterar apenas campos de usuário aqui (começados com U_ ), é proibido alterar campos do SAP B1
$query = "UPDATE OSLP SET U_SerializedData = " . $dto->serializedData . " WHERE SlpCode = " . $dto->slpCode;
$result = sqlsrv_query($this->sqlserverConnection, $query);
if ($result) {
return $dto->slpCode;
}
if (!$result && $this->showErrors) {
print_r(sqlsrv_errors());
echo '<br/>';
}
return null;
}
function fetchNRow($db, $query)
{
global $db;
$params = array();
$options = array("Scrollable" => SQLSRV_CURSOR_KEYSET);
$stmt = sqlsrv_query($db, $query, $params, $options);
$row_count = sqlsrv_num_rows($stmt);
if ($row_count === false) {
die(print_r(sqlsrv_errors(), true));
} else {
return $row_count;
}
return false;
}
