Re: PDO_SQLite Transactions

I was indeed trying to update the record I just inserted, the thing is though, if I remove all other queries and keep just PDO->beginTransaction(); PDO->prepare(); PDOstatement->execute(); PDOstatement->closeCursor(); PDO->commit(); it still returns such an error. Which is very odd indeed... As for what I'm trying, it's true that this wasn't the best way of doing it, but there's actually more code involved with slightly different (read: more) changes being done and retrieved from various (non-PDO SQLite db) places. I've removed the Transactions and it works fine =/ - Tul Richard Lynch wrote:

I suspect that you are trying to update the record you just inserted. That may just not be possible in the transaction model, for whatever internal purposes. It also seems kind of backwards to me at least. Why not just do the file move and error checking before you even bother to insert, and then do a single statement, which won't even need a transaction, since any one statement is atomic. Perhaps I'm missing something, but it seems like you've needlessly complicated the DB side of things. On Sat, July 28, 2007 8:57 pm, M. Sokolewicz wrote:

I've been having this problem for a while now, when I use transactions in SQLite with PDO it stalls on me with an error stating my statements are in progress. I've tried closing all cursors to my statements, but that does not seem to resolve this for me. This is my code, (obviously shortened quite a bit) I hope someone could help me figure out what's going wrong, could it be that I can't run an update yet because the transaction has not been commited yet?? (sounds a bit odd to me) <?php // setup an SQLite db connection via PDO, this is in $db try { // $fileName, $fileDesc and $fileType are all existing, cleaned, checked values $statement = $db->prepare('INSERT INTO Files (file_name, file_desc, file_type, file_filename) VALUES (?, ?, ?, ?)'); $statement->execute(array($fileName, $fileDesc, $fileType, '')); $id = $db->lastInsertId(); $statement->closeCursor(); $res = move_uploaded_file($tmp_file, $new_file); if($res === false) {

	    $db->rollBack();

	    echo 'error';
	    exit;

} else {

	    $statement = $db->prepare('UPDATE Files SET file_filename=? WHERE

file_id=?');

	    $statement->execute(array($new_file, $id));

	    $statement->closeCursor();

	    OG::$db->commit();

} } catch(Exception $e) {

     var_dump($e);

} And the Exception I recieve (there is only one commit in the file, due to shortening for posting the line listed here is incorrect): object(PDOException)#7 (7) { ["message:protected"]=> string(88) "SQLSTATE[HY000]: General error: 1 cannot commit transaction - SQL statements in progress" ["string:private"]=> string(0) "" ["code:protected"]=> string(5) "HY000" ["file:protected"]=> string(43) "/home/tularis/public_html/t/createFile.php" ["line:protected"]=> int(95) ["trace:private"]=> array(1) {

     [0]=>
     array(6) {
       ["file"]=>
       string(43) "/home/tularis/public_html/t/createFile.php"
       ["line"]=>
       int(95)
       ["function"]=>
       string(6) "commit"
       ["class"]=>
       string(3) "PDO"
       ["type"]=>
       string(2) "->"
       ["args"]=>
       array(0) {
       }
     }

} ["errorInfo"]=> array(3) {

     [0]=>
     string(5) "HY000"
     [1]=>
     int(1)
     [2]=>
     string(54) "cannot commit transaction - SQL statements in

progress" } } -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php