List:MySQL on Win32« Previous MessageNext Message »
From:Joelle Tegwen Date:January 18 2006 10:42pm
Subject:looping results of prepared statements in a function
View as plain text  
After reading up on procedures and functions at 
http://dev.mysql.com/doc/refman/5.0/en/sqlps.html and 
http://mysql.gilfster.com/page.php?parent_id=1.3&page_id=1.3.6  for a 
couple of days I'm giving some stuff a shot.

What I need to be able to do is create a cursor dynamically. The 
documentation seems to suggest that in a procedure you need to use a 
prepared statement to do this, but then it's not clear how you loop it.

This is my function:
DELIMITER $$

DROP FUNCTION IF EXISTS `youthhood`.`getTablePrimaryKey` $$
CREATE FUNCTION `youthhood`.`getTablePrimaryKey` (sSchema VARCHAR(100), 
sTable VARCHAR(100)) RETURNS VARCHAR(200)
BEGIN
  declare bEndLoop int default 0;
  declare sKey, temp VARCHAR(200);
  declare continue handler for sqlstate '02000' set bEndLoop=1;

  set @schema=sSchema;
  set @table=sTable;

  set @sSql:= concat('SELECT *',
                      'FROM information_schema.TABLE_CONSTRAINTS T'
                      'WHERE table_schema=? AND constraint_type=PRIMARY 
KEY AND table_name=?');
  prepare rs from @sSql;
  execute rs using @schema, @table;


  repeat
    fetch rs into temp;
    if not bEndLoop then
      set sKey := concat(sKey, ", " temp)
    end if;

  until bEndLoop end repeat;

  deallocate prepare sSql;

  if length(sKey)>0 then sKey:=right(sKey, length(sKey)-2)

  return sKey
END $$

DELIMITER ;

I just want to get a comma delimited string of the Primary Key fields 
(and in a similar function the indexes) for a given table. So if there 
is a better way to do this in general, I'm totally open to that too. I'm 
pretty sure I'm in totally over my head so any help would be much 
appreciated.

Thanks in advance.
Joelle


Thread
looping results of prepared statements in a functionJoelle Tegwen18 Jan
  • Re: looping results of prepared statements in a functionJoelle Tegwen19 Jan