Solved

How to return a temporary table from a mysql5 function?

Posted on 2006-10-30
4
494 Views
Last Modified: 2008-01-09
Hi,

I have a simple requirement.

I want to write an mysql5 function that creates a temporary table and returns the contents of the temporary table. In the past I have seen this kind of thing done in sybase and with the advent of stored function support in mysql5 expected to be able to do it there.

However I cannot find any examples of doing this in a mysql5 function. I have experimented with the syntax of the function but keep getting errors when I try and save it using navicat mysql5 tool?

Is it possible to accomplish what I want in mysql5? If so I would appreciate a very simple example showing the correct syntax of a working function.

regards

Paul
0
Comment
Question by:ucacppw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 14

Expert Comment

by:racek
ID: 17833853
Statements that return a result set cannot be used within a stored function. This includes SELECT  statements that do not use INTO to fetch column values into variables, SHOW  statements, and other statements such as EXPLAIN. For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs (ER_SP_NO_RETSET_IN_FUNC). For statements that can be determined only at runtime to return a result set, a PROCEDURE %s can't return a result set in the given context error occurs (ER_SP_BADSELECT).

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
0
 

Author Comment

by:ucacppw
ID: 17834398
This suggests to me that in mysql5 it is not possible to create a function with "RETURNS TABLE(.....)" in its definition. Is that your understanding as well?
0
 
LVL 14

Accepted Solution

by:
racek earned 500 total points
ID: 17834461
YES :-(
0
 
LVL 14

Expert Comment

by:racek
ID: 17834471
... but if you can select from temp table after executing strored procedure or function
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question