英文:
Go MySQL stored procedure cannot return result set
问题
如果我运行下面的代码，但将'CALL'替换为"SELECT * FROM User LIMIT 1"，我会得到一个用户。如果我切换到调用存储过程，我会一直收到以下错误信息：
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
我该如何调用我的存储过程？
这是我的存储过程：
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
}
英文:
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
}
答案1
得分: 6
使用MySQL时，目前无法调用存储过程，也无法同时执行多个语句。你可以参考这个链接了解更多信息：http://go-database-sql.org/surprises.html
英文:
With MySQL you cannot call stored procedures currently, and nor can you execute multiple statements at once apparently.
答案2
得分: 2
尝试使用单引号。
像这样：
CALL User_ByEmail('?');
当输入参数的类型为VARCHAR时，你应该始终在值周围使用单引号。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论