aspose file tools*
The moose likes JDBC and the fly likes MySQL - Returning Tables in Functions/SPs/Views Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySQL - Returning Tables in Functions/SPs/Views" Watch "MySQL - Returning Tables in Functions/SPs/Views" New topic
Author

MySQL - Returning Tables in Functions/SPs/Views

Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

I'd like to write a function in MySQL that returns a table such as:



So that I can have SQL code such as :



But it appears having TABLE has a return value is a missing feature in MySQL. As an alternative, I could use a View BUT as far as I know parameterization of Views such as the '5' in MyFunction(5) is also not allowed.

I guess that leaves me with Stored Procedures? How would I write the signature of a stored procedure to do something like:



In particular, can stored procedures have return types and used as above?

Is there no good solution?
[ November 07, 2008: Message edited by: Scott Selikoff ]

My Blog: Down Home Country Coding with Scott Selikoff
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38865
    
  23
There I was thinking it was a greenhorn asking!

You are obviously familiar with the MySQL manual and this article.

What is wrong with simply selecting the entire contents of the table and returning them as a result set?
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38865
    
  23
By selecting, I meant in a procedure.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

When it comes to Stored Procedures, everyone's a greenhorn (or a madman). The problem I'm having is I want to use syntax such as "SELECT * FROM (mySP(5))" either with a View, Stored Procedure, or Function, but *none* of them seem possible in MySQL. Unless anyone has a solution I missed?

The MySQL documentation doesn't cover returning tables in stored procedures nor how do to so in a single line as part of an existing query (rather 2 lines with a SET command).
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38865
    
  23
That should work from the command line.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

Have you tried using your function in a command such as "SELECT * FROM (CALL myFunction(5))?
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38865
    
  23
I wrote a little procedure (not function) like that which found my own address from my address table and I have copied the result (with some details unsuitable for a public website obscured and newlines added).

When I tried select * from (call all_addresses(1573)); I got a syntax error.

I hope that helps you
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38865
    
  23
And I don't think writing "reads sql data" as I should have rather than "modifies" would have made any difference
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

Originally posted by Campbell Ritchie:
When I tried select * from (call all_addresses(1573)); I got a syntax error.

I hope that helps you


Nope, that's the line I need to work.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38865
    
  23
That's as far as I can get. Sorry.

Anybody else?
Tim Holloway
Saloon Keeper

Joined: Jun 25, 2001
Posts: 16065
    
  21

Stored Procedures are a blight on the Earth.

Seriously. The preceeding are all simple enough SQL statements that you gain almost nothing by placing them in stored procedures. And transactionalized SELECTS???

There is a place and a time for almost everything. I advocate stored procedures when:

1. you want to codify extremely complex processes as fundamental database operations.

2. You need to do something that requires a lot of processing within the database context and the benefit of reduced network activity and frontend calculations compensates sufficiently for the fact that you're adding to the workload on the database server. And the more frontend processors you have the most justification you'll need. Database boxes are usually more expensive and often require additional expensive database licences as well.

3. You need to present a simplified command-line style query to nontechnical users.

4. You've got to execute the same business logic from a variety of different client platforms, such as ASP.Net and JEE.

In exchange for the above benefits, you pay the following costs:

1. More load on the DBMS server machine (as previously mentioed)

2. Less portability in the SQL code of the frontend apps. It can be a major pain to convert an Oracle PL/SQL app to MS-SQL or vice versa, but most of the ORM tools can switch the bulk of simple SQL without even recompiling the app.

3. Fracturing of the application structure. This one bit me really bad last month. I got an app where you literally couldn't tell what the Java code was doing because half the program logic wasn't even in the source code tree - it was in stored procedures on someone else's database.

4. Related to item 3, anyone doing design or maintenance work on the apps will need fluency in not 1, but 2 languages. One for the frontend and one for the particular dialect of database in use. One of the major selling points for ORM is that it's easier (and cheaper) to find people fluent in Java than it is to find Java/SQL experts.

In short, I'll use stored procedures when I have a compelling need, but that's rare. It's a convenience with a very high price tag.


Customer surveys are for companies who didn't pay proper attention to begin with.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

You forgot #5:

When your converting a database and don't have the freedom to throw them away.
Patrollie Kaptein
Greenhorn

Joined: Dec 06, 2011
Posts: 1
Boet, I was once like you. But I realized this: with an ever increasing workload, it's easier if the DB can do more for you. Example: I have two separate systems that hosts DB's. One is an inbound text messing system, the other an outbound voice log system. I want to join tables across the two DB's to figure out which inbound text messages do not have a voice log.

1. Your solution = Run queries on both DB's, figure out the rest in code. Sounds interesting. When I'm up for a challenge I might just look into this...... not. I'm not a Java programmer anyway.
2. MySQL solution = federated or federatedx storage engine. Sucks. Slow. Bugs. Dead projects.
3. One of my solutions = Create a table in one of the two DB's. Keep it in sync with shell scripting. Do the joins locally on one DB. Sucks. Not real time. Maintenance.
4. Ideal solution = Postgres DB link functions. It runs a query on a remote postgres DB and returns the results in a dataset in the function. Joinable to other tables on the local system. Fast. Indexes are utilized on the remote system.
5. A solution I would have liked: Create my own db-link equivalent for MySQL. Would have been possible if I could return datasets in stored function results so that I can join on them.

Am I to be Greenhorn? No boet. Just in hurry and working alone.

Tim Holloway wrote:Stored Procedures are a blight on the Earth.

Seriously. The preceeding are all simple enough SQL statements that you gain almost nothing by placing them in stored procedures. And transactionalized SELECTS???

There is a place and a time for almost everything. I advocate stored procedures when:

1. you want to codify extremely complex processes as fundamental database operations.

2. You need to do something that requires a lot of processing within the database context and the benefit of reduced network activity and frontend calculations compensates sufficiently for the fact that you're adding to the workload on the database server. And the more frontend processors you have the most justification you'll need. Database boxes are usually more expensive and often require additional expensive database licences as well.

3. You need to present a simplified command-line style query to nontechnical users.

4. You've got to execute the same business logic from a variety of different client platforms, such as ASP.Net and JEE.

In exchange for the above benefits, you pay the following costs:

1. More load on the DBMS server machine (as previously mentioed)

2. Less portability in the SQL code of the frontend apps. It can be a major pain to convert an Oracle PL/SQL app to MS-SQL or vice versa, but most of the ORM tools can switch the bulk of simple SQL without even recompiling the app.

3. Fracturing of the application structure. This one bit me really bad last month. I got an app where you literally couldn't tell what the Java code was doing because half the program logic wasn't even in the source code tree - it was in stored procedures on someone else's database.

4. Related to item 3, anyone doing design or maintenance work on the apps will need fluency in not 1, but 2 languages. One for the frontend and one for the particular dialect of database in use. One of the major selling points for ORM is that it's easier (and cheaper) to find people fluent in Java than it is to find Java/SQL experts.

In short, I'll use stored procedures when I have a compelling need, but that's rare. It's a convenience with a very high price tag.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySQL - Returning Tables in Functions/SPs/Views