Connecting to Oracle DB in Go

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

This blog: https://blogs.oracle.com/developers/developing-microservices-in-java%2c-javascript%2c-python%2c-net%2c-and-go-with-the-oracle-converged-database

and the workshop and repos it points to, specifically https://github.com/oracle/microservices-datadriven/tree/main/grabdish/inventory-go , might be of help.

Here is a snippet from it showing working src and dockerfile...

source...

import (
   "context"
   "database/sql"
   "github.com/godror/godror"
)

user := os.Getenv("DB_USER")
dbpassword := os.Getenv("DB_PASSWORD") 
connectString := os.Getenv("DB_CONNECT_STRING") //for example "examplepdb_tp"
connectionString := user + "/" + dbpassword + "@" + connectString
connection, err := sql.Open("godror", connectionString)

Dockerfile...

FROM alpine:latest
ENV LD_LIBRARY_PATH=/lib
RUN wget https://download.oracle.com/otn_software/linux/instantclient/193000/instantclient-basic-linux.x64-19.3.0.0.0dbru.zip && \
    unzip instantclient-basic-linux.x64-19.3.0.0.0dbru.zip && \
    cp -r instantclient_19_3/* /lib && \
    rm -rf instantclient-basic-linux.x64-19.3.0.0.0dbru.zip && \
    apk add libaio && \
    apk add libaio libnsl libc6-compat
RUN cd /lib
RUN ln -s /lib64/* /lib
RUN ln -s libnsl.so.2 /usr/lib/libnsl.so.1
RUN ln -s /lib/libc.so.6 /usr/lib/libresolv.so.2
COPY /go/bin/inventory-go /usr/lib/inventory-go
ENTRYPOINT ["/usr/lib/inventory-go"]
Dockerfile (including build)...

FROM golang:alpine AS builder
RUN apk update && apk add --no-cache git build-base
WORKDIR /src
COPY . .
RUN go get -d -v
RUN go build -o /go/bin/inventory-go

FROM alpine:latest
ENV LD_LIBRARY_PATH=/lib
RUN wget https://download.oracle.com/otn_software/linux/instantclient/193000/instantclient-basic-linux.x64-19.3.0.0.0dbru.zip && \
    unzip instantclient-basic-linux.x64-19.3.0.0.0dbru.zip && \
    cp -r instantclient_19_3/* /lib && \
    rm -rf instantclient-basic-linux.x64-19.3.0.0.0dbru.zip && \
    apk add libaio && \
    apk add libaio libnsl libc6-compat
RUN cd /lib
RUN ln -s /lib64/* /lib
RUN ln -s libnsl.so.2 /usr/lib/libnsl.so.1
RUN ln -s /lib/libc.so.6 /usr/lib/libresolv.so.2
COPY --from=builder /go/bin/inventory-go /usr/lib/inventory-go
ENTRYPOINT ["/usr/lib/inventory-go"]

Answer 2

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)\n\t%s\n",
            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.\n\t%s\n", query, err.Error())
        return err
    }
    defer stmt.Close()

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

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

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

Answer 3

The error you get says you are trying to access a "method" on an non-initialised variable (nil value). You should check you have no errors before using the values you get back.

package main

import (
    "github.com/odbc"
    "log"
)

func main() {
    conn, err := odbc.Connect("DSN=lnxcepd1db01.XXXXXX.com:51521*CBPDEV;UID=XXXXX;PWD=XXXXX")
    if err != nil {
        log.Println(log)
        return
    }
    defer conn.Close()

    stmt, err := conn.Prepare("select * from XXXXX where XXXX = ?")
    if err != nil {
        log.Println(log)
        return
    }
    defer stmt.Close()

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

}