Stored procedure support for mysql

[coolmandar]

Hi,

How we can support stored procedure to fetch data from mysql using xorm

[lunny]

This is an interesting topic. You can just call engine.Query("call yourfunc()") to do that.

[coolmandar]

Thank you for providing details,

I am attaching source code and db script.
Here I am able to get data from database using select statement, but after call stored procedure getting error
"Error: %d Error 1312: PROCEDURE location.getAllLocation can't return a result se
t in the given context"

Can you help on it?

Execution environment
Golang version - 1.5.3 (32 bit)
Database - Mysql

Go program

package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/go-xorm/xorm"
)

func main() {
fmt.Println("Starting application")

engine, err := xorm.NewEngine("mysql", "root:welcome@/location?charset=utf8")

if err != nil {
    fmt.Println("Database error")
    fmt.Println(err)
    return
}

result, err := engine.Query("select * from location_tbl;")
fmt.Println("Database result: %s", result)
fmt.Println("Error: %d", err)

result, err = engine.Query("call getAllLocation()")
fmt.Println("Database result: %s", result)
fmt.Println("Error: %d", err)

}

Database Script

CREATE DATABASE IF NOT EXISTS location /*!40100 DEFAULT CHARACTER SET utf8 */;
USE location;

-- MySQL dump 10.13 Distrib 5.7.9, for Win32 (AMD64)

-- Host: localhost Database: location

---

-- Server version 5.5.47

/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE='+00:00' /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Table structure for table location_tbl

DROP TABLE IF EXISTS location_tbl;
/!40101 SET @saved_cs_client = @@character_set_client */;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE location_tbl (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(45) DEFAULT NULL,
description varchar(150) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/!40101 SET character_set_client = @saved_cs_client */;

-- Dumping data for table location_tbl

LOCK TABLES location_tbl WRITE;
/!40000 ALTER TABLE location_tbl DISABLE KEYS */;
INSERT INTO location_tbl VALUES (3,'loc1','test12'),(4,'location2','test2'),(5,'lcoation1','loca2');
/!40000 ALTER TABLE location_tbl ENABLE KEYS */;
UNLOCK TABLES;

-- Dumping routines for database 'location'

/!50003 DROP PROCEDURE IF EXISTS getAllLocation */;
/!50003 SET @saved_cs_client = @@character_set_client / ;
/!50003 SET @saved_cs_results = @@character_set_results / ;
/!50003 SET @saved_col_connection = @@collation_connection / ;
/!50003 SET character_set_client = utf8 / ;
/!50003 SET character_set_results = utf8 / ;
/!50003 SET collation_connection = utf8_general_ci / ;
/!50003 SET @saved_sql_mode = @@sql_mode / ;
/!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' / ;
DELIMITER ;;
CREATE DEFINER=root@localhost PROCEDURE getAllLocation()
BEGIN
Select * from location_tbl;
END ;;
DELIMITER ;
/!50003 SET sql_mode = @saved_sql_mode / ;
/!50003 SET character_set_client = @saved_cs_client / ;
/!50003 SET character_set_results = @saved_cs_results / ;
/!50003 SET collation_connection = @saved_col_connection / ;
/!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2016-03-17 22:49:02

[coolmandar]

Hi,
After executing engine.Query("call yourfunc()")
I got following error
PROCEDURE "yourfunc()" can't return a result set in the given context

[lunny]

The driver don't support multiple result sets return on PROCEDURE. Could you confirm if your PROCEDURE return multiple result sets?

[coolmandar]

I am using following procedure called from go code

CREATE DEFINER=root@localhost PROCEDURE getAllLocations(
)
BEGIN
Select * from location_tbl;
END

After executing procedure in MySQL Workbench, I can see following result set

I can confirm that procedure returning only single result set. Let me know if you need any more input to solove this issue

I am working with liteIDE, X 27
GO 1.6
MySQL 5.7
MySQL ODBC driver version 5.3.6 (I believe ODBC driver used internally)

Also if you can provide sample to print rows and column values will be very helpful.
Thanks

[lunny]

The error is returned by mysql driver, maybe you can visit go-sql-driver/mysql#66 to follow the issue.