Fatal error: Uncaught exception 'PDOException' with message 'There is no active transaction' in

[terungwa]

Here's my example of using transactions in PDO:

I am getting the two errors below when I run my scripts in the browser.

1. PDOException: There is no active transaction in......
2. Fatal error: Uncaught exception 'PDOException' with message 'There is no active transaction' in ......

here is ny pdo database connection class:

<?php
class conn
{
	public $host = '';
	public $dbname = '';
	public $username = '';
	public $password = '';
    /**
     * @var object $db_connection The database connection
     */
    private $db_connection = null;
	
	public function __construct($host, $dbname, $username, $password)
	{
		$this->host = $host;
		$this->dbname = $dbname;
		$this->username = $username;
		$this->password = $password;
	}
	public function connected()
	{
		try 
		{
			$this->db_connection = @new PDO('mysql:host='.$this->hos.';dbname='.$this->dbname.';charset=utf8mb4', $this->username, $this->password);
			return $this->db_connection;
		} 
		catch (PDOException $e) 
		{
			echo "Unable to connect to the PDO database: " . $e->getMessage(); 
		}
	}
}

And below is the database queries:

<?php
require('config/conn.php');
$host = 'localhost';
$dbname = 'dbname';
$username = 'username';
$password = 'password';
$db = new conn($host, $dbname, $username, $password);		
try {
   //note that calling beginTransaction() turns off auto commit automatically
   $db->connected()->beginTransaction();
   $stmt = $db->connected()->prepare("INSERT INTO category_types (name, cat_id) VALUES (:name, :value)");
   $stmt->bindParam(':name', $name);
   $stmt->bindParam(':value', $value);
   
   // insert one row
   $name = 'one';
   $value = 1;
   $stmt->execute();
   
   // insert another row with different values
   $name = 'two';
   $value = 2;
   $stmt->execute();
   
 
   $stmt = $db->connected()->prepare("INSERT INTO category_types2 (name, cat_id) VALUES (:name, :value)");
   $stmt->bindParam(':name', $name);
   $stmt->bindParam(':value', $value);
   
   // insert one row
   $name = 'one';
   $value = 1;
   if($stmt->execute())   
   
   //all went well commit!
   $db->connected()->commit();

 
} catch (Exception $e) {
//Something went wrong rollback!
  $db->connected()->rollBack();
  echo "Failed: " . $e->getMessage();
}

What might I be doing wrong?

 

Thanks

[Muddy_Funster]

 

@new PDO('mysql:host='.$this->hos.';dbname='.$this

 

first up, don't suppress the instantiation of the class with an @ symbol, second of all, $this->hos is missing a "t"

[terungwa]

@new PDO('mysql:host='.$this->hos.';dbname='.$this

first up, don't suppress the instantiation of the class with an @ symbol, second of all, $this->hos is missing a "t"

 

Thank you Muddy_Funster,

I have effected the synthax corrections (

$this->db_connection = new PDO('mysql:host='.$this->host.';dbname='.$this->dbname.';charset=utf8mb4', $this->username, $this->password);

) but the errors still persit.

[mac_gyver]

each call/reference to - $db->connected() is creating a new database connection, which doesn't know anything about anything from any previous call. any transaction you start is only available on the database connection where it was started.

 

you need to make one database connection, in the constructor in your conn class.

[terungwa]

each call/reference to - $db->connected() is creating a new database connection, which doesn't know anything about anything from any previous call. any transaction you start is only available on the database connection where it was started.

 

you need to make one database connection, in the constructor in your conn class.

Thank you mac_gyver, your observation is valid, on each page reload an instance of the class is created and a call to the connected() method creates a new database connection and as you observed, this doesn't know anything about anything from any previous call.

 

What eventually worked was to check for an existing database connection each time the connected() method is called(using if/else statement).

	public function connected()
	{
		if ($this->db_connection) 
		{
			return $this->db_connection;
		}
        else
		{
			try 
			{
			
				return $this->db_connection = new PDO('mysql:host='.$this->host.';dbname='.$this->dbname.';charset=utf8mb4',
							   $this->username, $this->password);
			} 
			catch (PDOException $e) 
			{
				echo "Unable to connect to the PDO database: " . $e->getMessage(); 
			}
		}
	}