• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Filter from results of stored procedure

 
Ranch Hand
Posts: 254
1
MySQL Database Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have an SP of numerous lines and I want to filter its resultset. I'm confused which way would be good -- does performing database specific operations in code look feasible? Or is there any other option here? Do I just deal with the convoluted SP and edit it?

I'm thinking to do some SQL like processing in code. What would you do if this was your problem?
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What do you mean by "SQL processing in code"? SQL processing is generally better done by the database, mostly for performance reasons (the database is closer to the data, so it avoids the network traffic, plus databases are designed to do SQL processing right from the start, so are usually pretty good at it.

Generally the possibilities I see (in no particular order) are:

1) Get rid of the stored procedure and code the logic in the application. I might opt for this if there was a long-term outlook to get rid of all stored procedures and the procedure wasn't actually needed for performance.

2) Fix the stored procedure (perhaps overhaul the DB code a bit). If there is lots of database code, and you don't plan to get rid of it for any reason, then it would be better to keep it in shape.

3) Some databases might allow you to use the SP (perhaps after converting it to a function that returns a resultset) in an SQL query as a table, so you might be able to do the SQL operations over the result of the SP itself. It might solve your needs, but still looks like a little bit of hack. Quick fix for a project that doesn't have a longer term development perspective.
 
Ahsan Bagwan
Ranch Hand
Posts: 254
1
MySQL Database Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you for your response, Martin.

What I meant by database processing in code was that to perform operations (like remove) in Java after I fetch the records. To give an example, I want to remove all invalid telephone numbers that have bypassed the form validation.

The third possibility mentioned above looks like it will come in handy. Any way MySQL supports this? Searching the internet did not give any relevant results.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm not at all versed in MySQL, but it seems that MySQL cannot do this. You might modify your stored procedure to create a temporary table and then use SQL over that table to do the processing you want. The question is whether it is not better to fix the stored procedure outright, given that you're going to touch it anyway.
 
Ahsan Bagwan
Ranch Hand
Posts: 254
1
MySQL Database Java Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you again for your inputs. I had to bite the bullet and deal with the SP code.
 
reply
    Bookmark Topic Watch Topic
  • New Topic