stored procedures, error #1312, CLIENT_MULTI_RESULTS flag

Question

i am writing stored procedures in MySQL that return values;

CREATE PROCEDURE getCustomerById (id int)
BEGIN
 SELECT *
    FROM customer
 WHERE customer.id = id;
END;

and i get the error that the results cannot be shown in the given context.

after some googling, i think that i need to set the flag "CLIENT_MULTI_RESULTS" - i am connecting the database from JDBC using a java app, but cant find where to set it!

any suggestions?

Answer 1

try this

delimiter ;

drop procedure if exists getCustomerById;

delimiter #

create procedure getCustomerById
(
 in p_id int unsigned
)
begin
  select c.* from customer c where c.id = p_id;
end #

delimiter ;