GoでDB接続

• MySQL

(8.0.22)
接続元：CentOS7
接続先：CentOS7

https://qiita.com/taizo/items/54f5f49c6102f86194b8
https://github.com/go-sql-driver/mysql

-- 1. 環境設定
-- 1.1. Goのインストール

yum install epel-release
yum info golang

yum install golang
go version

mkdir -p /usr/local/go
vim ~/.bash_profile

export GOPATH=/usr/local/go
export PATH=$PATH:$GOPATH/bin

. ~/.bash_profile

mkdir -p /usr/local/go/{bin,pkg,src}

-- 1.2. go-sql-driver/mysqlの取得

cd $GOPATH/src
go get github.com/go-sql-driver/mysql

-- 2. テストテーブル作成

drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 timestamp);
insert into tab1 values(1,'A',current_timestamp());
insert into tab1 values(2,'B',current_timestamp());
select * from tab1;

-- 3. DB接続確認

mkdir $GOPATH/src/a
cd $GOPATH/src/a

vim a.go

---
package main

import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)

func main() {
var col1 int
var col2 string
var col3 string

cnn, err := sql.Open("mysql", "test:test@tcp(mmm066:3306)/test")
if err != nil {
fmt.Println(err)
return
}
defer cnn.Close()

fmt.Println("---- select(バインド変数有) ---- ")
stmt11 ,err := cnn.Prepare("select * from tab1 where col1 >= ?")
defer stmt11.Close()
rows11, err := stmt11.Query(1)
for rows11.Next(){
rows11.Scan(&col1, &col2, &col3)
fmt.Println(col1,"\t",col2,"\t",col3)
}
fmt.Println("---- ")
rows12, err := stmt11.Query(2)
for rows12.Next(){
rows12.Scan(&col1, &col2, &col3)
fmt.Println(col1,"\t",col2,"\t",col3)
}

fmt.Println("---- select(バインド変数なし) ---- ")
rows02 ,err := cnn.Query("select * from tab1 where col1 >= 1")
defer rows02.Close()
for rows02.Next(){
rows02.Scan(&col1, &col2, &col3)
fmt.Println(col1,"\t",col2,"\t",col3)
}

fmt.Println("---- insert(バインド変数有) ---- ")
tx03, err := cnn.Begin()
defer tx03.Rollback()
stmt03, err := tx03.Prepare("insert into tab1(col1,col2,col3) values(?,?,?)")
result03, err := stmt03.Exec(100,"X","2021/01/01")
err = tx03.Commit()
ret03,err := result03.RowsAffected()
fmt.Println("ret=",ret03)

fmt.Println("---- insert(バインド変数なし) ---- ")
tx04, err := cnn.Begin()
defer tx04.Rollback()
result04, err := tx04.Exec("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')")
err = tx04.Commit()
ret04,err := result04.RowsAffected()
fmt.Println("ret=",ret04)

}

---

go run a.go

• Oracle

(19c)
接続元：CentOS7
接続先：CentOS7

https://qiita.com/qt-luigi/items/fbbe6792a77b493a58f9
https://github.com/mattn/go-oci8
https://qiita.com/kenken2go/items/79fefffc52d5215f223e
https://golang.org/pkg/database/sql/
https://qiita.com/tenntenn/items/dddb13c15643454a7c3b
https://golang.shop/post/go-databasesql-04-retrieving-ja/

-- 1. 環境設定
-- 1.1. Oracle Instant Clientのダウンロードとインストール

rpm -ivh oracle-instantclient19.9-basic-19.9.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.9-devel-19.9.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.9-sqlplus-19.9.0.0.0-1.x86_64.rpm

-- 1.2. Goのインストール

yum install epel-release
yum info golang

yum install golang
go version

mkdir -p /usr/local/go
vim ~/.bash_profile

export GOPATH=/usr/local/go
export PATH=$PATH:$GOPATH/bin

. ~/.bash_profile

mkdir -p /usr/local/go/{bin,pkg,src}

-- 1.3. oci8.pcファイルの作成

vim /root/oci8.pc

prefix=/usr
includedir=${prefix}/include/oracle/19.9/client64
libdir=${prefix}/lib/oracle/19.9/client64/lib

Name: oci8
Description: Oracle Instant Client
Version: 19.9
Cflags: -I${includedir}
Libs: -L${libdir} -lclntsh

vim ~/.bash_profile

export PKG_CONFIG_PATH=/root

. ~/.bash_profile

-- 1.4. go-oci8の取得

cd $GOPATH/src
go get github.com/mattn/go-oci8

-- 2. テストテーブル作成

drop table tab1 purge;
create table tab1(col1 int,col2 varchar2(10),col3 timestamp);
insert into tab1 values(1,'A',sysdate);
insert into tab1 values(2,'B',sysdate);
commit;
select * from tab1;

-- 3. DB接続確認

mkdir $GOPATH/src/a
cd $GOPATH/src/a

vim a.go

---
package main

import (
"database/sql"
"fmt"
_ "github.com/mattn/go-oci8"
)

func main() {
var col1 int
var col2 string
var col3 string

cnn,err := sql.Open("oci8", "test/test@mmm065:1521/pdb1.example.com")
if err != nil {
fmt.Println(err)
return
}
defer cnn.Close()

fmt.Println("---- select(バインド変数有) ---- ")
stmt11 ,err := cnn.Prepare("select * from tab1 where col1 >= :col1")
defer stmt11.Close()
rows11, err := stmt11.Query(1)
for rows11.Next(){
rows11.Scan(&col1, &col2, &col3)
fmt.Println(col1,"\t",col2,"\t",col3)
}
fmt.Println("---- ")
rows12, err := stmt11.Query(2)
for rows12.Next(){
rows12.Scan(&col1, &col2, &col3)
fmt.Println(col1,"\t",col2,"\t",col3)
}

fmt.Println("---- select(バインド変数なし) ---- ")
rows02 ,err := cnn.Query("select * from tab1 where col1 >= 1 ")
defer rows02.Close()
for rows02.Next(){
rows02.Scan(&col1, &col2, &col3)
fmt.Println(col1,"\t",col2,"\t",col3)
}

fmt.Println("---- insert(バインド変数有) ---- ")
tx03, err := cnn.Begin()
defer tx03.Rollback()
stmt03, err := tx03.Prepare("insert into tab1(col1,col2,col3) values(:col1,:col2,:col3)")
result03, err := stmt03.Exec(100,"X","2021/01/01")
err = tx03.Commit()
ret03,err := result03.RowsAffected()
fmt.Println("ret=",ret03)

fmt.Println("---- insert(バインド変数なし) ---- ")
tx04, err := cnn.Begin()
defer tx04.Rollback()
result04, err := tx04.Exec("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')")
err = tx04.Commit()
ret04,err := result04.RowsAffected()
fmt.Println("ret=",ret04)

}

---

go run a.go

• PostgreSQL

(13)
接続元：CentOS7
接続先：CentOS8

https://qiita.com/koshi_an/items/3185f6d3696f07803fa8
https://astaxie.gitbooks.io/build-web-application-with-golang/content/ja/05.4.html

※接続時、sslmode=disableを指定しないと、"segmentation violation"のエラーが発生する

-- 1. 環境設定
-- 1.1. Goのインストール

yum install epel-release
yum info golang

yum install golang
go version

mkdir -p /usr/local/go
vim ~/.bash_profile

export GOPATH=/usr/local/go
export PATH=$PATH:$GOPATH/bin

. ~/.bash_profile

mkdir -p /usr/local/go/{bin,pkg,src}

-- 1.2. pqの取得

cd $GOPATH/src
go get github.com/lib/pq

-- 2. テストテーブル作成

drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 timestamp);
insert into tab1 values(1,'A',clock_timestamp());
insert into tab1 values(2,'B',clock_timestamp());
select * from tab1;

-- 3. DB接続確認

mkdir $GOPATH/src/a
cd $GOPATH/src/a

vim a.go

---
package main

import (
"database/sql"
"fmt"
_ "github.com/lib/pq"
)

func main() {
var col1 int
var col2 string
var col3 string

cnn, err := sql.Open("postgres", "host=mmm067 port=5432 user=postgres password=postgres dbname=test sslmode=disable")
if err != nil {
fmt.Println(err)
return
}
defer cnn.Close()

fmt.Println("---- select(バインド変数有) ---- ")
stmt11 ,err := cnn.Prepare("select * from tab1 where col1 >= $1")
defer stmt11.Close()
rows11, err := stmt11.Query(1)
for rows11.Next(){
rows11.Scan(&col1, &col2, &col3)
fmt.Println(col1,"\t",col2,"\t",col3)
}
fmt.Println("---- ")
rows12, err := stmt11.Query(2)
for rows12.Next(){
rows12.Scan(&col1, &col2, &col3)
fmt.Println(col1,"\t",col2,"\t",col3)
}

fmt.Println("---- select(バインド変数なし) ---- ")
rows02 ,err := cnn.Query("select * from tab1 where col1 >= 1")
defer rows02.Close()
for rows02.Next(){
rows02.Scan(&col1, &col2, &col3)
fmt.Println(col1,"\t",col2,"\t",col3)
}

fmt.Println("---- insert(バインド変数有) ---- ")
tx03, err := cnn.Begin()
defer tx03.Rollback()
stmt03, err := tx03.Prepare("insert into tab1(col1,col2,col3) values($1,$2,$3)")
result03, err := stmt03.Exec(100,"X","2021/01/01")
err = tx03.Commit()
ret03,err := result03.RowsAffected()
fmt.Println("ret=",ret03)

fmt.Println("---- insert(バインド変数なし) ---- ")
tx04, err := cnn.Begin()
defer tx04.Rollback()
result04, err := tx04.Exec("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')")
err = tx04.Commit()
ret04,err := result04.RowsAffected()
fmt.Println("ret=",ret04)

}

---

go run a.go

• SQL Server

(2019)
接続元：CentOS7
接続先：Windows Server 2016

https://user-first.ikyu.co.jp/entry/2017/12/07/180000

-- 1. 環境設定
-- 1.1. Goのインストール

yum install epel-release
yum info golang

yum install golang
go version

mkdir -p /usr/local/go
vim ~/.bash_profile

export GOPATH=/usr/local/go
export PATH=$PATH:$GOPATH/bin

. ~/.bash_profile

mkdir -p /usr/local/go/{bin,pkg,src}

-- 1.2. go-mssqldbの取得

cd $GOPATH/src
go get github.com/denisenkom/go-mssqldb

-- 2. テストテーブル作成

drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 datetime2);
insert into tab1 values(1,'A',getdate());
insert into tab1 values(2,'B',getdate());
select * from tab1;

-- 3. DB接続確認

mkdir $GOPATH/src/a
cd $GOPATH/src/a

vim a.go

---
package main

import (
"database/sql"
"fmt"
_ "github.com/denisenkom/go-mssqldb"
)

func main() {
var col1 int
var col2 string
var col3 string

cnn, err := sql.Open("sqlserver", "sqlserver://sa:Aaa!1234@mmm064?database=test")
if err != nil {
fmt.Println(err)
return
}
defer cnn.Close()

fmt.Println("---- select(バインド変数有) ---- ")
rows11,err := cnn.Query("select * from tab1 where col1 >= @col1",sql.NamedArg{Name: "col1", Value: 1})
defer rows11.Close()
for rows11.Next(){
rows11.Scan(&col1, &col2, &col3)
fmt.Println(col1,"\t",col2,"\t",col3)
}
fmt.Println("---- ")
rows12,err := cnn.Query("select * from tab1 where col1 >= @col1",sql.NamedArg{Name: "col1", Value: 2})
defer rows12.Close()
for rows12.Next(){
rows12.Scan(&col1, &col2, &col3)
fmt.Println(col1,"\t",col2,"\t",col3)
}

fmt.Println("---- select(バインド変数なし) ---- ")
rows21, err := cnn.Query("select * from tab1 where col1 >= 1")
defer rows21.Close()
for rows21.Next(){
rows21.Scan(&col1, &col2, &col3)
fmt.Println(col1,"\t",col2,"\t",col3)
}

fmt.Println("---- insert(バインド変数有) ---- ")
tx03, err := cnn.Begin()
defer tx03.Rollback()
result03, err := tx03.Exec("insert into tab1(col1,col2,col3) values(@col1,@col2,@col3)",
sql.NamedArg{Name: "col1", Value: 100},
sql.NamedArg{Name: "col2", Value: "X"},
sql.NamedArg{Name: "col3", Value: "2021/01/01"})
err = tx03.Commit()
ret03,err := result03.RowsAffected()
fmt.Println("ret=",ret03)

fmt.Println("---- insert(バインド変数なし) ---- ")
tx04, err := cnn.Begin()
defer tx04.Rollback()
result04, err := tx04.Exec("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')")
err = tx04.Commit()
ret04,err := result04.RowsAffected()
fmt.Println("ret=",ret04)

}

---

go run a.go