GO连接DM数据库（含复杂密码处理）

whx@Flora

已于 2023-04-20 13:52:17 修改

阅读量1k

点赞数

文章标签： golang 数据库开发语言

于 2023-04-20 13:50:26 首次发布

本文链接：https://blog.csdn.net/canyueduyi/article/details/130264433

版权

注意：
DM目前不支持Mac版本的go驱动，在Mac的goland编译运行会报错

一、部署GO环境

1. 安装GOLANG语言包

官方下载地址
安装：下一步–>下一步
验证

# 查看go版本
go version

# 查看环境变量
go env

2. 安装goland编辑器

安装：注意这里，需要选择自动添加环境变量

3. 修改go的环境变量

# 查看当前配置
go env

# 修改配置，必须修改以下两项
go env -w GO111MODULE=auto
env -w GOPROXY=https://goproxy.cn,https://goproxy.io,direct

默认如下：

PS C:\Users\wendy\GolandProjects\awesomeProject> go env
set GO111MODULE=on
set GOARCH=amd64
set GOBIN=
set GOCACHE=C:\Users\wendy\AppData\Local\go-build
set GOENV=C:\Users\wendy\AppData\Roaming\go\env
set GOEXE=.exe
set GOEXPERIMENT=
set GOFLAGS=
set GOHOSTARCH=amd64
set GOHOSTOS=windows
set GOINSECURE=
set GOMODCACHE=C:\Users\wendy\go\pkg\mod
set GONOPROXY=
set GONOSUMDB=
set GOOS=windows
set GOPATH=C:\Users\wendy\go    --如果没有这个目录，需要自己创建，目录下创建3个文件夹：pkg、src、bin,如下图
set GOPRIVATE=
set GOPROXY=https://goproxy.cn     --go get拉取项目的地址
set GOROOT=C:\Go                   --golang安装目录
set GOSUMDB=sum.golang.org
set GOTMPDIR=
set GOTOOLDIR=C:\Go\pkg\tool\windows_amd64
set GOVCS=
set GOVERSION=go1.19.3
set GCCGO=gccgo
set GOAMD64=v1
set AR=ar
set CC=gcc
set CXX=g++
set CGO_ENABLED=1
set GOMOD=C:\Users\wendy\GolandProjects\awesomeProject\go.mod     --go 代码文件的路径
set GOWORK=
set CGO_CFLAGS=-g -O2
set CGO_CPPFLAGS=
set CGO_CXXFLAGS=-g -O2
set CGO_FFLAGS=-g -O2
set CGO_LDFLAGS=-g -O2
set PKG_CONFIG=pkg-config
set GOGCCFLAGS=-m64 -mthreads -fno-caret-diagnostics -Qunused-arguments -Wl,--no-gc-sections -fmessage-length=0 -fdebug-prefix-map=C:\Users\wendy\AppData\Local\Temp\go-build1224507220=/tmp/go-build -gno-record-gcc-swi
tches

4. 添加DM驱动

DM驱动必须添加到GOROOT的src下边，不然goland获取不到
在这里插入图片描述

5. 创建demo程序

1）创建go文件，命名为main.go

2) 将一下内容粘贴到文件中

/*该例程实现插入数据，修改数据，删除数据，数据查询等基本操作。*/
package main

// 引入相关包
import (
    "database/sql"
    "dm"
    "fmt"
    "io/ioutil"
    "time"
)

var db *sql.DB
var err error

func main() {
    driverName := "dm"
    user := "CS"
    passwd := "123@1"
    port := "5000"
    dbname := "CS"
    host := "192.168.1.14"

    connString := user + ":" + passwd + "@" + host + ":" + port + "/" + dbname

    dataSourceName := "dm://" + connString

    if db, err = connect(driverName, dataSourceName); err != nil {
        fmt.Println(err)
        return
    }

    if err = disconnect(); err != nil {
        fmt.Println(err)
        return
    }
}

/* 创建数据库连接 */
func connect(driverName string, dataSourceName string) (*sql.DB, error) {
    var db *sql.DB
    var err error
    if db, err = sql.Open(driverName, dataSourceName); err != nil {
        return nil, err
    }
    if err = db.Ping(); err != nil {
        return nil, err
    }
    fmt.Printf("connect to \"%s\" succeed.\n", dataSourceName)
    return db, nil
}

/* 往产品信息表插入数据 */
func insertTable() error {
    var inFileName = "D:\\三国演义.jpg"
    var sql = `INSERT INTO production.product(name,author,publisher,publishtime,
product_subcategoryid,productno,satetystocklevel,originalprice,nowprice,discount,
description,photo,type,papertotal,wordtotal,sellstarttime,sellendtime)
VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17);`
    data, err := ioutil.ReadFile(inFileName)
    if err != nil {
        return err
    }
    t1, _ := time.Parse("2006-Jan-02", "2005-Apr-01")
    t2, _ := time.Parse("2006-Jan-02", "2006-Mar-20")
    t3, _ := time.Parse("2006-Jan-02", "1900-Jan-01")
    _, err = db.Exec(sql, "三国演义", "罗贯中", "中华书局", t1, 4, "9787101046126", 10, 19.0000, 15.2000,
                     8.0,
                     "《三国演义》是中国第一部长篇章回体小说，中国小说由短篇发展至长篇的原因与说书有关。",
                     data, "25", 943, 93000, t2, t3)
    if err != nil {
        return err
    }
    fmt.Println("insertTable succeed")
    return nil
}

/* 修改产品信息表数据 */
func updateTable() error {
    var sql = "UPDATE production.product SET name = :name WHERE productid = 11;"
    if _, err := db.Exec(sql, "三国演义（上）"); err != nil {
        return err
    }
    fmt.Println("updateTable succeed")
    return nil
}

/* 查询产品信息表 */
func queryTable() error {
    var productid int
    var name string
    var author string
    var description dm.DmClob
    var photo dm.DmBlob
    var sql = "SELECT productid,name,author,description,photo FROM production.product WHERE productid=11"
    rows, err := db.Query(sql)
    if err != nil {
        return err
    }
    defer rows.Close()
    fmt.Println("queryTable results:")
    for rows.Next() {
        if err = rows.Scan(&productid, &name, &author, &description, &photo); err != nil {
            return err
        }
        blobLen, _ := photo.GetLength()
        fmt.Printf("%v %v %v %v %v\n", productid, name, author, description, blobLen)
    }
    return nil
}

  /* 删除产品信息表数据 */
  func deleteTable() error {
    var sql = "DELETE FROM production.product WHERE productid = 12;"
    if _, err := db.Exec(sql); err != nil {
        return err
    }
    fmt.Println("deleteTable succeed")
    return nil
}

  /* 关闭数据库连接 */
  func disconnect() error {
    if err := db.Close(); err != nil {
        fmt.Printf("db close failed: %s.\n", err)
        return err
    }
    fmt.Println("disconnect succeed")
    return nil
}

如果需要测试insert、update、delete方法，需要创建表

create schema production authorization sysdba;
create table production.product(
  productid int IDENTITY(1, 1) primary key,
  name varchar(50),
  author varchar(50),
  publisher varchar(50),
  publishtime date,
  product_subcategoryid int,
  productno varchar(20),
  satetystocklevel int,
  originalprice dec(6,4),
  nowprice dec(6,4),
  discount dec(6,1),
  description varchar(200),
  photo image,
  type varchar(10),
  papertotal int,
  wordtotal int,
  sellstarttime date,
  sellendtime date
);

运行测试

第一次运行会有一些依赖的报错，按提示执行

PS C:\Users\wendy\GolandProjects\awesomeProject> go get -u golang.org/x/text
go: downloading golang.org/x/text v0.4.0
go: added golang.org/x/text v0.4.0
PS C:\Users\wendy\GolandProjects\awesomeProject> go get github.com/golang/snappy
go: downloading github.com/golang/snappy v0.0.4
PS C:\Users\wendy\GolandProjects\awesomeProject> go mod tidy
PS C:\Users\wendy\GolandProjects\awesomeProject> go get github.com/golang/snappy
go: added github.com/golang/snappy v0.0.4
PS C:\Users\wendy\GolandProjects\awesomeProject> go get golang.org/x/text/encoding
go: added golang.org/x/text v0.4.0
PS C:\Users\wendy\GolandProjects\awesomeProject> go get golang.org/x/text/encoding/ianaindex
PS C:\Users\wendy\GolandProjects\awesomeProject> go get golang.org/x/text/language
PS C:\Users\wendy\GolandProjects\awesomeProject> go get golang.org/x/text/message

6. 使用ODBC连接DM(推荐)

1. 配置dm_svc.conf

2. 配置ODBC

注：如果使用的是二进制的压缩包的管理工具，是需要使用安装包单独安装下驱动的

开Windows的控制面板，搜索ODBC，在【用户DSN】-- 选择DM --【添加】
企业微信截图_093527d3-f457-4371-952c-064953a993da.png

package main

import (
    "fmt"
    "github.com/alexbrainman/odbc" // google's odbc driver
    "github.com/axgle/mahonia"
    "github.com/go-xorm/xorm"
    "xorm.io/core"
)

type Address struct {
    Addressid  int64  `xorm:"addressid"`
    Address1   string `xorm:"address1"`
    Address2   string `xorm:"address2"`
    City       string `xorm:"city"`
    Postalcode string `xorm:"postalcode"`
}

// 字符串解码函数，处理中文乱码
func ConvertToString(src string, srcCode string, tagCode string) string {
    srcCoder := mahonia.NewDecoder(srcCode)
    srcResult := srcCoder.ConvertString(src)
    tagCoder := mahonia.NewDecoder(tagCode)
    _, cdata, _ := tagCoder.Translate([]byte(srcResult), true)
    result := string(cdata)
    return result
}

func main() {
    //连接集群
    dataSourceName := "driver={DM8 ODBC DRIVER};server=DW;database=DM;uid=SYSDBA;pwd=SYSDBA"
    //连接单实例
    //dataSourceName := "driver={DM8 ODBC DRIVER};server=192.168.1.18:5238;database=DM;uid=SYSDBA;pwd=SYSDBA"

    engine, err := xorm.NewEngine("odbc", dataSourceName)
    if err != nil {
        fmt.Println("new engine got error:", err)
        return
    }
    engine.ShowSQL(true) //控制台打印出生成的SQL语句；
    engine.Logger().SetLevel(core.LOG_DEBUG)
    if err := engine.Ping(); err != nil {
        fmt.Println("ping got error:", err)
        return
    }
    fmt.Printf("connect to \"%s\" succeed.\n", dataSourceName)
    // 1) sql查询
    results, err := engine.Query("select 1")
    if err != nil {
        fmt.Println("查询出错:", err)
        return
    }
    fmt.Println(results)

}

二、数据库复杂密码|(密码中包含特殊字符)

问题

在传入密码之前通过url.PathEscape进行Escape处理，并在URL中加入&escapeProcess=true
类似案例

dataSourceName := “dm://SYSDBA:abc!@#$%^@192.168.1.10:5236?compatibleMode=oracle&stmtPoolSize=300”
if db, err = connect(driverName, dataSourceName); err != nil {
    fmt.Println(err)
    return
}

密码是：abc!@#$%^
无论是将这密码用双引号或单引号包含都包错误：net/url: invalid userinfo

尝试过写法：

dataSourceName := “dm://SYSDBA:‘abc!@#$%^’@192.168.1.10:5236”
dataSourceName := “dm://SYSDBA:\“abc!@#$%^\”@192.168.1.10:5236”
dataSourceName := “dm://SYSDBA:\“abc!@#$%^\”@192.168.1.10:5236”

问题还是一样，无法连接，该怎么处理？

用法说明

package main

import (
	"fmt"
	"net/url"
)

func main() {
	path := url.PathEscape("my/cool+blog&about,stuff")
	fmt.Println(path)

}
输出：

my%2Fcool+blog&about%2Cstuff

用到连接上试试看

    driverName := "dm"
    user := "CS"
    passwd := url.PathEscape("123@1"）
    port := "5000"
    dbname := "CS"
    host := "192.168.1.14"

    connString := user + ":" + passwd + "@" + host + ":" + port + "?schema=" + dbname

    dataSourceName := "dm://" + connString + "&escapeProcess=true"