1// +build go1.10
2
3package mssql_test
4
5import (
6	"database/sql"
7	"flag"
8	"fmt"
9	"log"
10	"strings"
11	"unicode/utf8"
12
13	"github.com/denisenkom/go-mssqldb"
14)
15
16const (
17	createTestTable = `CREATE TABLE test_table(
18		id int IDENTITY(1,1) NOT NULL,
19		test_nvarchar nvarchar(50) NULL,
20		test_varchar varchar(50) NULL,
21		test_float float NULL,
22		test_datetime2_3 datetime2(3) NULL,
23		test_bitn bit NULL,
24		test_bigint bigint NOT NULL,
25		test_geom geometry NULL,
26	CONSTRAINT PK_table_test_id PRIMARY KEY CLUSTERED
27	(
28		id ASC
29	) ON [PRIMARY]);`
30	dropTestTable = "IF OBJECT_ID('test_table', 'U') IS NOT NULL DROP TABLE test_table;"
31)
32
33// This example shows how to perform bulk imports
34func ExampleCopyIn() {
35	flag.Parse()
36
37	if *debug {
38		fmt.Printf(" password:%s\n", *password)
39		fmt.Printf(" port:%d\n", *port)
40		fmt.Printf(" server:%s\n", *server)
41		fmt.Printf(" user:%s\n", *user)
42	}
43
44	connString := makeConnURL().String()
45	if *debug {
46		fmt.Printf(" connString:%s\n", connString)
47	}
48
49	db, err := sql.Open("sqlserver", connString)
50	if err != nil {
51		log.Fatal("Open connection failed:", err.Error())
52	}
53	defer db.Close()
54
55	txn, err := db.Begin()
56	if err != nil {
57		log.Fatal(err)
58	}
59
60	// Create table
61	_, err = db.Exec(createTestTable)
62	if err != nil {
63		log.Fatal(err)
64	}
65	defer db.Exec(dropTestTable)
66
67	// mssqldb.CopyIn creates string to be consumed by Prepare
68	stmt, err := txn.Prepare(mssql.CopyIn("test_table", mssql.BulkOptions{}, "test_varchar", "test_nvarchar", "test_float", "test_bigint"))
69	if err != nil {
70		log.Fatal(err.Error())
71	}
72
73	for i := 0; i < 10; i++ {
74		_, err = stmt.Exec(generateString(0, 30), generateStringUnicode(0, 30), i, i)
75		if err != nil {
76			log.Fatal(err.Error())
77		}
78	}
79
80	result, err := stmt.Exec()
81	if err != nil {
82		log.Fatal(err)
83	}
84
85	err = stmt.Close()
86	if err != nil {
87		log.Fatal(err)
88	}
89
90	err = txn.Commit()
91	if err != nil {
92		log.Fatal(err)
93	}
94	rowCount, _ := result.RowsAffected()
95	log.Printf("%d row copied\n", rowCount)
96	log.Printf("bye\n")
97}
98
99func generateString(x int, n int) string {
100	letters := "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
101	b := make([]byte, n)
102	for i := range b {
103		b[i] = letters[(x+i)%len(letters)]
104	}
105	return string(b)
106}
107func generateStringUnicode(x int, n int) string {
108	letters := []byte("ab©��é?ghïjklmnopqЯ☀tuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")
109	b := &strings.Builder{}
110	for i := 0; i < n; i++ {
111		r, sz := utf8.DecodeRune(letters[x%len(letters):])
112		x += sz
113		b.WriteRune(r)
114	}
115	return b.String()
116}
117