looping results of prepared statements in a function

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/win32?unsub=gcdmw-win32@m.gmane.org

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/win32?unsub=gcdmw-win32@m.gmane.org