This example shows how to perform bulk imports

Code:play 

//go:build go1.10
// +build go1.10

package main

import (
	"database/sql"
	"flag"
	"fmt"
	"log"
	"strings"
	"unicode/utf8"

	"github.com/denisenkom/go-mssqldb"
)

const (
	createTestTable = `CREATE TABLE test_table(
		id int IDENTITY(1,1) NOT NULL,
		test_nvarchar nvarchar(50) NULL,
		test_varchar varchar(50) NULL,
		test_float float NULL,
		test_datetime2_3 datetime2(3) NULL,
		test_bitn bit NULL,
		test_bigint bigint NOT NULL,
		test_geom geometry NULL,
	CONSTRAINT PK_table_test_id PRIMARY KEY CLUSTERED
	(
		id ASC
	) ON [PRIMARY]);`
	dropTestTable = "IF OBJECT_ID('test_table', 'U') IS NOT NULL DROP TABLE test_table;"
)

// This example shows how to perform bulk imports
func main() {
	flag.Parse()

	if *debug {
		fmt.Printf(" password:%s\n", *password)
		fmt.Printf(" port:%d\n", *port)
		fmt.Printf(" server:%s\n", *server)
		fmt.Printf(" user:%s\n", *user)
	}

	connString := makeConnURL().String()
	if *debug {
		fmt.Printf(" connString:%s\n", connString)
	}

	db, err := sql.Open("sqlserver", connString)
	if err != nil {
		log.Fatal("Open connection failed:", err.Error())
	}
	defer db.Close()

	txn, err := db.Begin()
	if err != nil {
		log.Fatal(err)
	}

	// Create table
	_, err = db.Exec(createTestTable)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Exec(dropTestTable)

	// mssqldb.CopyIn creates string to be consumed by Prepare
	stmt, err := txn.Prepare(mssql.CopyIn("test_table", mssql.BulkOptions{}, "test_varchar", "test_nvarchar", "test_float", "test_bigint"))
	if err != nil {
		log.Fatal(err.Error())
	}

	for i := 0; i < 10; i++ {
		_, err = stmt.Exec(generateString(0, 30), generateStringUnicode(0, 30), i, i)
		if err != nil {
			log.Fatal(err.Error())
		}
	}

	result, err := stmt.Exec()
	if err != nil {
		log.Fatal(err)
	}

	err = stmt.Close()
	if err != nil {
		log.Fatal(err)
	}

	err = txn.Commit()
	if err != nil {
		log.Fatal(err)
	}
	rowCount, _ := result.RowsAffected()
	log.Printf("%d row copied\n", rowCount)
	log.Printf("bye\n")
}

func generateString(x int, n int) string {
	letters := "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
	b := make([]byte, n)
	for i := range b {
		b[i] = letters[(x+i)%len(letters)]
	}
	return string(b)
}
func generateStringUnicode(x int, n int) string {
	letters := []byte("ab©💾é?ghïjklmnopqЯ☀tuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")
	b := &strings.Builder{}
	for i := 0; i < n; i++ {
		r, sz := utf8.DecodeRune(letters[x%len(letters):])
		x += sz
		b.WriteRune(r)
	}
	return b.String()
}

This example shows the usage of Connector type

Code:play 

//go:build go1.10
// +build go1.10

package main

import (
	"context"
	"database/sql"
	"flag"
	"fmt"
	"log"
	"net/url"
	"strconv"

	mssql "github.com/denisenkom/go-mssqldb"
)

var (
	debug         = flag.Bool("debug", false, "enable debugging")
	password      = flag.String("password", "", "the database password")
	port     *int = flag.Int("port", 1433, "the database port")
	server        = flag.String("server", "", "the database server")
	user          = flag.String("user", "", "the database user")
)

const (
	createTableSql      = "CREATE TABLE TestAnsiNull (bitcol bit, charcol char(1));"
	dropTableSql        = "IF OBJECT_ID('TestAnsiNull', 'U') IS NOT NULL DROP TABLE TestAnsiNull;"
	insertQuery1        = "INSERT INTO TestAnsiNull VALUES (0, NULL);"
	insertQuery2        = "INSERT INTO TestAnsiNull VALUES (1, 'a');"
	selectNullFilter    = "SELECT bitcol FROM TestAnsiNull WHERE charcol = NULL;"
	selectNotNullFilter = "SELECT bitcol FROM TestAnsiNull WHERE charcol <> NULL;"
)

func makeConnURL() *url.URL {
	return &url.URL{
		Scheme: "sqlserver",
		Host:   *server + ":" + strconv.Itoa(*port),
		User:   url.UserPassword(*user, *password),
	}
}

// This example shows the usage of Connector type
func main() {
	flag.Parse()

	if *debug {
		fmt.Printf(" password:%s\n", *password)
		fmt.Printf(" port:%d\n", *port)
		fmt.Printf(" server:%s\n", *server)
		fmt.Printf(" user:%s\n", *user)
	}

	connString := makeConnURL().String()
	if *debug {
		fmt.Printf(" connString:%s\n", connString)
	}

	// Create a new connector object by calling NewConnector
	connector, err := mssql.NewConnector(connString)
	if err != nil {
		log.Println(err)
		return
	}

	// Use SessionInitSql to set any options that cannot be set with the dsn string
	// With ANSI_NULLS set to ON, compare NULL data with = NULL or <> NULL will return 0 rows
	connector.SessionInitSQL = "SET ANSI_NULLS ON"

	// Pass connector to sql.OpenDB to get a sql.DB object
	db := sql.OpenDB(connector)
	defer db.Close()

	// Create and populate table
	_, err = db.Exec(createTableSql)
	if err != nil {
		log.Println(err)
		return
	}
	defer db.Exec(dropTableSql)
	_, err = db.Exec(insertQuery1)
	if err != nil {
		log.Println(err)
		return
	}
	_, err = db.Exec(insertQuery2)
	if err != nil {
		log.Println(err)
		return
	}

	var bitval bool
	ctx, cancel := context.WithCancel(context.Background())
	defer cancel()

	// (*Row) Scan should return ErrNoRows since ANSI_NULLS is set to ON
	err = db.QueryRowContext(ctx, selectNullFilter).Scan(&bitval)
	if err != nil {
		if err.Error() != "sql: no rows in result set" {
			log.Println(err)
			return
		}
	} else {
		log.Println("Expects an ErrNoRows error. No error is returned")
		return
	}

	// (*Row) Scan should return ErrNoRows since ANSI_NULLS is set to ON
	err = db.QueryRowContext(ctx, selectNotNullFilter).Scan(&bitval)
	if err != nil {
		if err.Error() != "sql: no rows in result set" {
			log.Println(err)
			return
		}
	} else {
		log.Println("Expects an ErrNoRows error. No error is returned")
		return
	}

	// Set ANSI_NULLS to OFF
	connector.SessionInitSQL = "SET ANSI_NULLS OFF"

	// (*Row) Scan should copy data to bitval
	err = db.QueryRowContext(ctx, selectNullFilter).Scan(&bitval)
	if err != nil {
		log.Println(err)
		return
	}
	if bitval != false {
		log.Println("Incorrect value retrieved.")
		return
	}

	// (*Row) Scan should copy data to bitval
	err = db.QueryRowContext(ctx, selectNotNullFilter).Scan(&bitval)
	if err != nil {
		log.Println(err)
		return
	}
	if bitval != true {
		log.Println("Incorrect value retrieved.")
		return
	}
}

This example shows how to insert and retrieve date and time types data

Code:play 

//go:build go1.10
// +build go1.10

package main

import (
	"database/sql"
	"flag"
	"fmt"
	"log"
	"time"

	"github.com/denisenkom/go-mssqldb"
	"github.com/golang-sql/civil"
)

// This example shows how to insert and retrieve date and time types data
func main() {
	flag.Parse()

	if *debug {
		fmt.Printf(" password:%s\n", *password)
		fmt.Printf(" port:%d\n", *port)
		fmt.Printf(" server:%s\n", *server)
		fmt.Printf(" user:%s\n", *user)
	}

	connString := makeConnURL().String()
	if *debug {
		fmt.Printf(" connString:%s\n", connString)
	}

	db, err := sql.Open("sqlserver", connString)
	if err != nil {
		log.Fatal("Open connection failed:", err.Error())
	}
	defer db.Close()

	insertDateTime(db)
	retrieveDateTime(db)
	retrieveDateTimeOutParam(db)
}

func insertDateTime(db *sql.DB) {
	_, err := db.Exec("CREATE TABLE datetimeTable (timeCol TIME, dateCol DATE, smalldatetimeCol SMALLDATETIME, datetimeCol DATETIME, datetime2Col DATETIME2, datetimeoffsetCol DATETIMEOFFSET)")
	if err != nil {
		log.Fatal(err)
	}
	stmt, err := db.Prepare("INSERT INTO datetimeTable VALUES(@p1, @p2, @p3, @p4, @p5, @p6)")
	if err != nil {
		log.Fatal(err)
	}
	tin, err := time.Parse(time.RFC3339, "2006-01-02T22:04:05.787-07:00")
	if err != nil {
		log.Fatal(err)
	}
	var timeCol civil.Time = civil.TimeOf(tin)
	var dateCol civil.Date = civil.DateOf(tin)
	var smalldatetimeCol string = "2006-01-02 22:04:00"
	var datetimeCol mssql.DateTime1 = mssql.DateTime1(tin)
	var datetime2Col civil.DateTime = civil.DateTimeOf(tin)
	var datetimeoffsetCol mssql.DateTimeOffset = mssql.DateTimeOffset(tin)
	_, err = stmt.Exec(timeCol, dateCol, smalldatetimeCol, datetimeCol, datetime2Col, datetimeoffsetCol)
	if err != nil {
		log.Fatal(err)
	}
}

func retrieveDateTime(db *sql.DB) {
	rows, err := db.Query("SELECT timeCol, dateCol, smalldatetimeCol, datetimeCol, datetime2Col, datetimeoffsetCol FROM datetimeTable")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	var c1, c2, c3, c4, c5, c6 time.Time
	for rows.Next() {
		err = rows.Scan(&c1, &c2, &c3, &c4, &c5, &c6)
		if err != nil {
			log.Fatal(err)
		}
		fmt.Printf("c1: %+v; c2: %+v; c3: %+v; c4: %+v; c5: %+v; c6: %+v;\n", c1, c2, c3, c4, c5, c6)
	}
}

func retrieveDateTimeOutParam(db *sql.DB) {
	CreateProcSql := `
	CREATE PROCEDURE OutDatetimeProc
		@timeOutParam TIME OUTPUT,
		@dateOutParam DATE OUTPUT,
		@smalldatetimeOutParam SMALLDATETIME OUTPUT,
		@datetimeOutParam DATETIME OUTPUT,
		@datetime2OutParam DATETIME2 OUTPUT,
		@datetimeoffsetOutParam DATETIMEOFFSET OUTPUT
	AS
		SET NOCOUNT ON
		SET @timeOutParam = '22:04:05.7870015'
		SET @dateOutParam = '2006-01-02'
		SET @smalldatetimeOutParam = '2006-01-02 22:04:00'
		SET @datetimeOutParam = '2006-01-02 22:04:05.787'
		SET @datetime2OutParam = '2006-01-02 22:04:05.7870015'
		SET @datetimeoffsetOutParam = '2006-01-02 22:04:05.7870015 -07:00'`
	_, err := db.Exec(CreateProcSql)
	if err != nil {
		log.Fatal(err)
	}
	var (
		timeOutParam, datetime2OutParam, datetimeoffsetOutParam mssql.DateTimeOffset
		dateOutParam, datetimeOutParam                          mssql.DateTime1
		smalldatetimeOutParam                                   string
	)
	_, err = db.Exec("OutDatetimeProc",
		sql.Named("timeOutParam", sql.Out{Dest: &timeOutParam}),
		sql.Named("dateOutParam", sql.Out{Dest: &dateOutParam}),
		sql.Named("smalldatetimeOutParam", sql.Out{Dest: &smalldatetimeOutParam}),
		sql.Named("datetimeOutParam", sql.Out{Dest: &datetimeOutParam}),
		sql.Named("datetime2OutParam", sql.Out{Dest: &datetime2OutParam}),
		sql.Named("datetimeoffsetOutParam", sql.Out{Dest: &datetimeoffsetOutParam}))
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("timeOutParam: %+v; dateOutParam: %+v; smalldatetimeOutParam: %s; datetimeOutParam: %+v; datetime2OutParam: %+v; datetimeoffsetOutParam: %+v;\n", time.Time(timeOutParam), time.Time(dateOutParam), smalldatetimeOutParam, time.Time(datetimeOutParam), time.Time(datetime2OutParam), time.Time(datetimeoffsetOutParam))
}

This example shows how to use tvp type

Code:

{
	const (
		createTable = "CREATE TABLE Location (Name VARCHAR(50), CostRate INT, Availability BIT, ModifiedDate DATETIME2)"

		dropTable = "IF OBJECT_ID('Location', 'U') IS NOT NULL DROP TABLE Location"

		createTVP = `CREATE TYPE LocationTableType AS TABLE
		(LocationName VARCHAR(50),
		CostRate INT)`

		dropTVP = "IF type_id('LocationTableType') IS NOT NULL DROP TYPE LocationTableType"

		createProc = `CREATE PROCEDURE dbo.usp_InsertProductionLocation
		@TVP LocationTableType READONLY
		AS
		SET NOCOUNT ON
		INSERT INTO Location
		(
			Name,
			CostRate,
			Availability,
			ModifiedDate)
			SELECT *, 0,GETDATE()
			FROM @TVP`

		dropProc = "IF OBJECT_ID('dbo.usp_InsertProductionLocation', 'P') IS NOT NULL DROP PROCEDURE dbo.usp_InsertProductionLocation"

		execTvp = "exec dbo.usp_InsertProductionLocation @TVP;"
	)
	type LocationTableTvp struct {
		LocationName    string
		LocationCountry string `tvp:"-"`
		CostRate        int64
		Currency        string `json:"-"`
	}

	flag.Parse()

	if *debug {
		fmt.Printf(" password:%s\n", *password)
		fmt.Printf(" port:%d\n", *port)
		fmt.Printf(" server:%s\n", *server)
		fmt.Printf(" user:%s\n", *user)
	}

	connString := makeConnURL().String()
	if *debug {
		fmt.Printf(" connString:%s\n", connString)
	}

	db, err := sql.Open("sqlserver", connString)
	if err != nil {
		log.Fatal("Open connection failed:", err.Error())
	}
	defer db.Close()

	_, err = db.Exec(createTable)
	if err != nil {
		log.Fatal(err)
	}
	_, err = db.Exec(createTVP)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Exec(dropTVP)
	_, err = db.Exec(createProc)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Exec(dropProc)

	locationTableTypeData := []LocationTableTvp{
		{
			LocationName:    "Alberta",
			LocationCountry: "Canada",
			CostRate:        0,
			Currency:        "CAD",
		},
		{
			LocationName:    "British Columbia",
			LocationCountry: "Canada",
			CostRate:        1,
			Currency:        "CAD",
		},
	}

	tvpType := mssql.TVP{
		TypeName: "LocationTableType",
		Value:    locationTableTypeData,
	}

	_, err = db.Exec(execTvp, sql.Named("TVP", tvpType))
	if err != nil {
		log.Fatal(err)
	} else {
		for _, locationData := range locationTableTypeData {
			fmt.Printf("Data for location %s, %s has been inserted.\n", locationData.LocationName, locationData.LocationCountry)
		}
	}
}