xorm/manual-en-US
Archived
9
1
Fork 2
Code Issues Pull Requests Releases Wiki Activity

Stored procedure support for mysql #4

New Issue
Closed
opened 2016-03-16 10:33:01 +00:00 by coolmandar · 6 comments
coolmandar commented 2016-03-16 10:33:01 +00:00 (Migrated from github.com)
Copy Link

Hi,

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

Hi, How we can support stored procedure to fetch data from mysql using xorm
lunny commented 2016-03-16 14:54:57 +00:00
Copy Link

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

This is an interesting topic. You can just call `engine.Query("call yourfunc()")` to do that.
coolmandar commented 2016-03-17 17:33:24 +00:00 (Migrated from github.com)
Copy Link

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

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 commented 2016-05-03 18:05:15 +00:00 (Migrated from github.com)
Copy Link

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

Hi, After executing engine.Query("call yourfunc()") I got following error PROCEDURE "yourfunc()" can't return a result set in the given context
lunny commented 2016-05-04 00:39:29 +00:00
Copy Link

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

The driver don't support multiple result sets return on PROCEDURE. Could you confirm if your PROCEDURE return multiple result sets?
coolmandar commented 2016-05-04 03:26:14 +00:00 (Migrated from github.com)
Copy Link

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

image

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

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 ![image](https://cloud.githubusercontent.com/assets/130304/15004124/ba5621b8-11d0-11e6-857c-6d6deb8371e3.png) 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 commented 2016-07-14 02:11:27 +00:00
Copy Link

The error is returned by mysql driver, maybe you can visit https://github.com/go-sql-driver/mysql/issues/66 to follow the issue.

The error is returned by mysql driver, maybe you can visit https://github.com/go-sql-driver/mysql/issues/66 to follow the issue.
This repo is archived. You cannot comment on issues.
No Branch/Tag Specified
Branches Tags
master
appleboy-patch-1
No results found.
Labels
Clear labels   
bug

   
duplicate

   
enhancement

   
help wanted

   
invalid

   
question

   
wontfix
No Label
bug
duplicate
enhancement
help wanted
invalid
question
wontfix
Milestone
Clear milestone
No items
No Milestone
Assignees
Clear assignees
No Assignees
1 Participants
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: xorm/manual-en-US#4
No description provided.
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?