DB.Exec args总是导致我的占位符错误

Question

I have an SQL script with a variable I want to set from Golang.

SET @foo_bar_invitation_id = ?;
SELECT @foo_bar_invitation_id;

I.e. I want to set ? to "foobar". My code:

package main

import (
        "io/ioutil"
        "log"

        "database/sql"

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

type handler struct{ db *sql.DB }

func (h handler) runsql() (err error) {
        sqlscript, err := ioutil.ReadFile("script.sql")
        if err != nil {
                return
        }
        _, err = h.db.Exec(string(sqlscript), "foobar")
        if err != nil {
                log.Println(err)
        }
        return
}

Always results in 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 'SELECT @foo_bar_invitation_id' at line 2

I test via code generated by gotests:

import (
        "database/sql"
        "os"
        "testing"

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

var db *sql.DB

func TestMain(m *testing.M) {
        db, _ = sql.Open("mysql", os.Getenv("DSN"))
        defer db.Close()
        os.Exit(m.Run())
}

func Test_handler_runsql(t *testing.T) {
        type fields struct {
                db *sql.DB
        }
        tests := []struct {
                name    string
                fields  fields
                wantErr bool
        }{{
                "Check ID can be set",
                fields{db: db},
                false,
        }}
        for _, tt := range tests {
                t.Run(tt.name, func(t *testing.T) {
                        h := handler{
                                db: tt.fields.db,
                        }
                        if err := h.runsql(); (err != nil) != tt.wantErr {
                                t.Errorf("handler.step2runsql() error = %v, wantErr %v", err, tt.wantErr)
                        }
                })
        }
}

My DSN connection string includes ?multiStatements=true&sql_mode=TRADITIONAL.

I'm hoping I just do not understand how DB.Exec args interpolation / placeholder works, but I am finding it difficult to find examples.

Answer 1

The answer was to set interpolate params to true. https://github.com/go-sql-driver/mysql#interpolateparams