Create mysql procedure with GOLang

Getting Help
1

Since the DELIMITER directive is a signal to the MySQL client to ignore the default query delimiter (a semicolon ‘;’) while creating stored routines and triggers, it is not accepted by the API as legit SQL In GoLang. Question is how to get “database/sql” to create stored routines for a MySQL database instance?

2

It’s not really used to ignore semicolon but to change the delimiter. For example you can set it to $$ so you can use ; in procedure defintion and not get the statement terminated. Like this

DELIMITER $$ -- here is delimiter changed to $$
 CREATE PROCEDURE GetAllProducts()
   BEGIN
     SELECT *  FROM products; -- now can you use semicolon here without ending the expression
   END $$ -- ending the expression
 DELIMITER ; -- changing back the delimiter

I haven’t tested this but maybe you must add the flag for multistatements to be able to run this as it really is three statments in one query. But if you just run a statement like the one above without changing the delimiter could it not work because the driver by default sees only one query?

https://github.com/go-sql-driver/mysql#multistatements

3

Thanks for the input. The DELIMITER directive is directed toward the mysql client, it tells the client to terminate the statement at it’s new delimiter. Of course, golang does not do this, and does not recognize DELIMITER as legit. so multistatement just bombs out on the first line, or any line with a semicolon. NO, there has to be something that will tell the server that this is a huge chunk of SQL that is compiled when END is reached.

4

Of course your absolutely right. Which MySQL client are you using? The one I referenced above?

5

I tested myself now and it worked

package main

import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "root:secret@/mytable")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	sql := `CREATE PROCEDURE GetAll()
	BEGIN
	  SELECT * FROM mytable;
	END`

	_, err := db.Exec(sql)
	if err != nil {
		panic(err)
	}
}

And then inside the mysql client

mysql> \u mytable

mysql> SHOW CREATE PROCEDURE getall\G
*************************** 1. row ***************************
           Procedure: getall
            sql_mode:
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `getall`()
BEGIN
	  SELECT * FROM mytable;
	END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_swedish_ci
1 row in set (0.00 sec)
1 Like
6

That did the trick. thanks for the help.

1 Like
7

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.