Ticket #153 (closed defect: fixed)

Opened 1 year ago

Last modified 5 months ago

sqlite select failure

Reported by: anonymous Assigned to: hans
Priority: minor Milestone: 2.3.0
Component: phing-tasks-ext Version: 2.3.0RC1
Keywords: Cc:

Description

The use of a select query with a SQLite db fails in PDOExecSQLtask: 

BUILD FAILED
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 cannot commit transaction - SQL statements in progress' in /usr/local/share/pear/phing/tasks/ext/PDOSQLExecTask.php:319
Stack trace:
#0 /usr/local/share/pear/phing/tasks/ext/PDOSQLExecTask.php(319): PDO->commit()
#1 /usr/local/share/pear/phing/Task.php(254): PDOSQLExecTask->main()
#2 /usr/local/share/pear/phing/Target.php(240): Task->perform()
#3 /usr/local/share/pear/phing/Target.php(263): Target->main()
#4 /usr/local/share/pear/phing/Project.php(709): Target->performTasks()
#5 /usr/local/share/pear/phing/Project.php(682): Project->executeTarget('test-pdo-sel')
#6 /usr/local/share/pear/phing/Phing.php(516): Project->executeTargets(Array)
#7 /usr/local/share/pear/phing/Phing.php(164): Phing->runBuild()
#8 /usr/local/share/pear/phing/Phing.php(262): Phing::start(Array, NULL)
#9 /usr/local/share/pear/phing.php(41): Phing::fire(Array)
#10 {main}

Total time: 0.2576 seconds

Adding this: 

$this->statement->closeCursor();

after: 

$this->log($this->statement->rowCount() . " rows affected", Project::MSG_VERBOSE);

in PDOSQLExecTask.php fixes the error, but it still isn't outputting the selected data.

Attachments

Change History

09/22/07 00:38:32 changed by albert

I did a little more work on this and noticed that by commenting out the $rs = null, it worked without the patch I included above.

09/25/07 20:35:40 changed by mrook

  • owner changed from hans to mrook.
  • status changed from new to assigned.

I'm not familiar with this task, but the "$rs = null" statement in printResults() should never be executed as that function is never called.

I can add the "closeCursor()" statement mentioned in the patch above.

10/04/07 20:17:12 changed by hans

  • milestone set to 2.3.0.

10/04/07 20:21:51 changed by hans

Albert, could you add a simple reproduce case here. Does this fail for you on any SQLite SELECT query?

10/04/07 21:47:40 changed by albertlash

Couple of notes - mrook - I commented out the lines that prevented printResults from getting called: 

            # FIXME - currently, this only works for update statements
            #if (!$this->statement->execute($sql)) {
                //echo "hie";
                //$this->statement->closeCursor();
                $this->statement = null;
                $this->statement = $this->conn->prepare($sql);
                //$this->statement->closeCursor();
                $this->statement->execute();
                $this->log($this->statement->rowCount() . " rows affected", Project::MSG_VERBOSE);
                $this->statement->closeCursor();
            #} else {
                if ($this->print) {
           //         echo "hie";

                    $this->printResults($out);
                }
            #}

You'll also notice I edited some of the $this->statement->closeCursor(); code. Here's the SQL SELECT I was running, very basic:

"SELECT bar,bar as blah from foo LIMIT 1"

I also varied that to some extent as I was trying to figure out what was going wrong, and verified that the SELECT query worked when called from the sqlite shell interface. I just reversed my edits to PDOSQLExecTask.php and was able to get the error again, so I believe that the correct fix is to add $this->statement->closeCursor(); to the line after 

$this->log($this->statement->rowCount() . " rows affected", Project::MSG_VERBOSE);

That should also fix the "# FIXME - currently, this only works for update statements" issue, when combined with the alteration of the $rs = null; statement.

I also had to add $this->statement = null; after the close of the while loop around line 512 to get the output working OK. See:

http://verens.com/archives/2006/10/19/pdosqlite-gotcha/

And finally I modified the fetch statement like this: 

            while ($row = $this->statement->fetch(PDO::FETCH_ASSOC)) {

but that should be configurable with a default setting.

10/09/07 17:34:31 changed by mrook

  • owner changed from mrook to hans.
  • status changed from assigned to new.

Reassigning this ticket to you Hans -- you are probably more knowledgeable regarding this task.

10/09/07 17:58:11 changed by hans

  • status changed from new to assigned.

Yeah, I'll take care of this. Thanks!

10/20/07 22:31:58 changed by hans

Can you tell me whether my changeset:256 fixes this ... I got a little puzzle by what you were doing exactly :) (Why don't you contact me off-list for an SVN account too, and you can commit what works for you directly.)

10/21/07 00:17:11 changed by anonymous

One change I introduced, that may be incorrect, was to check the SQL statement before deciding whether to print it out (i.e. only printing out SELECT statements). Maybe we always want to print output when print="true"?

10/23/07 02:19:20 changed by hans

  • status changed from assigned to closed.
  • resolution set to fixed.

I'm going to close this now, assuming it is fixed. See also #167 for follow-on work to this task.

01/10/08 06:17:29 changed by anonymous

风幕柜 熟食柜 点菜柜 蛋糕柜 thjnyjnyuym

Add/Change #153 (sqlite select failure)




Action