List:General Discussion« Previous MessageNext Message »
From:Olaf Stein Date:October 17 2008 1:11pm
Subject:Re: Stored proc - dynamic sql in cursor
View as plain text  
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" <mgainty@stripped> 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: olaf.stein@stripped
>> > To: mgainty@stripped; mysql@stripped
>> > 
>> > 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" <mgainty@stripped> 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: olaf.stein@stripped
>>>>> > >> > To: mgainty@stripped; mysql@stripped
>>>>> > >> > 
>>>>> > >> > 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"
> <mgainty@stripped> 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: olaf.stein@stripped
>>>>>>>>>>> > >>>>> > >> >
> To: mysql@stripped
>>>>>>>>>>> > >>>>> > >> >
>>>>>>>>>>> > >>>>> > >> >
> 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/mysql?unsub=1
>>>>>>>>>>> > >>>>> > >> >
>>>>>>> > >>> > > 
>>>>>>> > >>> > > 
>>>>>>> > >>> > > 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: olaf.stein@stripped
>>>>> > >> > 
>>>>> > >> > 
>>>>> > >> > ³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: olaf.stein@stripped
>> > 
>> > 
>> > ³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: olaf.stein@stripped


“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


Thread
Stored proc - dynamic sql in cursorOlaf Stein16 Oct
  • Re: Stored proc - dynamic sql in cursorRob Wultsch16 Oct
    • Re: Stored proc - dynamic sql in cursorOlaf Stein16 Oct
Re: Stored proc - dynamic sql in cursorOlaf Stein16 Oct
Re: Stored proc - dynamic sql in cursorOlaf Stein16 Oct
Re: Stored proc - dynamic sql in cursorOlaf Stein16 Oct
Re: Stored proc - dynamic sql in cursorOlaf Stein17 Oct