This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes Infinite Loop in mysql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Infinite Loop in mysql" Watch "Infinite Loop in mysql" New topic
Author

Infinite Loop in mysql

Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
I am working on mysql stored procedures. I am a beginner in mysql

I have tried a stored procedure in mysql, but, it results in infinite loop



It seems the query if executed, and if no result found, doesn't returns 0.
So i tried WHILE b IS NOT NULL, but it didn't worked

So, how to proceed? I want the functionality that as soon as the query doesn't returns anything, it should come out of loop. So, what condition i should code in WHILE?


kunal
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1671
    
  14

Can you show us where your variable "b" is assigned a new value inside your WHILE loop? If you are using a variable as your stop condition, you need to make sure it gets set correctly inside the loop.

I'm not familiar with stored procedures in MySQL, but you should probably look at how to process SQL cursors using LOOP in MySQL.


No more Blub for me, thank you, Vicar.
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Inside WHILE loop, b is assigned a new value in line no.12, i.e SET b = c; Where c is the value i get from Query.
I need to check that if Query doesn't returns anything, simply come out of WHILE loop.
Whereas , in the procedure i wrote, b is never null or 0. So, The control, doesn't gets out of WHILE, and keeps on printing the last value as in infinite loop
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1671
    
  14

Kunal Lakhani wrote:I want the functionality that as soon as the query doesn't returns anything, it should come out of loop. So, what condition i should code in WHILE?

Forget WHILE and use LOOP with a SQL cursor instead.
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Looking forward to it

Thanks
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Here is what i tried with CURSOR

i execute it with parameter as 1004
It runs successfully and the output is 1, 1008. However, the loop runs only once. But, it should run twice, next time with value of b in query as 1008, and the output should be 2,1009
I guess, the query gets executed only once, and so the value of 'b' in query doesn't gets updated. What i must do, so that the query gets executed each time after the value of b is updated?
What i am missing?
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1671
    
  14

What are you actually trying to achieve with this procedure, and how many rows do you expect to get from your query? A cursor-loop will run the query once, then process each row in turn, so changing the variable used in the query after the query has been executed doesn't change the query results. You don't actually do anything with your results here, so it's hard to see what you are trying to achieve.

If you want to run one query and process each resulting row in turn, which is what we usually want to do e.g. when we don't know how many results we will get but we know we want to process all of them, then a cursor loop is a good choice.

If you want to run a query once and you know you will only get one row in the results e.g. you are selecting a count from a table, then you don't need a loop at all.

But if you want to run the query multiple times with different parameters each time, then you need to determine how many times it needs to be executed e.g. do you want it to run 5 times (use a for-loop with a counter variable), run once for each item in a collection (use a for-loop that iterates over your collection), or do you want it to run until some arbitrary condition is true (use a while-loop but make sure you identify your stopping condition and check for this in your loop)? In my experience it's fairly unusual to have to do this, and you can often define your SQL query so that it can return all the rows you want in a single execution (which is more efficient), rather than having to execute lots of separate queries with slightly different parameters.

But if you really need this kind of approach, then you would define your loop, and execute your query with fresh parameters once each time you go around the loop. Alternatively, you could define two procedures/functions - one to run the query with given parameters and process the results, and another to handle the looping conditions and call the query-procedure with different parameters each time around the loop - which would allow you to clearly separate the looping logic from the query.

Try writing out your process in pseudo-code, so you can be sure you know what you are trying to achieve, then pick the appropriate loop construct for implementing it. Sorry I can't help you with the MySQL code, but I think the main problem is in your logic rather than the actual code. Figure out the correct logic for your procedure, then you should find it easy to write the code.
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Thanks for your reply

But if you really need this kind of approach, then you would define your loop, and execute your query with fresh parameters once each time you go around the loop.



This is what i am trying to do

Suppose 1st time the parameter is 1004, SO this query should run and get the policynumber. Again next time, again the same query will run , but with fresh parameter (i.e new policynumber). This should keep on repeating until the query doesn't returns anything
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1671
    
  14

Kunal Lakhani wrote:Suppose 1st time the parameter is 1004, SO this query should run and get the policynumber. Again next time, again the same query will run , but with fresh parameter (i.e new policynumber). This should keep on repeating until the query doesn't returns anything

Looking at your latest procedure, you are passing in the parameter "b" (as a read-only value I think), then trying to re-set it inside the procedure. This is probably not going to work, and is a messy way of doing things anyway.

Here's one approach for a first attempt (you'll need to figure out the MySQL code yourself):

  • Define a function called something like GET_POLICY_NUMBER that gets one (parent?) policy number for a given parameter. This will take a named parameter e.g. SEARCH_PLACED_BELOW (not "b"!), run the query once, and return the POLICYNUMBER that was returned by the query. You will also need to work out what to do if no policy number is found i.e. if the query returns no results e.g. return a NULL value, or raise a "No data found" exception which you would handle in your calling procedure.
  • Define a separate procedure that handles the looping stuff. Each time you go around the loop, you call the GET_POLICY_NUMBER function with a new value for the SEARCH_PLACED_BELOW parameter. Check the policy number that is returned by the function, and decide what to do with it e.g. go back around the loop and call the query function again with the new parameter, or exit the loop because the query didn't return any results.

  • This will allow you to get each aspect of this process working correctly, without confusing your input/query parameters and loop conditions. After you've got everything working properly, you might look at ways to re-integrate it into a single procedure, but this would also depend on whether you might want to re-use the query function elsewhere. If there are no performance benefits from combining the functions, then it might be easier to keep them separate.

    Finally, you could look at whether MySQL supports anything like Oracle's "CONNECT BY" syntax, which allows you to query this kind of hierarchical data in a single SQL statement.
    Kunal Lakhani
    Ranch Hand

    Joined: Jun 05, 2010
    Posts: 622
    Thanks chris webster. Done with the problem. It works
    chris webster
    Bartender

    Joined: Mar 01, 2009
    Posts: 1671
        
      14

    Kunal Lakhani wrote:Thanks chris webster. Done with the problem. It works

    Well done.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Infinite Loop in mysql