Cursor Already Opened or Allocated Issue

General discussion on PHP
Post Reply
jord1322
Posts: 36
Joined: Mon Nov 03, 2014 3:49 pm
Location: Concord, North Carolina

Cursor Already Opened or Allocated Issue

Post by jord1322 » Thu Sep 10, 2015 5:03 pm

Hello,

I'm having an issue when running a query on a remote server involving the cursor. The query typically works, however at times (not that infrequently) its fails and states "SQLSTATE=24502 SQLCODE=-502 Cursor SQLCURSOR000000001 already open or allocated". I see at IBM's site that it is trying to use the OPEN statement for a cursor that is already open and it says to fix this I need to ensure that it does not attempt to execute an OPEN on an already open cursor.

My problem is that I do not seem to know how to do this in PHP and DB2 SQL and I cannot seem to find anything about this other than in RPG or simply what the error means.

I do not have this problem in any of my non-remote queries, its is just when accessing a remote database. Any help is appreciated.
Thanks,
Jordon Greene
PHP Full Stack Developer
SHOE SHOW, Inc.

scottgcampbell
Posts: 187
Joined: Wed Apr 22, 2009 2:29 pm
Location: Edmonton, AB, Canada

Re: Cursor Already Opened or Allocated Issue

Post by scottgcampbell » Fri Sep 11, 2015 3:07 pm

Can you post some of the code that you are using?
Just wondering how you are connecting/preparing/fetching etc.

Scott

jord1322
Posts: 36
Joined: Mon Nov 03, 2014 3:49 pm
Location: Concord, North Carolina

Re: Cursor Already Opened or Allocated Issue

Post by jord1322 » Mon Sep 14, 2015 7:37 pm

Here is basically what I'm using, the only difference is that it is a series of methods in a class and there are comments in mine:

Code: Select all

$this->conn = db2_pconnect($this->database, $this->username, $this->password);

if($this->conn) {
	
	$this->result = db2_exec($this->conn, $query);
		
	if(strtoupper(trim($queryType)) == "SELECT") {
		
		if($this->result) {
			
			$this->data = array();
			while($this->row = db2_fetch_both($this->result)) {
				$this->data[] = $this->row;
			};
	
			db2_commit($this->conn);
		} else {
			$this->data = die("<br>bad select ".db2_stmt_errormsg());
		};
	} else {
		db2_commit($this->conn);
		$this->data = "";
	};
};

return $this->data;

db2_pclose($this->conn);
Thanks,
Jordon Greene
PHP Full Stack Developer
SHOE SHOW, Inc.

scottgcampbell
Posts: 187
Joined: Wed Apr 22, 2009 2:29 pm
Location: Edmonton, AB, Canada

Re: Cursor Already Opened or Allocated Issue

Post by scottgcampbell » Mon Sep 14, 2015 8:22 pm

I'm not sure if that is exactly the sequence of the code in your class but you are doing the close (db2_pclose) AFTER the return so it will never be called, you are also "dying" when there is no data returned.

Not sure that either of these are the cause but if you run a query that doesn't return anything then run another that would return results do you think it might be possible that the cursor is still open?

Scott

jord1322
Posts: 36
Joined: Mon Nov 03, 2014 3:49 pm
Location: Concord, North Carolina

Re: Cursor Already Opened or Allocated Issue

Post by jord1322 » Tue Sep 15, 2015 1:55 pm

The db2_pclose is actually called by its own method after the method containing the actual query execution is called. Is that a problem in general?

What do you mean by "dying" when no data is returned?
Thanks,
Jordon Greene
PHP Full Stack Developer
SHOE SHOW, Inc.

scottgcampbell
Posts: 187
Joined: Wed Apr 22, 2009 2:29 pm
Location: Edmonton, AB, Canada

Re: Cursor Already Opened or Allocated Issue

Post by scottgcampbell » Tue Sep 15, 2015 2:16 pm

I thought that might be in a function call.

In this section:

Code: Select all

} else {
         $this->data = die("<br>bad select ".db2_stmt_errormsg());
      };
http://php.net/manual/en/function.die.php
http://php.net/manual/en/function.exit.php

Sorry I read that wrong, I thought it was when it returns no data, but it is when there is an error running the statement, probably still want to do a pclose at that point as there is NO call to the function to do the pclose unless you have it in your descrtuctor.

Scott

Post Reply