在Go中连接到Oracle DB

Question

I am trying to connect to Oracle DB from Windows (64 bit Go, 32 bit client)

I already reffered this question but I am completely new to Go so I am clueless about the configuration he suggested.

I have installed multiple clients, such as:

1. `code.google.com\p\odbc`
2. `github.com\mattn\go-oci8`

I tried to create the oci8.pc file but it seems it is wrong.

prefix=/devel/target/1.0
exec_prefix=${prefix}
libdir=C:/oracle/instantclient_12_1/sdk/lib/msvc
includedir=C:/oracle/instantclient_12_1/sdk/include
oralib=C:/oracle/instantclient_12_1/sdk/lib/msvc
orainclude=C:/oracle/instantclient_12_1/sdk/include
gcclib=C:/TDM-GCC-64/lib
gccinclude=C:/TDM-GCC-64/include
glib_genmarshal=glib-genmarshal
gobject_query=gobject-query
glib_mkenums=glib-mkenums
Name: oci8
Version: 12.1
Description: oci8 library
Libs: -L${oralib} -L${gcclib} -loci
Libs.private:
Cflags: -I${orainclude} -I${gccinclude}

When I run the following code, I get error:

// TestDB
package main

import (
"github.com/odbc"
)

func main() {
conn, _ := odbc.Connect("DSN=lnxcepd1db01.XXXXXX.com:51521*CBPDEV;UID=XXXXX;PWD=XXXXX")
defer conn.Close()

stmt, _ := conn.Prepare("select * from XXXXX where XXXX = ?")
defer stmt.Close()

stmt.Execute("100044")
rows, _ := stmt.FetchAll()
for i, row := range rows {
println(i, row)
}

}

Error:

panic: runtime error: invalid memory address or nil pointer dereference
    panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xc0000005 code=0x0 addr=0x8 pc=0x43c3a6]

goroutine 1 [running]:
github.com/odbc.(*Connection).Close(0x0, 0x45)
    C:/Go/UDL/src/github.com/odbc/odbc.go:263 +0x26
github.com/odbc.(*Connection).newStmt(0x0, 0x36, 0xc0820120e0)
    C:/Go/UDL/src/github.com/odbc/odbc.go:152 +0x50
github.com/odbc.(*Connection).Prepare(0x0, 0x51cf70, 0x36, 0x0, 0x0, 0x0, 0xc082005e90, 0x9d6eb0)
    C:/Go/UDL/src/github.com/odbc/odbc.go:162 +0x51
main.main()
    C:/Go/My Codes/new/TestDB.go:12 +0xc4

goroutine 17 [syscall, locked to thread]:
runtime.goexit()
    c:/go/src/runtime/asm_amd64.s:2232 +0x1

Am I missing anything? If anybody can point me into right direction, I will be really greatful.

Thanks in advance.

EDIT:

After browsing more I changed my code as follows:

// TestDB
package main

import (
_ "code.google.com/p/odbc"
    "database/sql"
    "fmt"
)

func main() {
fmt.Println(sql.Drivers())

db, err := sql.Open("odbc","DSN=lnxcepd1db01.xxxx.com:51521*CBPDEV;UID=XXXX;PWD=XXXX")
if err != nil {
        panic(err)
}

rows, err := db.Query("select TABLE_NAME from tabs")

var TABLE_NAME string 

defer rows.Close()
    for rows.Next() {
        fmt.Println(rows.Columns())
         rows.Scan(&TABLE_NAME)
     fmt.Println(TABLE_NAME)
    }
    err = rows.Err()

    if err != nil {
        panic(err)
    }

    defer db.Close()

}

The output + error stack:

[odbc]
panic: runtime error: invalid memory address or nil pointer dereference
    panic: runtime error: invalid memory address or nil pointer dereference
[signal 0xc0000005 code=0x0 addr=0x20 pc=0x453408]

goroutine 1 [running]:
database/sql.(*Rows).Close(0x0, 0x0, 0x0)
    c:/go/src/database/sql/sql.go:1659 +0x38
database/sql.(*Rows).Next(0x0, 0xc082002400)
    c:/go/src/database/sql/sql.go:1586 +0x2c
main.main()
    C:/Go/My Codes/new/TestDB.go:23 +0x263

goroutine 2 [runnable]:
runtime.forcegchelper()
    c:/go/src/runtime/proc.go:90
runtime.goexit()
    c:/go/src/runtime/asm_amd64.s:2232 +0x1

goroutine 3 [runnable]:
runtime.bgsweep()
    c:/go/src/runtime/mgc0.go:82
runtime.goexit()
    c:/go/src/runtime/asm_amd64.s:2232 +0x1

goroutine 4 [runnable]:
runtime.runfinq()
    c:/go/src/runtime/malloc.go:712
runtime.goexit()
    c:/go/src/runtime/asm_amd64.s:2232 +0x1

goroutine 5 [runnable]:
database/sql.(*DB).connectionOpener(0xc08205a000)
    c:/go/src/database/sql/sql.go:588
created by database/sql.Open
    c:/go/src/database/sql/sql.go:452 +0x323

As you can see, when I am printing the available Drivers, I am getting odbc, I am not sure what I am missing.

I am using this odbc driver.

https://code.google.com/p/odbc/wiki/GettingStartedOnWindows

I noticed 1 thing:

When I use

db, err := sql.Open("odbc", "DSN=lnxcepd1db01.XXXXX.com:51521;USERID=XXXX;PASSWORD=XXXX;DATABASE=CBPDEV")

I get error as,

%v SQLDriverConnect: {IM002} [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

When I try,

db, err := sql.Open("odbc", "DSN=lnxcepd1db01.XXXXXX.com:51521*CBPDEV;UID=XXXX;PWD=XXXXX")

I get error:

SQLDriverConnect: {IM010} [Microsoft][ODBC Driver Manager] Data source name too long

And this one as well:

db, err := sql.Open("odbc","DSN=lnxcepd1db01.XXXXX.com:51521*cbpdev;UID=XXX;PWD=XXXX")

Error:

SQLDriverConnect: {IM010} [Microsoft][ODBC Driver Manager] Data source name too long

I am completely lost as I cannot relate this to a driver issue, my code issue or I am missing some parameters in connection string.

I have the following questions :

Whats is wrong with my Connection String? Does anyone has a working setup or instructions to be carried out starting from API building (I am sorry but I tried a few but no luck)?? Which API is better or easier to use ?

Answer 1

here is a function to do a complete conn/prep/query/fetch operation:

func getDatePrepQuery(driver string, connString string) error {
    db, err := sql.Open(driver, connString)
    if err != nil {
        log.Printf("sql.Open(%s, %s)
\t%s
",
            driver, connString, err.Error())
        return err
    }
    defer db.Close()

    query := "select SYSDATE from dual"
    stmt, err := db.Prepare(query)
    if err != nil {
        log.Printf("db.Prepare(%s) failed.
\t%s
", query, err.Error())
        return err
    }
    defer stmt.Close()

    rows, err := stmt.Query()
    if err != nil {
        log.Printf("stmt.Query() failed.
\t%s
", err.Error())
        return err
    }
    defer rows.Close()

    var columns []string
    columns, err = rows.Columns()
    if err != nil {
        log.Printf("rows.Columns() failed.
\t%s
", err.Error())
    }
    for i, c := range columns {
        fmt.Printf("%3d %s
", i, c)
    }

    for rows.Next() {
        var sysdate time.Time
        err = rows.Scan(&sysdate)
        if err != nil {
            log.Printf("rows.Scan(...) failed.
\t%s
", err.Error())
            return err
        }
        fmt.Println("SYSDATE:", sysdate)
    }
    err = rows.Err()
    if err != nil {
        log.Printf("rows iteration failed.
\t%s
", err.Error())
        return err
    }
    return nil
}

Using Scan is the tricky part as it may not be something Oracle programmers are used to. This function worked for all of the odbc, mgodbc, oci8, adodb and goracle packages that I tried.