Search code examples
mysqlmysql-error-1064mysql-function

MySQL Functions: Error #1064

I'm getting the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN GenreID INT(11), OUT AlbumName VARCHAR(30)) BEGIN SELECT AlbumName INTO Na' at line 1

My Code:

CREATE FUNCTION get_album_info_for_genre (IN GenreID INT(11), OUT AlbumName 
VARCHAR(30))
BEGIN
    SELECT AlbumName INTO NameOfAlbum FROM Albums WHERE GenreID=GenreID;
END

I'm in PhpMyAdmin and I'm trying to write some functions for a music database. I'd like to write one that displays the artist name, genre name, and album name if the user selects a specific genre (only show albums and artists within that genre) or if they choose an artist vice versa.

I figured I'd start out simple and just show the albumname depending on the genre they chose.

If necessary, my table structure is:

  • Albums: AlbumID, GenreID, ArtistID, AlbumName

  • Genres: GenreID, GenreName

  • Artists: ArtistID, ArtistName

I tried changing where it had Na to something else but I still got the error. I also tried changing the delimeter but that didn't help either. Note that phpmyadmin by default has ; as the delimeter. I tried adding $$ at the top and bottom but I still got error 1064. For some reason I can't find a lot of articles or videos on mysql functions. There are tons on Stored Procedures but I've searched for 4 days and the only place I saw people writing functions was here on stackoverflow. I looked at a couple of the questions that got this error but and tried some of the solutions but must still be missing something obvious.

EDIT - I tried changing the syntax to remove the IN and OUT and get the same error but at a different location - syntax to use near 'BEGIN SELECT AlbumName INTO NameOfAlbum FROM Albums WHERE GenreID=GenreID' at line 2

Solution

  • A function takes a value and returns a value. The syntax for a function does not need to know the direction of a parameter (in or out) since logically there is only an in but it does need to know the attributes of what is being returned (defined by the Returns statement). A Return statement in the function completes the code and passes a variable to the Returns statement and the function quits. So this code should compile. If you are doing this from a query then change the delimiter from ; to $$ (don't forget to change it back).If you are doing it from the routines tab key as appropriate.

    CREATE DEFINER=`root`@`localhost` FUNCTION `get_album_info_for_genre`(`INGenreID` INT(11)) RETURNS VARCHAR(100) CHARSET latin1 
    NOT DETERMINISTIC 
    CONTAINS SQL 
    SQL SECURITY DEFINER 
    BEGIN Declare outs varchar(100); 
    SELECT AlbumName INTO outs FROM Albums WHERE GenreID=INGenreID; 
    RETURN outs; 
END

    You may have to figure out who your definer is for your shop.

    Note-if there are many albums per genreid then a function may not be what you want..