MySQL Stored Procedures

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.

So any other help would be appreciated though.

who told you that? your select statement looks fine to me. nothing weird about it.

Note: I am on MySQL 5.0.13-rc -nt

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.

so in python i have

query = "call getItemByID(@itemValues, "+str(itemID)+")"
  			print query
  			self.db.execute( str( query ) )
  			
  			query = "select @itemValues"
  			print query
  			itemvalues = self.db.execute( str( query ) )

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?

if you just use the query straight, without a stored procedure, does it work?

and in the example you posted, are you using your orignal SP, or the one i posted? if you’re using the one i posted, there’s only one parameter.

and i don’t know python, so you could be doing something wrong with python. what python package are you using to connect to the mysql server?

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"

I don’t know if this is an issue or not but the word IN is a reserved word in MySQL. Is naming one of your values IN causing the problem?

nope, because the reserved word IN is being used as a reserved word to identify the variable itemID as an input variable. :slight_smile:

i’ve done a little poking around and found that you might need to send the CLIENT_MULTI_RESULTS flag on your connection.

I will check it out when i get home ( at work ). Thanks for the help long neck :smiley:

the senior software dev told me i needed http://dev.mysql.com/doc/mysql/en/c-api-multiple-queries.html

CLIENT_MULTI_STATEMENTS

SO i added that and it worked. but i think multiple results would be needed for multiple results. from a call to be process. Thanks for the help :D.

How to Write Stored Procedure For Inserting And Updating And Deleting

and how to run these kind of Stored procedures and can u plzz explain me with Sample.

I’m very new to MySql. Now im comfortable with writing queries…
Now i’m learning to write Stored Procedures. Can u help me plzz.

next time, start your own thread, or we won’t answer your question.

see http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.pdf for a good tutorial on stored procedures

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…

can anyone know how to do that…

tos- delete your post and start a new thread.

i thought we could post related subjects in some threads…am sorry anyways…
am just a newbie…