Good point, I do however need this for simpliefied display purposes only, the information in these tables I do otherwise not need for anything. But I will keep this in mind next time I have a similar issue.
Thanks Olaf On 10/17/08 9:09 AM, "Martin Gainty" <[EMAIL PROTECTED]> wrote: > Good Morning Olaf- > > the only reason why I bring this up is a materialized view is based on > contents of the originally queried tables > If you create a temporary table instead of a materialized view > you have no ability to synchronise the contents of the materialized view with > the originally queried tables > after session is complete the temporary table disappears > > Martin Gainty > ______________________________________________ > Disclaimer and confidentiality note > Everything in this e-mail and any attachments relates to the official business > of Sender. This transmission is of a confidential nature and Sender does not > endorse distribution to any party other than intended recipient. Sender does > not necessarily endorse content contained within this transmission. > > >> > Date: Thu, 16 Oct 2008 16:46:35 -0400 >> > Subject: Re: Stored proc - dynamic sql in cursor >> > From: [EMAIL PROTECTED] >> > To: [EMAIL PROTECTED]; mysql@lists.mysql.com >> > >> > The only one that comes to mind is that I do not see the tmp table at any >> > time, which is nice... >> > The view would show up in a show tables >> > >> > >> > On 10/16/08 4:45 PM, "Martin Gainty" <[EMAIL PROTECTED]> wrote: >> > >>> > > >>> > > any reason for going with Temp Tables over materialized View which can be >>> > > periodically refreshed from DB contents? >>> > > http://dev.mysql.com/doc/refman/6.0/en/create-view.html >>> > > >>> > > thanks, >>> > > Martin >>> > > ______________________________________________ >>> > > Disclaimer and confidentiality note >>> > > Everything in this e-mail and any attachments relates to the official >>> business >>> > > of Sender. This transmission is of a confidential nature and Sender does not >>> > > endorse distribution to any party other than intended recipient. Sender >>> does >>> > > not necessarily endorse content contained within this transmission. >>> > > >>> > > >>>>> > >> > Date: Thu, 16 Oct 2008 16:14:39 -0400 >>>>> > >> > Subject: Re: Stored proc - dynamic sql in cursor >>>>> > >> > From: [EMAIL PROTECTED] >>>>> > >> > To: [EMAIL PROTECTED]; mysql@lists.mysql.com >>>>> > >> > >>>>> > >> > I found a post suggesting to use the cursor to select from a >>>>> temporary >>>> > >> table >>>>> > >> > that is created dynamically each time >>>>> > >> > This seems to work... >>>>> > >> > >>>>> > >> > >>>>> > >> > DECLARE adi CURSOR FOR select count(*) from t; >>>>> > >> > >>>>> > >> > SET @stmt_text=CONCAT("drop temporary table if exists t"); >>>>> > >> > PREPARE stmt FROM @stmt_text; >>>>> > >> > EXECUTE stmt; >>>>> > >> > DEALLOCATE PREPARE stmt; >>>>> > >> > >>>>> > >> > >>>>> > >> > SET @stmt_text=CONCAT("create temporary table t as select >>>>> i.ident >>>> > >> from >>>>> > >> > ",tablename," a join individual i on a.ident=i.ident where >>>>> fid=",agpfid); >>>>> > >> > PREPARE stmt FROM @stmt_text; >>>>> > >> > EXECUTE stmt; >>>>> > >> > DEALLOCATE PREPARE stmt; >>>>> > >> > >>>>> > >> > >>>>> > >> > select * from t; >>>>> > >> > OPEN adi; >>>>> > >> > FETCH adi INTO resadi; >>>>> > >> > CLOSE adi; >>>>> > >> > >>>>> > >> > IF (resadi>0) THEN >>>>> > >> > select resadi as tablename; >>>>> > >> > END IF; >>>>> > >> > >>>>> > >> > >>>>> > >> > On 10/16/08 4:07 PM, "Martin Gainty" <[EMAIL PROTECTED]> wrote: >>>>> > >> > >>>>>>> > >>> > > Good Afternoon Olaf- >>>>>>> > >>> > > >>>>>>> > >>> > > not seeing anything obvious which could be incorrect >>>>>>> > >>> > > what happens when you execute the proc >>>>>>> > >>> > > ? >>>>>>> > >>> > > >>>>>>> > >>> > > Thanks >>>>>>> > >>> > > Martin Gainty >>>>>>> > >>> > > ______________________________________________ >>>>>>> > >>> > > Disclaimer and confidentiality note >>>>>>> > >>> > > Everything in this e-mail and any attachments relates to the official >>>>> > >>> business >>>>>>> > >>> > > of Sender. This transmission is of a confidential nature and >>>>>>> Sender does >> > not >>>>>>> > >>> > > endorse distribution to any party other than intended >>>>>>> recipient. Sender >>>>> > >>> does >>>>>>> > >>> > > not necessarily endorse content contained within this >>>>>>> transmission. >>>>>>> > >>> > > >>>>>>> > >>> > > >>>>>>>>>>> > >>>>> > >> > Date: Thu, 16 Oct 2008 15:31:23 -0400 >>>>>>>>>>> > >>>>> > >> > Subject: Stored proc - dynamic sql in cursor >>>>>>>>>>> > >>>>> > >> > From: [EMAIL PROTECTED] >>>>>>>>>>> > >>>>> > >> > To: mysql@lists.mysql.com >>>>>>>>>>> > >>>>> > >> > >>>>>>>>>>> > >>>>> > >> > 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 // >>>>>>>>>>> > >>>>> > >> > >>>>>>>>>>> > >>>>> > >> > >>>>>>>>>>> > >>>>> > >> > ----------------------------------------- >>>>>>>>>>> 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] >>>>>>>>>>> > >>>>> > >> > >>>>>>> > >>> > > >>>>>>> > >>> > > >>>>>>> > >>> > > Want to read Hotmail messages in Outlook? The Wordsmiths show you how. >>>>> > >>> Learn >>>>>>> > >>> > > Now >>>>>>> > >>> > > >>>>> > >>> >> > >> <http://windowslive.com/connect/post/wedowindowslive.spaces.live.com-Blog-cns! >>>>>>> > >>> > > 20EE04FBC541789!167.entry?ocid=TXT_TAGLM_WL_hotmail_092008> >>>>> > >> > >>>>> > >> > >>>>> > >> > >>>>> > >> > >>>>> > >> > >>>>> > >> > >>>>> > >> > ------------------------- >>>>> > >> > 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 >>>>> > >> > >>> > > >>> > > >>> > > Want to read Hotmail messages in Outlook? The Wordsmiths show you how. >>> Learn >>> > > Now >>> > > >>> <http://windowslive.com/connect/post/wedowindowslive.spaces.live.com-Blog-cns! >>> > > 20EE04FBC541789!167.entry?ocid=TXT_TAGLM_WL_hotmail_092008> >> > >> > >> > >> > >> > >> > >> > ------------------------- >> > 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. > > > When your life is on the go—take your life with you. Try Windows Mobile® today > <http://clk.atdmt.com/MRT/go/115298558/direct/01/> ------------------------- 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
