Problems returning result set (many rows) from MySQL stored procedure

[mflanagan]

I have a MySql stored procedure that simply runs a SELECT that will return multiple rows. I'm calling it using PEAR:😃b->query. I get an error return:

[nativecode=1312 ** PROCEDURE neighborhoodserver.proc_Get_available can't return a result set in the given context]'

If I simply execute the SELECT statement through 'Db->query', it works fine. If I call the stored procedure from the command line, it works fine.

What am I missing? Thanks.

[Sxooter]

Hmmmm. Maybe PEAR:😃b doesn't know how to handle result sets from MySQL??? I'm just guessing. PEAR:😃b is an older interface. Have you tried using the newer PDO interface? Also, try it with a native msyql_query call and see if you can make that work. If the problem is in your mysql() functions, then PEAR:😃b won't be able to work, natch. If mysql_query works, then the problem is in PEAR😃b.

[mflanagan]

"Hmmmm," indeed, Sxooter! Thanks for the info.

I'm worried that you're correct. My job is to convert a 2003-era php/MySQL app to php 5, current MySQL, and also take most (all, if possible) of the knowledge of the db structure out of the php code and put it into stored procedures. If that's not possible, then at least take out all the knowledge of the db structure and isolate it in a class separate from the rest of the php code. The app can also run with MS SQL Server and Oracle (via config switches), hence their desire to use Pear:😃B (or something similar) instead of mysqli, et. al., to make switching a lot easier.

Well, I'll have to visit all the PEAR:😃B calls, anyway, so maybe now is the time to convert. How would you compare your suggested PDO to AdoDB? Any thoughts?

Thanks again.

[Sxooter]

Well, I haven't actually played much with PDO, and it's been a while since I did any kind of testing against AdoDB. I would say that in general, AdoDB is better tested, since it's been around a while. PDO is brand spaking new so expect to be helping the PDO folks thrash out the bugs if you start using it. OTOH, it's probably got plenty of active development going on, so if you found a bug it'd likely get fixed fairly fast.

Btw, if you already support MSSQL and Oracle, you might as well work on PostgreSQL as well. It's got stored procs (OK, they're User defined functions, but other than the strange select procname() calling convention, they're the same thing.) and has had triggers and UDFs for years now.

I'd definitely start low level and see if the standard PHP mysql interface can handle return sets, as well as the mysqli interface, to see where the problem is. I know someone else on this list had a problem with mysqli and resource leakage, whereas I've never had a problem with resource leakage with the plain mysql interface, so that leads me to believe that the mysqli interface may not be "done" just yet.

After figuring out which interface supports returning result sets, see if you can put ADODB or PDO on top of them. PEAR:😃b is the oldest of the abstraction models, and generally the least recommended nowadays. I'm guessing ADODB is likely to have the best combination of features and maturity for now. Look for PDO to pass it by in the next year or two, unless someone decides it's hopeless for some reason and development dies off. It's happened before, but I'm guessing there's too much momentum for PDO to just die off.

[mflanagan]

Good advice; thanks. I turned to PDO. I enabled the PDO extensions (PDO itself, PDO-MySQL, PDO-MSSQL), told it that I wanted to use MySQL, changed some of the calls, and voila! The stored procedure and its returned results set work fine.

Thanks again.

[Roger_Ramjet]

mflanagan wrote:

Good advice; thanks. I turned to PDO. I enabled the PDO extensions (PDO itself, PDO-MySQL, PDO-MSSQL), told it that I wanted to use MySQL, changed some of the calls, and voila! The stored procedure and its returned results set work fine.

Thanks again.

How did you get that working please. I just tried to use pdo and mysql stored proc and got nowhere, nothing returned. Now I read in the manual user notes that pdo this is the case and pdo will not return result sets from mysql stored procs. Googled around and all I found is blogs with moans about bug reports not being actioned.

So what am I missing please, how do I access the results from a stored proc that should return 3 out params?

[mflanagan]

I hat to say it, but I did just what I posted.

I did run into a problem on MS Windows. There's a reported problem whereby PDO/MySQL won't return multiple result sets. I'm running on *nix, so it's not a problem.

Sorry I can't be more help.

Michael

[Roger_Ramjet]

Some code PLEASE, cos I must be overlooking something very basic; but as I said I found lots of stuff sez NOT returning resultsets with PDO and MySQL stored procs.

[mflanagan]

Here's some code.

         $this->dbh = new PDO($this->dbDsn,
                     $user,
                     $password
                    );
...
      $query = "CALL proc_Get_coupon_views($deviceId,
                                           '$timeStartEncounter',
                                           $synthKey,
                                           $couponOfferKey);";
      $result = $this->dbh->query($query , PDO::FETCH_ASSOC);
...
      $rows = $result->fetchAll();
      foreach ($rows as $row) {...}

• Are you returning multiple result sets?

• Note that none of my sp's return values in any arguments. All return values are a result of a SELECT.

I hope this helps.

[Roger_Ramjet]

mflanagan wrote:

Note that none of my sp's return values in any arguments. All return values are a result of a SELECT.

Ah, maybe that's it. I was trying to return the arguments - you know 'SELECT ... INTO ...' - for a secure login system. Went with prepared statement just so I could move on.
I actually put this on the back-burner and went into site design, which is sooo time consumming and then got side tracked with js and my new blog.

Thanks for the reply, and the code.

[mflanagan]

Here's an alternative. Continue to use the SELECT of the 'answers', but just SELECT them. They get returned as a row. So, something such as:

   SELECT COUNT(*) INTO l_Count
     FROM Credential
     WHERE Credential.Credential_key = p_Phone_id AND
           Credential.Credential_type = 3;
...
      INSERT INTO Non_member_sequence () VALUES ();
...
      -- Now retrieve the new value.
      SELECT LAST_INSERT_ID() INTO l_Customer_key;
...
     SELECT l_Count, l_Customer_key;

Now, you'll get one row back, containing two fields: l_Count and l_Customer_key.