i had this same problem when trying to do a select directly after an insert
w/ pdo + sqlite3 transactions.
solution in that case was to use last_insert_id (or whatever the actual pdo
method is called, cant remember
and too lazy to lookup, atm :))
anyway if you want to do an update after an insert just use a trigger.
-nathan
On 7/29/07, M. Sokolewicz <tularis@xxxxxxx> wrote:
>
> 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
> >>
> >>
> >
> >
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
