Create mysql procedure with GOLang

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?

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 $$
     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?

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.

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

I tested myself now and it worked

package main

import (
	_ ""

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

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

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

And then inside the mysql client

mysql> \u mytable

*************************** 1. row ***************************
           Procedure: getall
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `getall`()
	  SELECT * FROM mytable;
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_swedish_ci
1 row in set (0.00 sec)

That did the trick. thanks for the help.

1 Like

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