2

I'm trying to run the query below, that is stored in a .sql file, then read with ioutils.ReadFile and executed on initialization 

CREATE TABLE IF NOT EXISTS districts
(
    GeoCode integer PRIMARY KEY,
    name    varchar(32)
);

drop procedure if exists insert_district;

DELIMITER $$

CREATE PROCEDURE insert_district(in pgeocode int, in pname varchar(32))
BEGIN
    INSERT INTO districts(geocode, name) VALUES (pgeocode, pname);
    SELECT * FROM districts where geocode = pgeocode;
END$$
DELIMITER ;

I am using the database/sql package and run the query with Exec 

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'drop procedure if exists insert_district;

DELIMITER $$

CREATE PROCEDURE insert' at line 7

To the best of my knowledge my syntax is correct, and I tested it so I cannot figure out why the same exact query cannot be run properly from the program.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Konkovac
  • 41
  • 6

1 Answers1

5

The Go MySQL client does not support multiple SQL statements by default. You can't just feed it a text file with ; separated statements.

See Does a Go Mysql driver exist that supports multiple statements within a single string? for details — there's an option you can use to allow multi-statements.

But that still won't support statements like DELIMITER which are not recognized by the MySQL Server. That's a mysql client command.

You have two alternatives:

  • Parse the .sql file to find statement terminators and run the statements one at a time.
  • Execute the mysql client in a sub-process using the .sql file as input.
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thank you I decided to use multiple statments, although I had to remove the delimiter command. – Konkovac Mar 13 '20 at 12:40
  • Sure, you don't need `DELIMITER` unless you are running the whole file as a script with the `mysql` client. The purpose of `DELIMITER` is to solve the ambiguity of `;` being a statement terminator for statements inside the procedure body, but also a statement terminator for the whole CREATE PROCEDURE statement. If you execute one statement at a time with the API, there's no ambiguity. – Bill Karwin Mar 13 '20 at 16:01