PDO Transaction fails on commit line

PHP
1

I want to create a transaction and run three sql statements and if all are successful, commit() the transaction, otherwise rollBack(). Here is what I am doing but am getting a message

Uncaught Error: Call to undefined method PDOStatement::commit()

Here is the code I am using (and it does execute ok for all three statements). The error is pointing to the commit line:

	try {
		$retval = true;
		$this->dbh->beginTransaction();
		
		$sth = $this->dbh->prepare($qt_sql);
		if ($sth->execute()) {

		
			$sth = $this->dbh->prepare($qt_sql2);
			if ($sth->execute()) {
		
				$sth = $this->dbh->prepare("optimize table pomaster");
				if ($sth->execute()) {
		
					$this->dbh->commit();
				} else {
					$this->dbh->rollBack();	
					$retval = false;
				}
			}	 else {
				$this->dbh->rollBack();	
					$retval = false;
			}
		}	 else {
			$this->dbh->rollBack();	
					$retval = false;
		}
2

The error message indicates you have a line of code - $sth->commit(); somewhere. The $sth values are PDOStatement objects.

Also, you would not prepare, then execute, the optimize table … query. You would just use the ->query() method.

The try and catch (which you didn’t post) implies you are using exceptions for PDO errors (which is the default setting now in php8+.) When using exceptions, you don’t use conditional logic around statements that can throw an exception, since an exception thrown by a statement transfers execution to the nearest correct type of catch block. With exceptions, your main inline code only ‘sees’ error free execution, so, you would unconditionally call the ->commit() method at the end of the queries, and call the ->rollback() method inside the catch block, simplifying all the code.

3

Got it. I was just reading that some statements cause an autocommit which I am now assuming the optimize statement is doing which is why there is no transaction active. And yes I am using a catch and will put the rollback there but while troubleshooting I moved it up top to see if that was it.

So long and short if I comment out the optimize statement it works fine and I will move the rollback down below again.

Thanks for the input. I appreciate it.

4

FYI I’m fairly certain a rollback after optimize table will not roll back to a point where the table was not optimized.

On InnoDB at least, optimize table does an alter table, and those live outside of transactions.

5

Ok, thanks. I’m now doing the optimize after the transaction has successfully completed and committed so all good I think.

1 Like
6

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.