List:MySQL on Win32« Previous MessageNext Message »
From:rdo mail list address Date:August 10 2004 7:58pm
Subject:Re: Calling a Stored Procedure crashing MySQL!!
View as plain text  
The same kind of thing has happened to me - in fact, it was my first couple
of posts to this list.  The table has "lots of columns" and more than 500
rows.  

The open cursor is what crashes the server.



At 12:52 AM 8/11/2004 +0530, Amit_Wadhwa@stripped wrote:
>Hi All,
>
>Im a Newbie with Cursors,
>
>What I basically need to do is:
>
>Execute one SQL Statement:
>Loop
>    For every Record, execute a query based on results of the first
>query
>            Loop
>                   Check a Condition based on these results.
>                    If (condition)
>                        Execute Another Query
>                    Else 
>                        Execute Another Query
>            End Loop
>End Loop                       
>
>Currently am using JDBC/Java Program to do the above in 10 Minutes for
>over 400,000 Records.
>But am experimenting with Cursors to see if it can be done any faster.
>
>The Procedure is given below.
>It Stores Fine. But when I call it, my MySqld-NT crashes
>immediately.(...has caused an error and needs to close)
>
>Any Help is greatly Appreciated!
>
>Regards,
>
>Amit Wadhwa
>
>CREATE PROCEDURE calculatehits()
>
>BEGIN
>
>DECLARE cur1 CURSOR FOR SELECT f1,f2,f3,f4 FROM t1 WHERE f5 IS NOT NULL
>AND f6 NOT IN ('CNL') AND f7 <> '0' AND f8<> 'null' ;
>DECLARE a CHAR(16);
>DECLARE b,c DATETIME;
>DECLARE d int(16);
>DECLARE e int(16);
>OPEN cur1;
>REPEAT
>FETCH cur1 INTO a, b,c,d;
>SELECT f9 INTO @e FROM t1 WHERE f1= @a and f2 > @b And f3 <= @c AND f4
>NOT IN ('CNL') AND f5 IS NOT NULL AND f6 <> 'null';
>IF d=e THEN
>//do something here
>END IF;
>UNTIL done END REPEAT;
>CLOSE cur1;
>
>END
>
>
Thread
Calling a Stored Procedure crashing MySQL!!Amit_Wadhwa10 Aug
Re: Calling a Stored Procedure crashing MySQL!!rdo mail list address10 Aug
RE: Calling a Stored Procedure crashing MySQL!!Amit_Wadhwa10 Aug
RE: Calling a Stored Procedure crashing MySQL!!rdo mail list address10 Aug
RE: Calling a Stored Procedure crashing MySQL!!jbonnett11 Aug
RE: Calling a Stored Procedure crashing MySQL!!Amit_Wadhwa11 Aug