aspose file tools*
The moose likes JDBC and the fly likes Filter from results of stored procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Filter from results of stored procedure" Watch "Filter from results of stored procedure" New topic
Author

Filter from results of stored procedure

Ahsan Bagwan
Ranch Hand

Joined: Oct 05, 2010
Posts: 162
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?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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

Joined: Oct 05, 2010
Posts: 162
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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

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

Joined: Oct 05, 2010
Posts: 162
Thank you again for your inputs. I had to bite the bullet and deal with the SP code.
 
jQuery in Action, 2nd edition
 
subject: Filter from results of stored procedure