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 [at] schema=sSchema;
set [at] table=sTable;
set [at] sSql:= concat('SELECT *',
'FROM information_schema.TABLE_CONSTRAINTS T'
'WHERE table_schema=? AND constraint_type=PRIMARY
KEY AND table_name=?');
prepare rs from [at] sSql;
execute rs using [at] schema, [at] 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 [at] m.gmane.org
