i have this code so far for a procedure that gets an item by the id. Problem is i get this error
Incorrect number of FETCH variables
when i run this code
call getItemByID(@itemValues, 1)
and i can’t figure where that even is, and since this is fairly new i can not see a clear solution. if anyone has any idea, let me know.
DELIMITER $$
DROP PROCEDURE IF EXISTS `rpg`.`getItemById`$$
CREATE PROCEDURE `getItemById`(OUT itemValues INT, IN itemID INT )
BEGIN
declare a,b int;
declare cur_1 CURSOR FOR
select
a.item_name,
a.item_id,
b.item_type_name,
c.class_name
from
items a
left join
item_types b
on
( a.item_type = b.item_type_id )
left join
classes c
on
( c.class_id = a.item_class )
where
a.item_id = itemID;
declare continue handler for not found set b = 1;
OPEN cur_1;
repeat
fetch cur_1 into itemValues;
until b = 1
end repeat;
close cur_1;
END$$
DELIMITER ;
The number of expressions in the select list of the cursor declaration must match the number of target variables in the fetch statement. You have four expressions in the select list but only one variable in the fetch statement.
a single variable can not hold an entire result set. it can only hold a single value.
and why are you using a cursor? what you are trying to do does not require a cursor. try this instead:
DELIMITER $$
DROP PROCEDURE IF EXISTS `rpg`.`getItemById`$$
CREATE PROCEDURE `getItemById`(IN itemID INT )
BEGIN
select
a.item_name,
a.item_id,
b.item_type_name,
c.class_name
from
items a
left join
item_types b
on
( a.item_type = b.item_type_id )
left join
classes c
on
( c.class_id = a.item_class )
where
a.item_id = itemID;
END$$
DELIMITER ;
then you can process the result set just like the results of a normal query.
I need a cursor because the mysql c api does not allow that type of select. It needs to be in a cursor and then selected later. I was told by making it a cursor and then selecting the cursor i would then be able to get the results out. i wish i could do some type of result set output.
I am using the mysql c api for python to do this, but if i do the same thing in mysql command line it has the same error.
well yes i can do that select. but i can not retrive any of the results from that query using the api. i need to pass it through as a variable, and then select that variable after. I am pretty sure it is a bug in the api.
but the first query does not complete, so i can not guy why there is that error, so can anyone work with me to figure out what i need to do to fix the first procedure?
DELIMITER $$
DROP PROCEDURE IF EXISTS `rpg`.`getItemById`$$
CREATE PROCEDURE `getItemById`(IN itemID INT )
BEGIN
select
a.item_name,
a.item_id,
b.item_type_name,
c.class_name
from
items a
left join
item_types b
on
( a.item_type = b.item_type_id )
left join
classes c
on
( c.class_id = a.item_class )
where
a.item_id = itemID;
END$$
DELIMITER ;
this works, in mysql, but when i use it in python it gives me this mysql error
_mysql_exceptions.OperationalError: (1312, "PROCEDURE rpg.getItemByID can't return a result set in the given context"
hi, well i have read quite a lot about mysql stored procedures and i wanted to clear something out.
my question is suppose i have a webpage in php that needs to call a stored procedure and display returned result sets on the same page…
i am confused that where to put the “create” the stored procedure codes
-since the page call itself,if i put the “create” codes at the top of the page it will create the stored procedure again, will not it be a waste…
or should i check if it already exists…how to escape creating it again if it exists…
i am thinking to validate it i.e check if it exists and then just go to call the procedure below…
