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.
