The fields are all Different, had to change the names because of
My Existing Query (without cursors) runs fine and is very similar to
what you've given below,
Was just expoerimenting with cursors to see if it made any performance
Thanks for the detailed response though.
But why would a stored Procedure crash MySQL when executed??
I tried a very simple query instead of the below given query, and I
still had the immediate crash when calling the procedure.
Could it be a problem with my MySQL Installation or something not set
right? Or is it a bug somewhere?
From: jbonnett@stripped [mailto:jbonnett@stripped]
Sent: Wednesday, August 11, 2004 5:28 AM
To: Wadhwa, Amit; win32@stripped
Subject: RE: Calling a Stored Procedure crashing MySQL!!
I have tried to unravell what you are trying to do and it seems to me
you may be able to achieve what you want without any cursors. As far as
I can tell what you are trying to do can be rewritten something like
FROM t1 as x, t1 as y
WHERE x.f1 = y.f1
AND y.f2 > x.f2
AND y.f3 <= x.f3
AND x.f6 NOT IN ('CNL')
AND x.f7 <> '0'
AND x.f8 <> 'null'
AND y.f4 NOT IN ('CNL')
AND y.f5 IS NOT NULL
AND y.f6 <> 'null'
What you put at <?> will depend on what you want to do at "//do
something here" and which fields you need. You could perhaps make a
cursor out of the above query to do whatever you want.
Also I think f4 NOT IN ('CNL') is the same as f4 <> 'CNL' which probably
I am also suspicious that your table design may not be very good. It
looks to me that you have two different things stored in fields of the
Life may be a lot simpler if there were two tables. While I don't
understand what you are doing, when I see things like f4 NOT IN ('CNL')
and f6 NOT IN
('CNL') plus f6 <> 'null' and f8 <> 'null' I get the feeling that f4, f6
f8 may be storing multiple instances of the same sort of data in the one
table. This sort of thing violates first normal form. You will find SQL
works much better if you design your tables to at least 3rd normal form.
Anyway, there are some thoughts.
From: Amit_Wadhwa@stripped [mailto:Amit_Wadhwa@stripped]
Sent: Wednesday, 11 August 2004 4:53 AM
Subject: Calling a Stored Procedure crashing MySQL!!
Im a Newbie with Cursors,
What I basically need to do is:
Execute one SQL Statement:
For every Record, execute a query based on results of the first
Check a Condition based on these results.
Execute Another Query
Execute Another Query
Currently am using JDBC/Java Program to do the above in 10 Minutes for
400,000 Records. But am experimenting with Cursors to see if it can be
The Procedure is given below.
It Stores Fine. But when I call it, my MySqld-NT crashes
caused an error and needs to close)
Any Help is greatly Appreciated!
CREATE PROCEDURE calculatehits()
DECLARE cur1 CURSOR FOR SELECT f1,f2,f3,f4 FROM t1 WHERE f5 IS NOT NULL
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
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;