List:MySQL on Win32« Previous MessageNext Message »
From:<Amit_Wadhwa Date:August 10 2004 8:21pm
Subject:RE: Calling a Stored Procedure crashing MySQL!!
View as plain text  
So How do you work around it?
The Cursor needs to be open to be able to retrieve records right? 

-----Original Message-----
From: rdo mail list address [mailto:rdomail@stripped] 
Sent: Wednesday, August 11, 2004 1:28 AM
To: win32@stripped
Subject: Re: Calling a Stored Procedure crashing MySQL!!

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
>
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:
http://lists.mysql.com/win32?unsub=1


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