Re: looping results of prepared statements in a function

Never mind... I had an insight overnight about a better way to do the
whole process.

Sorry
Joelle

Joelle Tegwen wrote:

> 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
>
>
>


-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]