[Golang] oracle 연동 gopkg.in/rana/ora.v3

http://godoc.org/gopkg.in/rana/ora.v3 (https://github.com/rana/ora)

단점 : 디버깅이 아예 안됨. gcc/gdb 차이때문인가?

0. oracle client 설치 및 tns 설정 하기

1. TDM32 bundle 설치 : http://tdm-gcc.tdragon.net/ 

2. OCI 다운로드 : http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

oci header: instantclient-sdk-nt-12.1.0.2.0.zip

oci.dll : instantclient-basic-nt-12.1.0.2.0.zip

3. LiteIDE 설정

win32-user.env

CGO_ENABLED=1

CGO_CFLAGS=-ID:\EmailReporter\tool\instantclient-sdk-nt-12.1.0.2.0\instantclient_12_1\sdk\include

CGO_LDFLAGS=D:\EmailReporter\tool\instantclient-basic-nt-12.1.0.2.0\instantclient_12_1\oci.dll

PATH=C:\TDM-GCC-32\bin;c:\mingw32\bin;%GOROOT%\bin;%PATH%

4. 테스트

import (

"fmt"

"gopkg.in/rana/ora.v3"

)

func main() {

fmt.Println("BEGIN\n\n")

if true {

env, err := ora.OpenEnv(nil)

defer env.Close()

if err != nil {

fmt.Println("OpenEnv : ", err)

return

}

srvCfg := ora.NewSrvCfg()

srvCfg.Dblink = "CENTOS6_11G_KR"

srv, err := env.OpenSrv(srvCfg)

defer srv.Close()

if err != nil {

fmt.Println("OpenSrv : ", err)

return

}

sesCfg := ora.NewSesCfg()

sesCfg.Username = "CFDBA_KR"

sesCfg.Password = "cf_local_kr"

ses, err := srv.OpenSes(sesCfg)

defer ses.Close()

if err != nil {

fmt.Println("OpenSes : ", err)

return

}

// create table

tableName := "t1"

/*

stmtTbl, err := ses.Prep(fmt.Sprintf("CREATE TABLE %v "+

"(C1 NUMBER(19,0) GENERATED ALWAYS AS IDENTITY "+

"(START WITH 1 INCREMENT BY 1), C2 VARCHAR2(48 CHAR))", tableName))

*/

stmtTbl, err := ses.Prep(fmt.Sprintf("CREATE TABLE %v "+

"(C1 NUMBER(19,0) ,"+

"C2 VARCHAR2(48 CHAR))", tableName))

defer stmtTbl.Close()

if err != nil {

panic(err)

}

rowsAffected, err := stmtTbl.Exe()

if err != nil {

panic(err)

}

fmt.Println(rowsAffected)

// begin first transaction

tx1, err := ses.StartTx()

if err != nil {

panic(err)

}

// insert record

var id uint64

str := "Go is expressive, concise, clean, and efficient."

stmtIns, err := ses.Prep(fmt.Sprintf(

"INSERT INTO %v (C2) VALUES (:C2) RETURNING C1 INTO :C1", tableName))

defer stmtIns.Close()

rowsAffected, err = stmtIns.Exe(str, &id)

if err != nil {

panic(err)

}

fmt.Println(rowsAffected)

// insert nullable String slice

a := make([]ora.String, 4)

a[0] = ora.String{Value: "Its concurrency mechanisms make it easy to"}

a[1] = ora.String{IsNull: true}

a[2] = ora.String{Value: "It's a fast, statically typed, compiled"}

a[3] = ora.String{Value: "One of Go's key design goals is code"}

stmtSliceIns, err := ses.Prep(fmt.Sprintf(

"INSERT INTO %v (C2) VALUES (:C2)", tableName))

defer stmtSliceIns.Close()

if err != nil {

panic(err)

}

rowsAffected, err = stmtSliceIns.Exe(a)

if err != nil {

panic(err)

}

fmt.Println(rowsAffected)

// fetch records

stmtQry, err := ses.Prep(fmt.Sprintf(

"SELECT C1, C2 FROM %v", tableName))

defer stmtQry.Close()

if err != nil {

panic(err)

}

rset, err := stmtQry.Qry()

if err != nil {

panic(err)

}

for rset.Next() {

fmt.Println(rset.Row[0], rset.Row[1])

}

if rset.Err != nil {

panic(rset.Err)

}

// commit first transaction

err = tx1.Commit()

if err != nil {

panic(err)

}

// begin second transaction

tx2, err := ses.StartTx()

if err != nil {

panic(err)

}

// insert null String

nullableStr := ora.String{IsNull: true}

stmtTrans, err := ses.Prep(fmt.Sprintf(

"INSERT INTO %v (C2) VALUES (:C2)", tableName))

defer stmtTrans.Close()

if err != nil {

panic(err)

}

rowsAffected, err = stmtTrans.Exe(nullableStr)

if err != nil {

panic(err)

}

fmt.Println(rowsAffected)

// rollback second transaction

err = tx2.Rollback()

if err != nil {

panic(err)

}

// fetch and specify return type

stmtCount, err := ses.Prep(fmt.Sprintf(

"SELECT COUNT(C1) FROM %v WHERE C2 IS NULL", tableName), ora.U8)

defer stmtCount.Close()

if err != nil {

panic(err)

}

rset, err = stmtCount.Qry()

if err != nil {

panic(err)

}

row := rset.NextRow()

if row != nil {

fmt.Println(row[0])

}

if rset.Err != nil {

panic(rset.Err)

}

// create stored procedure with sys_refcursor

stmtProcCreate, err := ses.Prep(fmt.Sprintf(

"CREATE OR REPLACE PROCEDURE PROC1(P1 OUT SYS_REFCURSOR) AS BEGIN "+

"OPEN P1 FOR SELECT C1, C2 FROM %v WHERE C1 > 2 ORDER BY C1; "+

"END PROC1;",

tableName))

defer stmtProcCreate.Close()

rowsAffected, err = stmtProcCreate.Exe()

if err != nil {

panic(err)

}

// call stored procedure

// pass *Rset to Exe to receive the results of a sys_refcursor

stmtProcCall, err := ses.Prep("CALL PROC1(:1)")

defer stmtProcCall.Close()

if err != nil {

panic(err)

}

procRset := &ora.Rset{}

rowsAffected, err = stmtProcCall.Exe(procRset)

if err != nil {

panic(err)

}

if procRset.IsOpen() {

for procRset.Next() {

fmt.Println(procRset.Row[0], procRset.Row[1])

}

if procRset.Err != nil {

panic(procRset.Err)

}

fmt.Println(procRset.Len())

}

}

fmt.Println("\n\nEND\n\n\n")

}

http://github.com/jzaikovs/ora 수정해서 쓸 계획(캐릭터 셋을 넣어줘야 함.)

oci.go

oci_OCIEnvNlsCreate       = ociLibrary.NewProc("OCIEnvNlsCreate")
conn.go

err := conn.envErr(oci_OCIEnvNlsCreate.Call(

conn.env.ref(),                      //OCIEnv        **envhpp,

OCI_DEFAULT|OCI_OBJECT|OCI_THREADED, //ub4           mode,

0,     //void          *ctxp,

0,     //void          *(*malocfp)

0,     //void          *(*ralocfp)

0,     //void          (*mfreefp)

0,     //size_t        xtramemsz,

0,     //void          **usrmempp

846,   //ub2           charset,

2000)) //ub2           ncharset )