Last active
December 19, 2018 01:18
-
-
Save MasayukiOzawa/504fc2f550c1768a564cd564e7cc4a23 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package main | |
import ( | |
"context" | |
"database/sql" | |
"encoding/json" | |
"fmt" | |
"log" | |
"os" | |
"time" | |
_ "github.com/denisenkom/go-mssqldb" | |
) | |
var ( | |
server = "xxx.xxx.xxx.xxx" | |
user = "xxxxxxxx" | |
password = "xxxxxxx" | |
database = "xxxxx" | |
dialTimeout = 10 | |
connectionTimeout = 0 | |
) | |
type region struct { | |
Regionkey int | |
Name string | |
Comment string | |
} | |
const timeFormat = "2006-01-02 15:04:05.000" | |
func writeMessage(m string) { | |
fmt.Printf("[%v]:%s\n", time.Now().Format(timeFormat), m) | |
} | |
func main() { | |
var err error | |
constring := fmt.Sprintf("server=%s;user id=%s;password=%s;database=%s;dial timeout=%d;connection timeout=%d", | |
server, | |
user, | |
password, | |
database, | |
dialTimeout, | |
connectionTimeout) | |
// Open した際には、クエリの実行は行われていないため、この時点では、接続確認は実行されていない | |
db, err := sql.Open("sqlserver", constring) | |
if err != nil { | |
log.Fatal("SQL Sever DB Ojbect Create Error :", err.Error()) | |
} | |
defer db.Close() | |
// 制御用のコンテキストの作成 | |
ctx := context.Background() | |
t1 := time.Now() | |
// 接続確認 (この段階で接続文字列が使用される) | |
writeMessage("Ping Context Start") | |
// dial timeout の秒数が実行時間に影響する (Default : 15 sec) | |
// [select 1;] の実行により、クエリが実行できることを確認している | |
err = db.PingContext(ctx) | |
fmt.Println(time.Now().Sub(t1)) | |
if err != nil { | |
log.Fatal("SQL Sever Connection Open Error : ", err.Error()) | |
} | |
writeMessage("Ping Context End (Connected)") | |
sql := ` | |
BEGIN TRAN | |
INSERT INTO T1 VALUES(1) | |
WAITFOR DELAY '00:00:20' | |
COMMIT TRAN | |
` | |
// コンテキストをコマンドタイムアウトの設定として利用 (設定していない場合、CommandTimeout = 0) | |
ctx, cancel := context.WithTimeout(ctx, time.Second*1) | |
defer cancel() | |
t1 = time.Now() | |
writeMessage("Query Execution") | |
_, err = db.ExecContext(ctx, sql) | |
fmt.Println(time.Now().Sub(t1)) | |
if err != nil { | |
log.Println("Query Timeout : ", err.Error()) | |
} | |
writeMessage("Query Execution End") | |
// SELECT のクエリ実行 | |
writeMessage("SELECT Query Start") | |
sql = ` | |
SELECT * FROM REGION | |
` | |
regionRec := make([]region, 0) | |
ctx = context.Background() | |
rows, err := db.QueryContext(ctx, sql) | |
if err != nil { | |
log.Println("SELECT Error : ", err.Error()) | |
} | |
fmt.Printf("%v\n", rows) | |
for rows.Next() { | |
r := new(region) | |
_ = rows.Scan(&r.Regionkey, &r.Name, &r.Comment) | |
regionRec = append(regionRec, *r) | |
} | |
fmt.Println(regionRec) | |
rows.Close() | |
writeMessage("SELECT Query End") | |
// 取得結果を JSON にエンコード | |
bs, _ := json.MarshalIndent(®ionRec, "", " ") | |
fmt.Println(string(bs)) | |
// エクスポート用のディレクトリ作成 | |
if _, err := os.Stat("C:\\sqlexport"); os.IsNotExist(err) { | |
writeMessage("Export Directory Not Exists") | |
_ = os.Mkdir("C:\\sqlexport", 0644) | |
} | |
// ファイルの書き込み | |
f := new(os.File) | |
if _, err := os.Stat("c:\\sqlexport\\export.txt"); !os.IsNotExist(err) { | |
writeMessage("File Exists") | |
f, _ = os.OpenFile("C:\\sqlexport\\export.txt", os.O_APPEND|os.O_CREATE, 0644) | |
f.Write([]byte("\n")) | |
} else { | |
writeMessage("File Not Exists") | |
f, _ = os.Create("c:\\sqlexport\\export.txt") | |
} | |
f.Write(bs) | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package main | |
import ( | |
"database/sql" | |
"fmt" | |
"runtime" | |
"sync" | |
"time" | |
_ "github.com/denisenkom/go-mssqldb" | |
) | |
var db *sql.DB | |
var ( | |
server = "xxx.xxx.xxx.xxx" | |
user = "xxxxxxxx" | |
password = "xxxxxxx" | |
database = "xxxxx" | |
dialTimeout = 10 | |
connectionTimeout = 0 | |
) | |
var wg sync.WaitGroup | |
const timeFormat = "2006-01-02 15:04:05.000" | |
func main() { | |
constring := fmt.Sprintf("server=%s;user id=%s;password=%s;database=%s;dial timeout=%d;connection timeout=%d", | |
server, | |
user, | |
password, | |
database, | |
dialTimeout, | |
connectionTimeout, | |
) | |
runtime.GOMAXPROCS(runtime.NumCPU()) | |
db, _ = sql.Open("sqlserver", constring) | |
defer db.Close() | |
basesql := "WAITFOR DELAY '00:00:05'" | |
sql := make([]string, 1000) | |
for i := 0; i < len(sql); i++ { | |
sql[i] = basesql | |
} | |
wg.Add(len(sql)) | |
fmt.Printf("[%s]Query Start\n", time.Now().Format(timeFormat)) | |
t1 := time.Now() | |
for _, s := range sql { | |
go func(sql string) { | |
_, _ = db.Exec(sql) | |
wg.Done() | |
}(s) | |
} | |
wg.Wait() | |
fmt.Printf("[%s]Query End\n", time.Now().Format(timeFormat)) | |
fmt.Println(time.Now().Sub(t1)) | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment