/**
* @param string $sql
* @param array $queryParams
*
* @return bool|\mysqli_result
* @throws Mysql
*/
public function execute(string $sql, $queryParams = [])
{
// log the query
$this->log_sql($sql, $queryParams);
// start sql transaction
$this->mysqli->begin_transaction();
// use cache to get prepared statement
$statement = $this->get_statement_from_sql($sql);
// bind params
if (is_array($queryParams) && !empty($queryParams)) {
$bindTypes = '';
foreach ($queryParams as $name => $value) {
$bindTypes .= static::get_bind_type($value);
}
$statement->bind_param($bindTypes, ...$queryParams);
}
// execute statement
if (!$statement->execute()) {
$this->mysqli->rollback();
throw new Mysql($statement->error, $statement->errno, null, $sql);
}
// commit this transaction
$this->mysqli->commit();
// save info for latest query
$this->insertId = $statement->insert_id;
$this->affectedRows = $statement->affected_rows;
return $statement->get_result();
}
/**
* rollback transaction
*/
public function rollback()
{
if ($this->connection === null) {
$this->open();
}
$this->connection->rollback();
$this->connection->autocommit(true);
}
function modificaCoche($coche)
{
//alert($coche->login);
global $servidor, $bd, $usuario, $contrasenia;
try {
@($db = new mysqli($servidor, $usuario, $contrasenia));
if (mysqli_connect_errno() != 0) {
throw new Exception('Error conectando:' . mysqli_connect_error(), mysqli_connect_errno());
}
$db->select_db($bd);
if ($db->errno != 0) {
throw new Exception('Error seleccionando bd:' . $db->error, $db->errno);
}
$consulta = "update coches set marca='" . $coche->marca . "', modelo='" . $coche->modelo . "', color='" . $coche->color . "' where matricula='" . $coche->matricula . "'";
if ($db->query($consulta) === false) {
throw new ExcepcionEnTransaccion();
}
$db->commit();
$db->close();
} catch (ExcepcionEnTransaccion $e) {
echo 'No se ha podido realizar la modificacion';
$db->rollback();
$db->close();
} catch (Exception $e) {
echo $e->getMessage();
if (mysqli_connect_errno() == 0) {
$db->close();
}
exit;
}
}
function modificaAlumno($alumno)
{
global $servidor, $bd, $usuario, $contrasenia;
try {
@($db = new mysqli($servidor, $usuario, $contrasenia));
if (mysqli_connect_errno() != 0) {
throw new Exception('Error conectando:' . mysqli_connect_error(), mysqli_connect_errno());
}
$db->select_db($bd);
if ($db->errno != 0) {
throw new Exception('Error seleccionando bd:' . $db->error, $db->errno);
}
$consulta = "update alumnos set password='******', nombre='" . $alumno->nombre . "', apellido='" . $alumno->apellido . "', dni='" . $alumno->dni . "', email='" . $alumno->email . "', telefono=" . $alumno->telefono . ", sexo='" . $alumno->sexo . "' where login='******'";
if ($db->query($consulta) === false) {
throw new ExcepcionEnTransaccion();
}
$db->commit();
$db->close();
} catch (ExcepcionEnTransaccion $e) {
echo 'No se ha podido realizar la modificacion';
$db->rollback();
$db->close();
} catch (Exception $e) {
echo $e->getMessage();
if (mysqli_connect_errno() == 0) {
$db->close();
}
exit;
}
}
/**
* Rolls back a transaction.
*
* @return Boolean
*/
public function rollback()
{
$this->connect();
if ($this->connected === TRUE) {
return $this->mysqli->rollback();
}
return FALSE;
}
function rollback()
{
if ($this->in_transaction > 0) {
parent::rollback();
$this->in_transaction = 0;
}
return $this->in_transaction;
}
public function rollback()
{
if ($this->logger != null)
{
$this->logger->log(Logger::LEVEL_TRACE, __FUNCTION__." called");
}
return $this->conn->rollback();
}
/**
* Rollback pending DB transactions.
*
* @return boolean true on success
*/
public function rollback()
{
$startTime = microtime(true);
$ret = $this->db->rollback();
//gather stats about queries
$this->addQueryTime(microtime(true) - $startTime);
return $ret;
}
/**
* Rollback (abort) the transaction
* @throws MysqltcsException on rollback error
*/
public function rollBack()
{
if ($this->mysqliRef->rollback()) {
$this->log("rollback");
return;
}
$mex = "Mysql error: it is not possible perform the rollback. " . $this->mysqliRef->error;
$this->log($mex);
throw new MysqltcsException($mex);
}
/**
* Rollback a transaction.
*
* @returns \Docnet\DB
* @throws \Exception if we're not in a transaction
* @throws \Exception if the driver reports that the rollback failed
*/
public function rollback()
{
if (!$this->bol_in_transaction) {
throw new \Exception("Not in a transaction, can't rollback");
}
if (!$this->obj_db->rollback()) {
throw new \Exception("MySQL failed to rollback the transaction");
}
$this->bol_in_transaction = false;
return $this;
}
/**
* Rollback
*
* @throws Exception\RuntimeException
* @return $this
*/
public function rollback()
{
if (!$this->resource) {
throw new Exception\RuntimeException('Must be connected before you can rollback.');
}
if (!$this->inTransaction) {
throw new Exception\RuntimeException('Must call commit() before you can rollback.');
}
$this->resource->rollback();
return $this;
}
public function rollback()
{
if ($this->transactionCount == 0) {
return false;
}
if ($this->transactionCount == 1) {
$result = @$this->mysqli->rollback();
} else {
$result = @$this->mysqli->query('ROLLBACK TO point' . ($this->transactionCount - 1));
}
$this->popTransaction();
return $result;
}
/**
* {@inheritDoc}
*/
public function rollback()
{
if (!$this->isConnected()) {
throw new Exception\RuntimeException('Must be connected before you can rollback.');
}
if (!$this->inTransaction) {
throw new Exception\RuntimeException('Must call beginTransaction() before you can rollback.');
}
$this->resource->rollback();
$this->resource->autocommit(true);
$this->inTransaction = false;
return $this;
}
/**
* Method to roll back a transaction.
*
* @param boolean $toSavepoint If true, rollback to the last savepoint.
*
* @return void
*
* @since 1.0
* @throws \RuntimeException
*/
public function transactionRollback($toSavepoint = false)
{
if (!$toSavepoint || $this->transactionDepth <= 1) {
$this->connect();
if ($this->connection->rollback()) {
$this->transactionDepth = 0;
}
return;
}
$savepoint = 'SP_' . ($this->transactionDepth - 1);
if ($this->executeUnpreparedQuery('ROLLBACK TO SAVEPOINT ' . $this->quoteName($savepoint))) {
$this->transactionDepth--;
}
}
function compraProdotto($id, $email)
{
$mysqli = new mysqli(DB_SERVER, DB_USER, DB_PASS, DB_NAME);
$mysqli->autocommit(false);
$add = $mysqli->query("INSERT INTO ordini (id_prodotto, nomeutente) VALUES ({$id}, '{$email}')");
$q = $mysqli->query("SELECT * FROM prodotti WHERE id={$id}");
if (!$add) {
$mysqli->rollback();
return false;
}
$prodottoSelezionato = mysqli_fetch_assoc($q);
if ($prodottoSelezionato['quantita'] == 0) {
$mysqli->rollback();
return false;
}
$nuovaQuantita = $prodottoSelezionato['quantita'] - 1;
$u = $mysqli->query("UPDATE prodotti SET quantita = {$nuovaQuantita} WHERE id={$id}");
if ($u) {
$mysqli->commit();
$mysqli->autocommit(true);
return true;
}
}
/**
* @param mysqli $db
* @param int $epoch
*/
function cleanChallenges($db, $epoch = NULL)
{
global $MAXCHALLENGELIFETIME;
if ($epoch === NULL) {
$epoch = time();
}
$cutoff = $epoch - $MAXCHALLENGELIFETIME;
if ($stmt = $db->prepare('DELETE FROM challenges WHERE `epoch` < ?')) {
$stmt->bind_param("i", $cutoff);
if ($stmt->execute()) {
$db->commit();
} else {
$db->rollback();
// Don't report, this is just maintenance.
}
}
}
/**
* Transaction rollback function
*
* @uses mysqli->rollback();
* @uses mysqli->autocommit(true);
*/
public function rollback()
{
$this->_mysqli->rollback();
$this->_transaction_in_progress = false;
$this->_mysqli->autocommit(true);
}
function save_image()
{
include "dbconfig.php";
// normally this info would be pulled from a database.
// build JSON array.
// 1. 업로드 파일 존재여부 확인
if (!isset($_FILES['image'])) {
exit("업로드 파일 존재하지 않음");
}
// if
// 2. 업로드 오류여부 확인
if ($_FILES['image']['error'] > 0) {
switch ($_FILES['image']['error']) {
case 1:
exit("php.ini 파일의 upload_max_filesize 설정값을 초과함(업로드 최대용량 초과)");
case 2:
exit("Form에서 설정된 MAX_FILE_SIZE 설정값을 초과함(업로드 최대용량 초과)");
case 3:
exit("파일 일부만 업로드 됨");
case 4:
exit("업로드된 파일이 없음");
case 6:
exit("사용가능한 임시폴더가 없음");
case 7:
exit("디스크에 저장할수 없음");
case 8:
exit("파일 업로드가 중지됨");
default:
exit("시스템 오류가 발생");
}
// switch
}
// if
// 3. 업로드 제한용량 체크(서버측에서 8M로 제한)
if ($_FILES['image']['size'] > 8388608) {
exit("업로드 최대용량 초과");
}
// if
// 4. 업로드 허용 확장자 체크(보편적인 jpg,jpeg,gif,png,bmp 확장자만 필터링)
$ableExt = array('jpg', 'jpeg', 'gif', 'png', 'bmp');
$path = pathinfo($_FILES['image']['name']);
$ext = strtolower($path['extension']);
if (!in_array($ext, $ableExt)) {
exit("허용되지 않는 확장자입니다.");
}
// if
// 5. MIME를 통해 이미지파일만 허용(2차 확인)
// $ableImage = array('image/jpeg', 'image/JPG', 'image/X-PNG', 'image/PNG', 'image/png', 'image/x-png', 'image/gif','image/bmp','image/pjpeg');
// if(!in_array($_FILES['image']['type'], $ableImage)) {
// exit("지정된 이미지만 허용됩니다.");
// }//if
// 6. DB에 저장할 이미지 정보 가져오기(width,height 값 활용)
$img_size = getimagesize($_FILES['image']['tmp_name']);
// DB연결
$mysqli = new mysqli($dbhost, $dbusr, $dbpass, $dbname);
if ($mysqli->connect_errno) {
echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}
$create_table = "CREATE TABLE if not exists image_files( \n\t\t\t\t\tid int auto_increment, \n\t\t\t\t\tupload_filename varchar(100), \n\t\t\t\t\tdb_filename varchar(100), \n\t\t\t\t\tfilepath varchar(100), \n\t\t\t\t\tfilesize int(8),\n\t\t\t\t\tfile_type VARCHAR(40),\n\t\t\t\t\tupload_date DATETIME DEFAULT CURRENT_TIMESTAMP,\n\t\t\t\t\twidth int(8), \n\t\t\t\t\theight int(8), \n\t\t\t\t\tPRIMARY KEY (id) \n\t\t\t\t\t);";
$mysqli->query($create_table);
// do~while: 새로만든 파일명이 중복일경우 반복하기 위한 루틴
do {
// 6. 새로운 파일명 생성(마이크로타임과 확장자 이용)
$time = explode(' ', microtime());
$fileName = $time[1] . substr($time[0], 2, 6) . '.' . strtoupper($ext);
// 중요 이미지의 경우 웹루트(www) 밖에 위치할 것을 권장(예제 편의상 아래와 같이 설정)
$filePath = $_SERVER['DOCUMENT_ROOT'] . '/web_test/image_test/upload_image/';
// 7. 생성한 파일명이 DB내에 존재하는지 체크
$query = sprintf("SELECT no FROM image_files WHERE db_filename = '%s'", $fileName);
$result = $mysqli->query($query);
if ($result === NULL) {
break;
}
// 생성한 파일명이 중복하는 경우 새로 생성해서 체크를 반복(동시저장수가 대량이 아닌경우 중복가능 희박)
} while ($result != NULL && $result->num_rows > 0);
// db에 저장할 정보 가져옴
$upload_filename = $mysqli->real_escape_string($_FILES['image']['name']);
$file_size = $_FILES['image']['size'];
$file_type = $_FILES['image']['type'];
$upload_date = date("Y-m-d H:i:s");
// $ip = $_SERVER['REMOTE_ADDR'];
// 오토커밋 해제
$mysqli->autocommit(false);
// 8. db에 업로드 파일 및 새로 생성된 파일정보등을 저장
$query = sprintf("INSERT INTO image_files\n\t\t(upload_filename,db_filename,filepath,filesize,file_type,upload_date,width,height) \n\t\tVALUES ('%s','%s','%s','%s','%s','%s',%d,%d)", $upload_filename, $fileName, $filePath, $file_size, $file_type, $upload_date, $img_size[0], $img_size[1]);
$mysqli->query($query);
if ($mysqli->error) {
echo "Failed to insert image db: (" . $mysqli->error . ") ";
}
$insert_id = $mysqli->insert_id;
// DB에 파일내용 저장 성공시
if ($mysqli->affected_rows > 0) {
// 9. 업로드 파일을 새로 만든 파일명으로 변경 및 이동
if (move_uploaded_file($_FILES['image']['tmp_name'], $filePath . $fileName)) {
// 10. 성공시 db저장 내용을 적용(커밋)
$mysqli->commit();
} else {
// 실패시 db에 저장했던 내용 취소를 위한 롤백
$mysqli->rollback();
exit("업로드 실패");
}
// if
}
// if
$image_saving_info = array("id" => $insert_id);
$mysqli->close();
return $image_saving_info;
}
public function rollback() {
parent::rollback();
$this->autocommit(true);
}
}
$result = $conn->query($sql);
while ($row = $result->fetch_assoc()) {
$child_name_id = $row["child_name_id"];
$html_class = $row["html_class"];
$html .= formElemCreate($child_name_id, $html_class);
}
$id_second_part = idFromFirstOptions($html);
$id = idWrapAdd("{$id_first_part} {$id_second_part}");
$def = definitionCreate($id);
$filter_type = $def->info->filter_type;
if (count($error_arr) === 0) {
$conn->commit();
$status = "success";
} else {
$conn->rollback();
$status = "failure";
}
$output = new stdClass();
$output->html = $html;
$output->status = $status;
$output->errorArr = $error_arr;
// $output->debugSQL = $sql;
$output->id = $id;
$output->filter_type = $filter_type;
echo json_encode($output);
$conn->close();
?>
/**
* {@inheritDoc}
*/
protected function realRollback()
{
$this->link->rollback();
$this->link->autocommit(true);
return $this;
}
/**
* Update the credentials for the given user.
* @author pdvrieze
* @param mysqli $db The database connection.
* @param string $user The user whose password to update.
* @param string $newpassword The new password
*/
function updateCredentials($db, $user, $newpassword)
{
$passwordhash = createPasswordHash($password);
if ($stmt = $db->prepare('UPDATE `users` SET `password` = ? WHERE `user` = ?')) {
if (!$stmt->bind_param("ss", $passwordhash, $user)) {
handleError($db->error);
}
if ($stmt->execute() !== False) {
$db->commit();
return TRUE;
} else {
$db->rollback();
handleError("Error updating password");
}
}
}
function dryRun($filename)
{
global $servername, $username, $password, $dbname;
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error . "\n\n");
}
echo "Connected successfully\n";
echo "Turning Auto Commit off for Dry Run\n";
$conn->autocommit(FALSE);
$file = fopen($filename, "r");
// Parsing the CSV file
while (!feof($file)) {
// Preprocessing data to meet standard before insertion
$line = fgetcsv($file);
$firstName = str_replace('\'', '\'\'', preg_replace('/\\s+/', '', ucwords(strtolower($line[0]))));
$lastName = str_replace('\'', '\'\'', preg_replace('/\\s+/', '', ucwords(strtolower($line[1]))));
$emailAddress = str_replace('\'', '\'\'', preg_replace('/\\s+/', '', $line[2]));
// Checking email address validity
if (filter_var($emailAddress, FILTER_VALIDATE_EMAIL)) {
echo "This ({$emailAddress}) email address is considered valid.\n";
$sql = "INSERT INTO users (name, surname, email) \n VALUES ('{$firstName}', '{$lastName}', '{$emailAddress}')";
// Execute SQL statement
if ($conn->query($sql) === TRUE) {
echo "New record created successfully\n";
} else {
echo "Error: " . $sql . "<br>" . $conn->error . "\n\n";
}
// Rollback changes
$conn->rollback();
echo "New record insertion rolled back\n";
} else {
echo "({$emailAddress}) is not valid\n";
}
}
fclose($file);
$conn->autocommit(TRUE);
$conn->close();
}
/**
* Roll back a transaction and return to autocommit mode.
*
* @return Adapter
*/
public function rollBack($flags = NULL, $name = NULL)
{
if (!$this->_isConnected) {
$this->_connect();
}
if ($this->_profiler) {
$q = $this->_profiler->queryStart('rollback', Profiler::TRANSACTION);
}
parent::rollback($flags, $name);
if ($this->_profiler) {
$this->_profiler->queryEnd($q);
}
return $this;
}
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit;
}
/* disable autocommit */
$mysqli->autocommit(FALSE);
$mysqli->query("CREATE TABLE myCity LIKE City");
$mysqli->query("ALTER TABLE myCity Type=InnoDB");
$mysqli->query("INSERT INTO myCity SELECT * FROM City LIMIT 50");
/* commit insert */
$mysqli->commit();
/* delete all rows */
$mysqli->query("DELETE FROM myCity");
if ($result = $mysqli->query("SELECT COUNT(*) FROM myCity")) {
$row = $result->fetch_row();
printf("%d rows in table myCity.\n", $row[0]);
/* Free result */
$result->close();
}
/* Rollback */
$mysqli->rollback();
if ($result = $mysqli->query("SELECT COUNT(*) FROM myCity")) {
$row = $result->fetch_row();
printf("%d rows in table myCity (after rollback).\n", $row[0]);
/* Free result */
$result->close();
}
/* Drop table myCity */
$mysqli->query("DROP TABLE myCity");
$mysqli->close();
/**
* @param mysqli $db
* @param string $error
* @param int $code
* @param string $status
*/
function dbError($db, $error = "Database error", $code = 500, $status = "Server error")
{
$dberror = $db->error;
$db->rollback();
$db->close();
handleError($error . ': ' . $dberror);
}
@($db = new mysqli($servidor, $usuario, $contrasenia));
if (mysqli_connect_errno() != 0) {
throw new Exception('Error conectando:' . mysqli_connect_error(), mysqli_connect_errno());
}
$db->select_db($bd);
if ($db->errno != 0) {
throw new Exception('Error seleccionando bd:' . $db->error, $db->errno);
}
$consulta = "update coches set marca='" . $marca . "', modelo='" . $modelo . "', color='" . $color . "' where matricula='" . $matricula . "'";
if ($db->query($consulta) === false) {
throw new ExcepcionEnTransaccion();
}
$db->commit();
header("Location:../../index.php?controlador=coches&accion=listar");
$db->close();
} catch (ExcepcionEnTransaccion $e) {
echo 'No se ha podido realizar la modificacion';
$db->rollback();
$db->close();
} catch (Exception $e) {
echo $e->getMessage();
if (mysqli_connect_errno() == 0) {
$db->close();
}
exit;
}
}
?>
</body>
</html>
public function rollback()
{
return static::$db->rollback();
}
/**
* {@inheritdoc}non-PHPdoc)
*/
public function rollBack()
{
return $this->_conn->rollback();
}
function sqlO($sql, $parameters = [])
{
static $mysqli;
global $sqlconn;
#$_ENV['db'][]=&$ret;
if ($sql == 'close') {
if ($mysqli) {
$mysqli->close();
$mysqli = null;
}
return 1;
}
if (!$mysqli) {
sqlp('close');
#$class=new ReflectionClass('mysqli');$mysqli2=$class->newInstanceArgs($sqlconn);
$mysqli = new mysqli($sqlconn[0], $sqlconn[1], $sqlconn[2], $sqlconn[3]);
$mysqli->select_db($sqlconn[3]);
#print_r(compact('mysqli','mysqli2'));
}
if (!$mysqli || !$mysqli->stat) {
return ['#' => [__LINE__, 'no connection']];
}
$mysqli->autocommit(0);
$ok = 1;
if (!is_array(reset($parameters))) {
$sql = [$sql];
$parameters = [$parameters];
} elseif (is_array(reset($parameters)) && !is_array($sql)) {
$sql = array_fill(0, count($parameters), $sql);
}
#single sql multilple values
try {
#print_r($sql);
foreach ($sql as $k => $query) {
$ret[$k] = null;
$fakeKeys = [];
$types = '';
$params = [&$types];
$param = $parameters[$k];
if (!($stmt = $mysqli->prepare($query))) {
$ret['#'] = 1;
$line = __LINE__;
$error = $mysqli->error;
$ret[$k] = compact('line', 'error');
$ok = 0;
continue;
}
if (count($param)) {
foreach ($param as $k2 => $v) {
if (is_numeric($k2)) {
#bind to vars
unset($param[$k2]);
$i = $k2;
$k2 = 'z' . $i;
while (in_array($k2, $fakeKeys)) {
$i++;
$k2 = 'z' . $i;
}
$param[$k2] = $v;
}
if (is_integer($v)) {
$types .= 'i';
} elseif (is_double($v)) {
$types .= 'n';
} elseif (is_string($v)) {
$types .= 's';
} else {
$types .= 's';
}
${$k2} = $v;
$params[$k2] =& ${$k2};
}
if (!($x = call_user_func_array([$stmt, 'bind_param'], $params))) {
$ret['#'] = 1;
$ret[$k] = [__LINE__] + compact('x', 'params', 'param', 'z0');
$ok = 0;
continue;
}
}
if (!$stmt->execute()) {
$ret['#'] = 1;
$ret[$k] = [__LINE__, $query, $param, $mysqli->error];
$ok = 0;
} else {
if (stripos($query, 'select ') > -1) {
preg_match('#select (.*) from#i', $query, $m);
if (!$m[1]) {
$ret['#'] = 1;
$ret[$k] = [__LINE__, $query, 'cant match fields'];
continue;
}
$x = explode(',', trim($m[1]));
foreach ($x as $v) {
$fields[$v] =& ${$v};
}
# dynamic mysqli_stmt_bind_result && mysqli_stmt_fetch
if (!call_user_func_array([$stmt, 'bind_result'], $fields)) {
$ret['#'] = 1;
$ret[$k] = [__LINE__, $query, 'cant bind params'];
continue;
}
while (mysqli_stmt_fetch($stmt)) {
$t = [];
foreach ($fields as $k3 => $v) {
$t[$k3] = $v;
}
$res[] = $t;
#foreach($fields as $v)$v=null;
}
if (count($res) == 1) {
$res = reset($res);
}
$ret[$k] = $res;
} elseif (stripos($query, 'insert into ') > -1) {
$ret[$k] = $mysqli->insert_id;
} else {
$ret[$k] = $mysqli->affected_rows;
}
#update or delete
}
$stmt->close();
}
$ok ? $mysqli->commit() : null;
#$mysqli->rollback()
#$mysqli->autocommit(1);
} catch (exception $retrr) {
$mysqli->rollback();
$ret['#'] = 1;
}
return $ret;
}