英文:
Connecting to Oracle DB in Go
问题
我正在尝试从 Windows（64 位 Go，32 位客户端）连接到 Oracle 数据库。
我已经参考了这个问题，但我对 Go 完全不熟悉，所以对他建议的配置一无所知。
我已经安装了多个客户端，例如：
code.google.com\p\odbcgithub.com\mattn\go-oci8
我尝试创建 oci8.pc 文件，但似乎有问题。
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}
当我运行以下代码时，出现错误：
// 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)
}
}
错误信息如下：
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
我是否遗漏了什么？如果有人能指导我正确的方向，我将非常感激。
提前感谢。
编辑：
在浏览更多内容后，我将代码更改如下：
// 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()
}
输出和错误堆栈如下：
[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
如你所见，当我打印可用的驱动程序时，我得到了 odbc，我不确定我遗漏了什么。
我正在使用这个 odbc 驱动程序。
https://code.google.com/p/odbc/wiki/GettingStartedOnWindows
我注意到一件事：
当我使用以下代码时：
db, err := sql.Open("odbc", "DSN=lnxcepd1db01.XXXXXX.com:51521;USERID=XXXX;PASSWORD=XXXX;DATABASE=CBPDEV")
我得到以下错误：
%v SQLDriverConnect: {IM002} [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
当我尝试：
db, err := sql.Open("odbc", "DSN=lnxcepd1db01.XXXXXX.com:51521*CBPDEV;UID=XXXX;PWD=XXXXX")
我得到错误：
SQLDriverConnect: {IM010} [Microsoft][ODBC Driver Manager] Data source name too long
还有这个：
db, err := sql.Open("odbc","DSN=lnxcepd1db01.XXXXX.com:51521*cbpdev;UID=XXX;PWD=XXXX")
错误：
SQLDriverConnect: {IM010} [Microsoft][ODBC Driver Manager] Data source name too long
我完全迷失了，因为我无法将其与驱动程序问题、代码问题或连接字符串中缺少某些参数联系起来。
我有以下问题：
我的连接字符串有什么问题？有人有一个可用的设置或从构建 API 开始的说明吗（很抱歉，但我尝试了一些但没有成功）？哪个 API 更好或更容易使用？
英文:
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 ?
答案1
得分: 2
以下是一个完成连接/准备/查询/获取操作的函数：
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
}
使用Scan是一个棘手的部分，因为它可能不是Oracle程序员所习惯的。这个函数对我尝试过的所有odbc、mgodbc、oci8、adodb和goracle包都有效。
英文:
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.
答案2
得分: 0
你收到的错误提示表示你正在尝试在未初始化的变量（nil值）上访问一个“方法”。
在使用返回的值之前，你应该检查是否有错误。
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)
}
}
英文:
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)
}
}
答案3
得分: 0
这篇博客：https://blogs.oracle.com/developers/developing-microservices-in-java%2c-javascript%2c-python%2c-net%2c-and-go-with-the-oracle-converged-database
以及它指向的研讨会和代码库，特别是https://github.com/oracle/microservices-datadriven/tree/main/grabdish/inventory-go，可能会有所帮助。
以下是其中一部分显示工作源代码和Dockerfile的片段...
源代码...
import (
"context"
"database/sql"
"github.com/godror/godror"
)
user := os.Getenv("DB_USER")
dbpassword := os.Getenv("DB_PASSWORD")
connectString := os.Getenv("DB_CONNECT_STRING") //例如 "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（包括构建）...
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"]
英文:
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"]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论