On Thu, Oct 16, 2008 at 12:31 PM, Olaf Stein
<[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I am running into some issues with what I am trying to do in a stored proc.
> Basically I am trying to find records related to certain individuals in
> other tables in the databases and if there are any, tell me how many.
>
> Instead of doing this for each of these tables individually I use a cursor:
>
> DECLARE tnames CURSOR FOR select table_name from information_schema.tables
> where table_schema='agpv2' and table_name like 'ad%' and table_name not like
> '%headers' order by table_name desc;
>
> to get all the tables I need.
>
> Now I loop over the result set of this cursor and want to dynamically insert
> the tablename into a second cursor. From what I read (and tried) that does
> not work:
>
> OPEN tnames;
> REPEAT
> FETCH tnames INTO tablename;
> IF NOT tnames_done THEN
> SELECT tablename;
> BEGIN
> DECLARE resadi INT;
> DECLARE adi_done INT DEFAULT 0;
> DECLARE adi CURSOR FOR select count(*) from tablename a join
> individual i on a.ident=i.ident where fid=agpfid;
>
> OPEN adi;
> FETCH adi INTO resadi;
> CLOSE adi;
>
> IF (resadi>0) THEN
> select resadi as adi_wps;
> END IF;
>
> The cursor does not use tablename as a variable.
>
>
> What does work is using prepared statements:
>
> SET @stmt_text=CONCAT("select count(*) from ",tablename," a join
> individual i on a.ident=i.ident where fid=",agpfid);
> PREPARE stmt FROM @stmt_text;
> EXECUTE stmt;
> DEALLOCATE PREPARE stmt;
>
> The problem with this is that I only want the result of the query if
> count(*) > 0 as there are many tables I am looking in and most have no
> reference to individual so I do not want them in the output and this just
> executes the statement.
>
> Is there any way I can dynamically manipulate the string fro the cursor.
> Or, is there anyway I can catch the EXECUTE stmt output and look at it
> before outputting it?
>
> Thanks
> Olaf
>
>
>
> Here is the full proc as I would like it to work:
>
>
> DELIMITER //
> DROP PROCEDURE IF EXISTS show_pheno//
> CREATE PROCEDURE show_pheno(agpfid INT)
> READS SQL DATA
> SQL SECURITY INVOKER
> COMMENT 'shows phenotypes for given family id'
> BEGIN
> BEGIN
> DECLARE tablename TEXT;
> DECLARE tnames_done INT DEFAULT 0;
> DECLARE tnames CURSOR FOR select table_name from information_schema.tables
> where table_schema='agpv2' and table_name like 'ad%' and table_name not like
> '%headers' order by table_name desc;
> DECLARE CONTINUE HANDLER FOR NOT FOUND SET tnames_done=1;
> OPEN tnames;
> REPEAT
> FETCH tnames INTO tablename;
> IF NOT tnames_done THEN
> SELECT tablename;
> BEGIN
> DECLARE resadi INT;
> DECLARE adi_done INT DEFAULT 0;
> SET @tn = tablename;
> DECLARE adi CURSOR FOR select count(*) from a join individual i on
> a.ident=i.ident where fid=agpfid;
>
> OPEN adi;
> FETCH adi INTO resadi;
> CLOSE adi;
>
> IF (resadi>0) THEN
> select resadi as adi_wps;
> END IF;
>
>
> END;
> END IF;
> UNTIL tnames_done
> END REPEAT;
> CLOSE tnames;
> END;
>
> END //
A couple of thoughts, and please bear in mind that you probably know
more about stored than I do.
1. Would this be better dealt with by BASH? You could look at the
results and act accordingly much more easily with BASH/Perl/whatever.
2. Why not fetch the prepared statement into a udv?
would become
SET @stmt_text=CONCAT("select count(*) INTO resadi from
",tablename," a join
individual i on a.ident=i.ident where fid=",agpfid);
...
you could then test resadi as shown.
I hope I am not adding to the confusion,
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
