dynamic sql using INTO
I have a function built that will get me X percentile from my history table for last X days. It works fine as long as I hard code the certain values in where they go... what I really need to know is how can I make dynamic sql still do a select into a variable? simply replacing the '31' below with 'duration' does not work in here because it is not a proper place for a variable substitution DELIMITER $$ -- -- Definition for function firescope.getPercentile -- DROP FUNCTION IF EXISTS firescope.getPercentile$$ CREATE FUNCTION getPercentile(iid BIGINT(20), duration INT, percentile INT) RETURNS TEXT CHARSET LATIN1 BEGIN DECLARE cnt, pct BIGINT(20); DECLARE temp TEXT; SELECT COUNT(*), AVG(value) INTO cnt FROM history WHERE itemid = iid AND clock UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 31 DAY)); /* this 31 should be duration from the in list*/ SET @calc = 100 / (100 - percentile); SET @yarp = cnt / @calc; SELECT value INTO temp FROM history WHERE itemid = iid AND clock UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 31 DAY)) /* this 31 should be duration from the in list*/ ORDER BY value DESC LIMIT 0, 1; /*this 0 should be @yarp*/ RETURN temp; END $$ DELIMITER ;
Re: Stored proc - dynamic sql in cursor
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 (resadi0) 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
Stored proc - dynamic sql in cursor
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 (resadi0) 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 (resadi0) 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]
Re: Stored proc - dynamic sql in cursor
The tablename in the cursor cannot be dynamic... I am just seeing that it should say: DECLARE adi CURSOR FOR select count(*) from tablename a join individual i on a.ident=i.ident where fid=agpfid; In the full proc below where tablename is a variable... Olaf 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 (resadi0) 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 (resadi0) 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
Re: Stored proc - dynamic sql in cursor
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 (resadi0) 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 (resadi0) 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]
Re: Stored proc - dynamic sql in cursor
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 (resadi0) 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 (resadi0) 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 (resadi0) 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
Re: Stored proc - dynamic sql in cursor
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 (resadi0) 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 (resadi0) 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
Re: Stored proc - dynamic sql in cursor
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 (resadi0) 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 (resadi0) 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
create cursor from dynamic sql
I need to create a cursor using dynamic sql. Below is a short example of what I am trying to use in order to get the correct resultset... ignore the rest of the proc, I just need to know how to make the cursor declaration from @vsql Thanks DELIMITER $$ DROP PROCEDURE IF EXISTS `firescope`.`triggerDownTime` $$ CREATE PROCEDURE `firescope`.`triggerDownTime` (IN TrigID BIGINT(20) unsigned,IN tRange varchar(10), OUT DownTime BIGINT(20) unsigned) BEGIN DECLARE done INT DEFAULT 0; DECLARE eventTime, startTime, endTime, defaultStart, defaultEnd, outage BIGINT(20) unsigned; DECLARE val, alreadyStarted int(10); set @vsql = 'select unix_timestamp(DATE_SUB(now(),INTERVAL ',tRange,')), unix_timestamp(now()), value, unix_timestamp(time_stamp) from fs_sl_events where triggerid = 'TrigID,' AND time_stamp DATE_SUB(now(),INTERVAL 'tRange,')'); DECLARE cur1 CURSOR FOR vsql; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO defaultStart, defaultEnd, val, eventTime; IF NOT done THEN set DownTime = unix_timestamp(DATE_SUB(now(),INTERVAL 30 DAY)); END IF; UNTIL done END REPEAT; CLOSE cur1; select DownTime; END $$ DELIMITER ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dynamic sql in proc
Hi, just try like: mysql create procedure mi() - begin - declare done int default 0; - declare table_name varchar(50); - declare cur1 cursor for select tables.table_name from information_schema.tables where table_schema='test' and table_type='BASE TABLE' and engine='MyISAM'; - DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; - open cur1; - repeat - fetch cur1 into table_name; - set @table_name=table_name; - if not done then - set @stext=CONCAT(Alter table , @table_name, engine=InnoDB); - prepare smt from @stext; - execute smt; - deallocate prepare smt; - end if; - until done end repeat; - close cur1; - end; - | Query OK, 0 rows affected (0.03 sec) mysql call mi(); Query OK, 2 rows affected (0.01 sec) +---+ | version() | +---+ | 5.0.18| +---+ 1 row in set (0.00 sec) Thanks ViSolve DB Team. - Original Message - From: Bryan Cantwell [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, June 29, 2007 4:03 AM Subject: dynamic sql in proc I have the following proc... when I run it I get a response that says You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1. I just want a programatic way to upgrade db engine to innodb where I don't know exactly what tables exist... DELIMITER $$ DROP PROCEDURE IF EXISTS `firescope`.`MYISAMtoINNODB` $$ CREATE PROCEDURE `MYISAMtoINNODB`() BEGIN DECLARE done INT DEFAULT 0; DECLARE table_name VARCHAR(255); DECLARE cur1 CURSOR FOR select table_name from information_schema.tables where table_schema='firescope' and table_type='BASE TABLE' and engine='MyISAM'; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO table_name; SET @table_name=table_name; IF NOT done THEN SET @stmt_text=CONCAT(ALTER TABLE , @table_name, ENGINE = InnoDB); PREPARE stmt FROM @stmt_text; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; UNTIL done END REPEAT; CLOSE cur1; END $$ DELIMITER ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.476 / Virus Database: 269.9.10/875 - Release Date: 6/27/2007 9:08 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dynamic sql in proc
I have the following proc... when I run it I get a response that says You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1. I just want a programatic way to upgrade db engine to innodb where I don't know exactly what tables exist... DELIMITER $$ DROP PROCEDURE IF EXISTS `firescope`.`MYISAMtoINNODB` $$ CREATE PROCEDURE `MYISAMtoINNODB`() BEGIN DECLARE done INT DEFAULT 0; DECLARE table_name VARCHAR(255); DECLARE cur1 CURSOR FOR select table_name from information_schema.tables where table_schema='firescope' and table_type='BASE TABLE' and engine='MyISAM'; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO table_name; SET @table_name=table_name; IF NOT done THEN SET @stmt_text=CONCAT(ALTER TABLE , @table_name, ENGINE = InnoDB); PREPARE stmt FROM @stmt_text; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; UNTIL done END REPEAT; CLOSE cur1; END $$ DELIMITER ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dynamic SQL
Using UltraDev 4 with Phakt 1.2.1, linking to a MySQL server 3.22.23
I want to develop a SQL SELECT query where I can vary what customers are
listed depending on the value of various form fields.
For example if the check box surname_all is ticked then all customers are
displayed if not then it displays all customers where the surname lies
between surname_from to surname_to.
I couldn't find an appropriate way of handling this in MySQL directly so my
aim was to determine the SQL portion outside the query and then append it to
the query:
if ($surname_all == -1){
$surname_query = WHERE surname between $surname_from and $surname_to;
} else {
$surname_query = ;
}
NB - If $surname_all = -1 then it has been ticked.
And then I would declare my SQL statement as
SELECT * from customers.$surname_query
UltraDev doesn't like this and so I would appreciate any help in either a
better way to code it in UltraDev or a SQL statement that would achieve the
same aim.
Thanks for your help
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Dynamic SQL
Try using something a little different:
if ($surname_all){ //checks to see if the variable is set
$surname_query = WHERE surname between $surname_from and $surname_to;
} else {
$surname_query = ;
}
-Original Message-
From: Craig Shepherd [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 04, 2002 10:55 AM
To: [EMAIL PROTECTED]
Subject: Dynamic SQL
Using UltraDev 4 with Phakt 1.2.1, linking to a MySQL server 3.22.23
I want to develop a SQL SELECT query where I can vary what customers are
listed depending on the value of various form fields.
For example if the check box surname_all is ticked then all customers are
displayed if not then it displays all customers where the surname lies
between surname_from to surname_to.
I couldn't find an appropriate way of handling this in MySQL directly so my
aim was to determine the SQL portion outside the query and then append it to
the query:
if ($surname_all == -1){
$surname_query = WHERE surname between $surname_from and $surname_to;
} else {
$surname_query = ;
}
NB - If $surname_all = -1 then it has been ticked.
And then I would declare my SQL statement as
SELECT * from customers.$surname_query
UltraDev doesn't like this and so I would appreciate any help in either a
better way to code it in UltraDev or a SQL statement that would achieve the
same aim.
Thanks for your help
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Dynamic SQL
Thanks for that.
It's actually more the UltraDev/Phakt thing I'm having problems with. I just
wondered how you would set the SQL statement in such a way that UltraDev
wouldn't complain - it doesn't like SELECT * from customers.$surname_query.
Do you know of a good way of handling conditional statements in MySQL?
-Original Message-
From: Trelfa, Jonathon [mailto:[EMAIL PROTECTED]]
Sent: 04 March 2002 16:34
To: [EMAIL PROTECTED]
Subject: RE: Dynamic SQL
Try using something a little different:
if ($surname_all){ //checks to see if the variable is set
$surname_query = WHERE surname between $surname_from and $surname_to;
} else {
$surname_query = ;
}
-Original Message-
From: Craig Shepherd [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 04, 2002 10:55 AM
To: [EMAIL PROTECTED]
Subject: Dynamic SQL
Using UltraDev 4 with Phakt 1.2.1, linking to a MySQL server 3.22.23
I want to develop a SQL SELECT query where I can vary what customers are
listed depending on the value of various form fields.
For example if the check box surname_all is ticked then all customers are
displayed if not then it displays all customers where the surname lies
between surname_from to surname_to.
I couldn't find an appropriate way of handling this in MySQL
directly so my
aim was to determine the SQL portion outside the query and then
append it to
the query:
if ($surname_all == -1){
$surname_query = WHERE surname between $surname_from and $surname_to;
} else {
$surname_query = ;
}
NB - If $surname_all = -1 then it has been ticked.
And then I would declare my SQL statement as
SELECT * from customers.$surname_query
UltraDev doesn't like this and so I would appreciate any help in either a
better way to code it in UltraDev or a SQL statement that would
achieve the
same aim.
Thanks for your help
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
