Thanks,
The INTO clause I had totally disregarded...
And I could do this outside the database I just have the cmd line client
connected at all times anyway and like to quickly look up certain things...
On 10/16/08 4:14 PM, "Rob Wultsch" <[EMAIL PROTECTED]> wrote:
> 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)
-------------------------
Olaf Stein
DBA
Battelle Center for Mathematical Medicine
Nationwide Children's Hospital, The Research Institute
700 Children's Drive
43205 Columbus, OH
phone: 1-614-355-5685
cell: 1-614-843-0432
email: [EMAIL PROTECTED]
³I consider that the golden rule requires that if I like a program I must
share it with other people who like it.²
Richard M. Stallman
----------------------------------------- Confidentiality Notice:
The following mail message, including any attachments, is for the
sole use of the intended recipient(s) and may contain confidential
and privileged information. The recipient is responsible to
maintain the confidentiality of this information and to use the
information only for authorized purposes. If you are not the
intended recipient (or authorized to receive information for the
intended recipient), you are hereby notified that any review, use,
disclosure, distribution, copying, printing, or action taken in
reliance on the contents of this e-mail is strictly prohibited. If
you have received this communication in error, please notify us
immediately by reply e-mail and destroy all copies of the original
message. Thank you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
