Solved

MySQL Stored Function Question note the table and columns  are verified

Posted on 2011-09-02
7
313 Views
Last Modified: 2012-05-12
Can anyone tell me what is wrong with this simple stored function in MySQL?
CREATE FUNCTION lup_t1(sid int(12) ) RETURNS text DETERMINISTIC
BEGIN
        DECLARE a1 text;
        SELECT  abstract into a1 from notestbl where Id=sid;
        RETURN a1;
END;

Does MySQL 5.x not allow returning Text types? What is going on
Does MySQL 5.x not allow returning certain large text types
0
Comment
Question by:Robert Silver
  • 3
  • 2
  • 2
7 Comments
 
LVL 22

Accepted Solution

by:
pivar earned 250 total points
ID: 36475949
Hi,

Try the following:

DELIMITER //
CREATE FUNCTION lup_t1(sid int(12) ) RETURNS text DETERMINISTIC
BEGIN
        DECLARE a1 text;
      SELECT  abstract into a1 from notestbl where Id=sid;
        RETURN a1;
END
//

/peter
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36476689
I have seen a bug report from long ago on TEXT that was too long. How large is the data?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36476707
I could not reproduce the old bug in 5.5 MySQL by the way. Without further information, I would have to agree with Peter that likely you just need to add the DELIMITER change from and to ;
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 2

Author Closing Comment

by:Robert Silver
ID: 36477914
For the record what does DELIMITER //    {CODE} //   do?
0
 
LVL 2

Author Comment

by:Robert Silver
ID: 36478051
I beleieve this is due to a semicolon confusing things so by making // the delimiter
we open and close the code in  // marks.
I believe this is explained in the Section 17.1 of the pdf file: mysql-refman-5.5-en.pdf file
reference manual found at
 http://dev.mysql.com/doc/

Would that be correct?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36478122
Yes. Since the semicolon is the delimiter and as such is likely to be in your code of the function, you have to change the delimiter temporarily, then change it back when you are done.

-- ; used here since we have not changed delimiter yet
USE mysql;

DELIMITER //

-- // used here since we already changed delimiter
DROP FUNCTION IF EXISTS lup_t1 //

CREATE FUNCTION lup_t1(sid int(12) ) RETURNS text DETERMINISTIC
BEGIN
        DECLARE a1 text;
        SELECT  abstract into a1 from notestbl where Id=sid;
        RETURN a1;
END
//
DELIMITER ;
0
 
LVL 22

Expert Comment

by:pivar
ID: 36478338
You have to show where the procedure ends, at the same time you have to show where each statement ends. So to not confuse the parser, you need to have different delimiters.
delimiter // changes the delimiter showing where the procedure ends, while the delimiter for statements stays as ;

If you have an one line procedure you don't need to change delimiter:

CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC
RETURN CONCAT('Hello, ',s,'!');

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Database needed for HR files 4 55
How to display div value on input field value in php 11 52
MySql Recovery 2 27
selector:validator cookies 4 30
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

790 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