0

I have the following SP:

CREATE PROCEDURE vessels_near_anchorage_test() 
   BEGIN 
      DECLARE arg0 VARCHAR(255);
      DECLARE arg1 FLOAT;
      DECLARE arg2 FLOAT;
      DECLARE arg3 FLOAT;
      DECLARE cur1 CURSOR FOR SELECT field1, field2 FROM table1;
      OPEN cur1; 
      read_loop: LOOP
           FETCH cur1 INTO arg0, arg1, arg2, arg3;
           SELECT * FROM table2 WHERE c1=arg0 AND c2=arg1;
      END LOOP;
      CLOSE cur1;
END;

When I try to result set from this SP in the application server, I get DataError: 1329 (02000): No data - zero rows fetched, selected, or processed. I want to understand how the SP is really being executed and what is causing this error.

In the SP above, the SELECT statement is inside the loop. It returns rows in some iterations while there may be no result in some iterations. When I run this in PhpMyAdmin, I get 850 rows, but it's entirely possible that the last iteration does not return any row.

  1. When a SELECT statement is executed in a loop like that, can I combine results from all iterations and output as a single result set available by the time the loop completes?
  2. For the application, is it only the result set from the last iteration in the loop made available which in this case has no rows?
  • Yes, in fact I didn't list the fields list to keep the query short, but I will update the question to reflect that. – kovac Dec 29 '17 at 14:23
  • 1
    I'm not a MySql pro, but I think you should store every step into a temp table and eventualy return a select * from temp_table. – McNets Dec 29 '17 at 14:32
  • Indeed this is what I just started doing! Thank you for the validation. – kovac Dec 29 '17 at 14:46
2

CURSORs are rarely worth using. Try to get rid of them. In your example:

SELECT b.*
    FROM table2 AS b
    JOIN table1 AS a  ON b.c1 = a.field1
                     AND b.c2 = a.field2

Anyway, when you execute multiple SELECTs inside a Stored Proc, you get "multiple resultsets". Some interfaces either can't handle such, or need different coding.

And you had an error: The cursor SELECT fetched 2 columns but the FETCH expected 4.

Coding questions should be addressed to stackoverflow.com

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.