Go MySQL stored procedure cannot return result set

Question

If I run the below code, but replace the 'CALL' with "SELECT * FROM User LIMIT 1" I get back a user. If I switch to calling the stored procedure I keep getting this error:

panic: Error 1312: PROCEDURE MobiFit_Dev.User_ByEmail can't return a result set in the given context [recovered]
    panic: Error 1312: PROCEDURE MobiFit_Dev.User_ByEmail can't return a result set in the given context

How do I call my SP?

Here is my SP:

DELIMITER $$

USE `MobiFit_Dev`$$

DROP PROCEDURE IF EXISTS `User_ByEmail`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `User_ByEmail`(pEmail VARCHAR(250))
BEGIN
    SELECT 
        * 
    FROM
        `User` 
    WHERE `Email` = pEmail ;
END$$

DELIMITER ;

package entities

import (
    "database/sql"
    "fmt"
    "github.com/go-sql-driver/mysql"
    "mobifit/db"
    // "time"
)

const (
    Male   = "Male"
    Female = "Female"
)

type User struct {
    Id             sql.NullInt64
    Email          sql.NullString
    HashedPassword sql.NullString
    RoleId         sql.NullInt64
    FirstName      sql.NullString
    LastName       sql.NullString
    Gender         sql.NullString
    DateOfBirth    mysql.NullTime
    Height         sql.NullFloat64
    CurrentWeight  sql.NullFloat64
    CreatedAt      mysql.NullTime
    ConfirmedAt    mysql.NullTime
    LastActivityAt mysql.NullTime
    DeletedAt      mysql.NullTime
}

func UserByEmail(email string) *User {
    db := db.DB()
    u := new(User)
    rows, err := db.Query("CALL User_ByEmail(?)", email) << A SELECT *... works but this doesn't

    if err != nil {
        panic(err)
    }
    fmt.Println(rows.Columns())

    for rows.Next() {
        if err := rows.Scan(
            &u.Id,
            &u.Email,
            &u.HashedPassword,
            &u.RoleId,
            &u.FirstName,
            &u.LastName,
            &u.Gender,
            &u.DateOfBirth,
            &u.Height,
            &u.CurrentWeight,
            &u.CreatedAt,
            &u.ConfirmedAt,
            &u.LastActivityAt,
            &u.DeletedAt); err != nil {
            panic(err)
        }
    }

    if err := rows.Err(); err != nil {
        panic(err)
    }
    fmt.Println(u)
    return u
}

How do you call the procedure? `CALL MobiFit.User_ByEmail('email@domain.com')`? — Mad Dog Tannen, Jan 17 '14 at 14:54

score 6 · Accepted Answer · answered Jan 17 '14 at 20:02

6

With MySQL you cannot call stored procedures currently, and nor can you execute multiple statements at once apparently.

http://go-database-sql.org/surprises.html

answered Jan 17 '14 at 20:02

Lee

8,354
14
55
90

score 2 · Answer 2 · answered Jan 17 '14 at 14:55

2

Try using single qoutes.

LIKE THIS

CALL User_ByEmail('?');

When input parameter is of type VARCHAR, then you should Always use single quotes around the value.

answered Jan 17 '14 at 14:55

Mad Dog Tannen

7,129
5
31
55

OK, I did that I now get ```panic: sql: statement expects 0 inputs; got 1 [recovered] panic: sql: statement expects 0 inputs; got 1``` – Lee Jan 17 '14 at 15:07
Can you add the '' in the `, Email` variable? Can you try to determine what the final syntax is? – Mad Dog Tannen Jan 17 '14 at 15:12
I switched the call to rows, err := db.Query("CALL User_ByEmail('lee@g.com')") and now I gt the original error – Lee Jan 17 '14 at 15:23
Seems to me this is not a MySQL problem. Does this relate to your problem? http://stackoverflow.com/questions/2842263/why-am-i-getting-mysql-error-1312-when-using-a-simple-stored-procedure – Mad Dog Tannen Jan 17 '14 at 15:27
I guess there must be a bug with the driver. – Lee Jan 17 '14 at 15:30

Go MySQL stored procedure cannot return result set

2 Answers2