Gary Willoughby Gary Willoughby - 5 months ago 8
SQL Question

Does a Go Mysql driver exist that supports multiple statements within a single string?

I'm trying to find a MySql driver that i can use with Go which supports issuing multiple SQL statements in one call. For example i might wish to create a database using the following SQL:

DROP SCHEMA IF EXISTS foo;
CREATE SCHEMA IF NOT EXISTS foo;


In languages such as PHP you can just place both SQL statements in one string and execute it in one go, like this:

$db = new PDO(...);
$db->query("DROP SCHEMA IF EXISTS foo; CREATE SCHEMA IF NOT EXISTS foo;");


The reason i need this is because i have SQL dumps (from
mysqldump
) i'd like to apply programmatically to various databases.

I'm looking for the same functionality in Go but it seems all the different drivers don't support it, which, frankly, is shocking to me.

Go-MySQL-Driver

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

This seems to be the most used driver for Go.

package main

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

func main() {

db, err := sql.Open("mysql", "user:password@(127.0.0.1:3306)/")
if err != nil {
log.Println(err)
}

sql := "DROP SCHEMA IF EXISTS foo; CREATE SCHEMA IF NOT EXISTS foo;"
_, err = db.Exec(sql)
if err != nil {
log.Println(err)
}

db.Close()
}


output:

2015/02/16 18:58:08 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 'CREATE SCHEMA IF NOT EXISTS foo' at line 1


MyMySQL

https://github.com/ziutek/mymysql

This is another popular driver.

package main

import "database/sql"
import "log"
import _ "github.com/ziutek/mymysql/godrv"

func main() {

db, err := sql.Open("mymysql", "database/user/password")
if err != nil {
log.Println(err)
}

sql := "DROP SCHEMA IF EXISTS foo; CREATE SCHEMA IF NOT EXISTS foo;"
_, err = db.Exec(sql)
if err != nil {
log.Println(err)
}

sql = "USE DATABASE foo;"
_, err = db.Exec(sql) // <-- error
if err != nil {
log.Println(err)
}

db.Close()
}


output:

2015/02/16 18:58:08 packet sequence error


Does anyone know of any MySql driver compatible with Go that can handle multiple statements in one string like these?

Answer

https://github.com/ziutek/mymysql

Can do it. Although you have to use its interface vs the go defined one. The go official interface doesn't handle it, or multiple return values.

package main

import (
    "flag"
    "fmt"

    "github.com/ziutek/mymysql/autorc"
    "github.com/ziutek/mymysql/mysql"
    _ "github.com/ziutek/mymysql/thrsafe"
)

type ScanFun func(int, []mysql.Row, mysql.Result) error

func RunSQL(hostport, user, pass, db, cmd string, scan ScanFun) error {
    conn := autorc.New("tcp", "", hostport, user, pass, db)

    err := conn.Reconnect()
    if err != nil {
        return err
    }

    res, err := conn.Raw.Start(cmd)
    if err != nil {
        return err
    }

    rows, err := res.GetRows()
    if err != nil {
        return err
    }

    RScount := 0
    scanErr := error(nil)

    for {
        if scanErr == nil {
            func() {
                defer func() {
                    if x := recover(); x != nil {
                        scanErr = fmt.Errorf("%v", x)
                    }
                }()
                scanErr = scan(RScount, rows, res)
            }()
        }

        if res.MoreResults() {
            res, err = res.NextResult()
            if err != nil {
                return err
            }
            rows, err = res.GetRows()
            if err != nil {
                return err
            }
        } else {
            break
        }

        RScount++
    }
    return scanErr
}

func main() {
    host := flag.String("host", "localhost:3306", "define the host where the db is")
    user := flag.String("user", "root", "define the user to connect as")
    pass := flag.String("pass", "", "define the pass to use")
    db := flag.String("db", "information_schema", "what db to default to")

    sql := flag.String("sql", "select count(*) from columns; select * from columns limit 1;", "Query to run")

    flag.Parse()

    scan := func(rcount int, rows []mysql.Row, res mysql.Result) error {
        if res.StatusOnly() {
            return nil
        }

        for idx, row := range rows {
            fmt.Print(rcount, "-", idx, ") ")
            for i, _ := range row {
                fmt.Print(row.Str(i))
                fmt.Print(" ")
            }
            fmt.Println("")
        }
        return nil
    }

    fmt.Println("Host - ", *host)
    fmt.Println("Db   - ", *db)
    fmt.Println("User - ", *user)

    if err := RunSQL(*host, *user, *pass, *db, *sql, scan); err != nil {
        fmt.Println(err)
    }
}
Comments