• 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

MySQL - Returning Tables in Functions/SPs/Views

 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Marshal
Posts: 79179
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 79179
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
By selecting, I meant in a procedure.
 
Scott Selikoff
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 79179
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That should work from the command line.
 
Scott Selikoff
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you tried using your function in a command such as "SELECT * FROM (CALL myFunction(5))?
 
Campbell Ritchie
Marshal
Posts: 79179
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 79179
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
And I don't think writing "reads sql data" as I should have rather than "modifies" would have made any difference
 
Scott Selikoff
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Marshal
Posts: 79179
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That's as far as I can get. Sorry.

Anybody else?
 
Saloon Keeper
Posts: 27763
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Scott Selikoff
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You forgot #5:

When your converting a database and don't have the freedom to throw them away.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic