注册
【与达梦同行】达梦驱动图谱
技术分享/ 文章详情 /

【与达梦同行】达梦驱动图谱

夏克 2022/12/06 2260 7 2

达梦驱动图谱

摘要

达梦提供了大部分主流开发语言的驱动接口,在我用使用过的国产数据库中对客户端驱动的支持应该算是非常不错的。本文主要介绍达梦的驱动开发,通过实际操作,从环境搭建到实践验证,介绍了达梦各种语言驱动的详细使用过程,由于篇幅原因,将演示代码提交到gitee仓库上供大家参考。

图谱

以下分别对C++、Java、Python、Golang、JavaScript五种开发语言对应的7种驱动进行演示。

图片.png

C++接口

unixODBC

环境搭建

tar -zxvf unixODBC-2.3.9.tar.gz cd unixODBC-2.3.9 ./configure make -j4 sudo make install
  • 验证 unixODBC 安装
odbcinst -j unixODBC 2.3.9 DRIVERS............: /usr/local/etc/odbcinst.ini SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources USER DATA SOURCES..: /home/frank/.odbc.ini SQLULEN Size.......: 8 SQLLEN Size........: 8 SQLSETPOSIROW Size.: 8
  • 配置odbcinst.ini
[DM8] Description = dm odbc Driver = /home/frank/dmdbms/bin/libdodbc.so
  • 配置odbc.ini
[dm] Description = gch for DM8 Driver = DM8 Trace = yes TraceFile = sql.log SERVER= localhost UID= SYSDBA PWD = SYSDBA TCP_PORT= 5236
  • 设置魂晶变量
export LD_LIBRARY_PATH=/dm8/dmdbms/bin:$LD_LIBRARY_PATH source ~/.bash_profile

验证

isql -v dm +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select id_code; +----------------------------------------------------------------------------+ | ID_CODE | +----------------------------------------------------------------------------+ | 1-1-126-20.09.04-126608-ENT | +----------------------------------------------------------------------------+ SQLRowCount returns 1 1 rows fetched

C++代码

为了不占用更多篇幅,这部分这部分代码我提交到gitee上,供大家参考:

图片.png

OCI/DCI

环境搭建

${DM_HOME}/drivers/oci/ 达梦提供了OCI相关的库与头文件。

  • 环境变量配置:export LD_LIBRARY_PATH=${DM_HOME}/drivers/oci/:$LD_LIBRARY_PATH

C++代码

代码部分参考

cmake_minimum_required (VERSION 3.11) project (otloci ) set(CMAKE_CXX_FLAGS "-Wall") # set(CMAKE_CXX_FLAGS "-Wall -DOTL_ODBC_UNIX") set(CMAKE_CXX_FLAGS_DEBUG "-g3") set(CMAKE_CXX_FLAGS_RELEASE "-O2") set(CMAKE_BUILD_TYPE Debug) include_directories(./) include_directories(/usr/local/include) include_directories(/home/frank/dmdbms/drivers/oci/include) add_executable(otloci otloci.cpp) link_directories("/home/frank/dmdbms/drivers/oci/") target_link_libraries(otloci dmoci)

Python接口

dmPython

dmPython是DM提供的依据Python DB API version 2.0中API使用规定而开发的数据库访问接口。dmPython实现这些API,使Python应用程序能够对DM数据库进行访问

环境搭建

dmPython要求DM Server版本大于7.0.0.9;Python版本大于2.6

  • 设置胡静变量: DM_HOME=C:\dmdbmsPATH=C:\dmdbms\drivers\dpi
  • 编译源码:进入${DM_HOME}/drivers/python/dmPython目录,执行python setup.py install
python setup.py install running install running bdist_egg running egg_info writing dmPython.egg-info\PKG-INFO writing dependency_links to dmPython.egg-info\dependency_links.txt writing top-level names to dmPython.egg-info\top_level.txt reading manifest file 'dmPython.egg-info\SOURCES.txt' writing manifest file 'dmPython.egg-info\SOURCES.txt' installing library code to build\bdist.win-amd64\egg running install_lib running build_ext creating build\bdist.win-amd64\egg copying build\lib.win-amd64-3.9\dmPython.cp39-win_amd64.pyd -> build\bdist.win-amd64\egg creating stub loader for dmPython.cp39-win_amd64.pyd byte-compiling build\bdist.win-amd64\egg\dmPython.py to dmPython.cpython-39.pyc creating build\bdist.win-amd64\egg\EGG-INFO copying dmPython.egg-info\PKG-INFO -> build\bdist.win-amd64\egg\EGG-INFO copying dmPython.egg-info\SOURCES.txt -> build\bdist.win-amd64\egg\EGG-INFO copying dmPython.egg-info\dependency_links.txt -> build\bdist.win-amd64\egg\EGG-INFO copying dmPython.egg-info\top_level.txt -> build\bdist.win-amd64\egg\EGG-INFO writing build\bdist.win-amd64\egg\EGG-INFO\native_libs.txt zip_safe flag not set; analyzing archive contents... __pycache__.dmPython.cpython-39: module references __file__ creating 'dist\dmPython-2.3-py3.9-win-amd64.egg' and adding 'build\bdist.win-amd64\egg' to it removing 'build\bdist.win-amd64\egg' (and everything under it) Processing dmPython-2.3-py3.9-win-amd64.egg removing 'c:\programdata\miniconda3\lib\site-packages\dmPython-2.3-py3.9-win-amd64.egg' (and everything under it) creating c:\programdata\miniconda3\lib\site-packages\dmPython-2.3-py3.9-win-amd64.egg Extracting dmPython-2.3-py3.9-win-amd64.egg to c:\programdata\miniconda3\lib\site-packages dmPython 2.3 is already the active version in easy-install.pth Installed c:\programdata\miniconda3\lib\site-packages\dmpython-2.3-py3.9-win-amd64.egg Processing dependencies for dmPython==2.3 Finished processing dependencies for dmPython==2.3

验证

(base) C:\dmdbms\drivers\python\dmPython>python Python 3.9.5 (default, May 18 2021, 14:42:02) [MSC v.1916 64 bit (AMD64)] :: Anaconda, Inc. on win32 Type "help", "copyright", "credits" or "license" for more information. >>> import dmPython >>> conn = dmPython.connect(user='SYSDBA', password='SYSDBA', server='localhost', port=5236, autoCommit=True) >>> cursor = conn.cursor() >>> cursor.execute("select * from person.person") <builtins.DmdbCursor on <dmPython.Connection to SYSDBA@localhost:5236>> >>> cursor.description [('PERSONID', <class 'dmPython.NUMBER'>, 11, 10, 10, 0, 0), ('SEX', <class 'dmPython.FIXED_STRING'>, 1, 1, 1, 0, 0), ('NAME', <class 'dmPython.STRING'>, 50, 50, 50, 0, 0), ('EMAIL', <class 'dmPython.STRING'>, 50, 50, 50, 0, 1), ('PHONE', <class 'dmPython.STRING'>, 25, 25, 25, 0, 1)] >>>

Q&A

Q1:编译时目录没有权限(windows下)

A1:可以使用管理员方式打开或更换Python安装目录

Q2:import dmPython时找不到DLL

A2:可以通过上文设置环境变量或者将dpi目录下的*.dll放到运行目录

JayDeBeApi

JayDeBeApi是一个python模块,可以使用JayDeBeApi通过java JDBC驱动链接数据库。

环境搭建

  • pip install JayDeBeApi
Cloning into 'jaydebeapi'... remote: Enumerating objects: 1710, done. remote: Counting objects: 100% (123/123), done. remote: Compressing objects: 100% (70/70), done. remote: Total 1710 (delta 48), reused 90 (delta 27), pack-reused 1587 Receiving objects: 100% (1710/1710), 427.56 KiB | 452.00 KiB/s, done. Resolving deltas: 100% (919/919), done. [root@localhost jaydebeapi]# python3 setup.py install running install running bdist_egg running egg_info creating JayDeBeApi.egg-info writing JayDeBeApi.egg-info/PKG-INFO ......

验证

root@LAPTOP-4OF1323N:~# python3 Python 3.10.6 (main, Nov 2 2022, 18:53:38) [GCC 11.3.0] on linux Type "help", "copyright", "credits" or "license" for more information. >>> import jaydebeapi >>> url='jdbc:dm://localhost:5236' >>> user='SYSDBA' >>> password='SYSDBA' >>> driver='dm.jdbc.driver.DmDriver' >>> jarfile='/home/frank/test/DmJdbcDriver18.jar' >>> sqlstr='select id_code' >>> conn=jaydebeapi.connect(driver,url,[user,password],jarfile) >>> curs = conn.cursor() >>> curs.execute(sqlstr) >>> result = curs.fetchall()

Golang接口

Go 语言标准库 database/sql提供了一系列数据库操作的标准接口,DM 数据库基于 GO1.13 版本通过实现 database/sql 包的接口,向开发人员提供 DM 数据库操作的 Go 语言接口。

环境搭建

达梦安装后在安装目录的drivers下有go目录,该目录下面的dm-go-driver.zip。

  • 新建go工程
[root@vmpc ~]# mkdir go_dm8 [root@vmpc ~]# cd go_dm8/ [root@vmpc go_dm8]# mkdir src [root@vmpc go_dm8]# cd src/ [root@vmpc src]# mkdir app [root@vmpc src]# cd app [root@vmpc dm8]# unzip dm-go-driver.zip [root@vmpc dm8]# mv dm ../go_dm8/src/
  • 设置环境变量
[root@vmpc go_dm8]# export GOPATH=/root/go_dm8 [root@vmpc go_dm8]# export GO111MODULE="off"
  • 测试代码
/*该例程实现插入数据,修改数据,删除数据,数据查询等基本操作。*/ package main // 引入相关包 import ( "database/sql" "dm" "fmt" "io/ioutil" "time" ) var db *sql.DB var err error func main() { driverName := "dm" dataSourceName := "dm://SYSDBA:SYSDBA@localhost:5236" if db, err = connect(driverName, dataSourceName); err != nil { fmt.Println(err) return } if err = insertTable(); err != nil { fmt.Println(err) return } if err = updateTable(); err != nil { fmt.Println(err) return } if err = queryTable(); err != nil { fmt.Println(err) return } if err = deleteTable(); 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 = "sanguo.txt" 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, "9787101046121", 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 = 11;" 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 }

编译项目

  • 编译
[root@vmpc x]# cd $GOPATH [root@vmpc go_dm8]# go get app [root@vmpc go_dm8]# go install app
  • 目录结构
[root@vmpc go_dm8]# tree -L 2 . ├── bin │ ├── app │ └── sanguo.txt └── src ├── app ├── dm ├── github.com └── golang.org 6 directories, 2 files

验证

工程源码在gitee上,供参考:

图片.png

JDBC接口

环境搭建

  • 方法一:

在drivers/jdbc目录找到对应的驱动jar。

  1. DmJdbcDriver16 对应 Jdk1.6 及以上环境
  2. DmJdbcDriver17 对应 Jdk1.7 及以上环境
  3. DmJdbcDriver18 对应 Jdk1.8 及以上环境
  • 方法二:

maven仓库下载

&lt;dependency> &lt;groupId>com.dameng&lt;/groupId> &lt;artifactId>DmJdbcDriver18&lt;/artifactId> &lt;version>8.1.1.193&lt;/version> &lt;/dependency>

关键代码

// 定义 DM JDBC 驱动串 String jdbcString = "dm.jdbc.driver.DmDriver"; // 定义 DM URL 连接串 String urlString = "jdbc:dm://localhost:5236"; // 定义连接用户名 String userName = "SYSDBA"; // 定义连接用户口令 String password = "SYSDBA"; // 加载 JDBC 驱动程序 Class.forName(jdbcString); // 连接 DM 数据库 conn = DriverManager.getConnection(urlString, userName, password);
  • pom.xml
&lt;?xml version="1.0" encoding="UTF-8"?> &lt;modelVersion>4.0.0&lt;/modelVersion> <groupId>org.example</groupId> <artifactId>dm_jdbc</artifactId> <version>1.0-SNAPSHOT</version> <properties> <maven.compiler.source>8</maven.compiler.source> <maven.compiler.target>8</maven.compiler.target> </properties> <dependencies> <dependency> <groupId>com.dameng</groupId> <artifactId>DmJdbcDriver18</artifactId> <version>8.1.1.193</version> </dependency> </dependencies> &lt;/project>

验证

demo在gitee上

图片.png

Node.js

环境搭建

  • 安装npm
sudo apt install npm
  • 安装dmdb包
npm install dmdb

关键代码

var db = require('dmdb'); return db.createPool({ connectString: "dm://SYSDBA:SYSDBA@localhost:5236?autoCommit=false", poolMax: 10, poolMin: 1 }); try { var sql = "SELECT productid,name,author,publisher,photo FROM production.product" var result = await conn.execute(sql); var lob = result.rows[result.rows.length - 1][4]; var buffer = await readLob(lob); // Lob 对象使用完需关闭 await lob.close(); console.log(buffer); return result; } catch (err) { throw new Error("queryTable error: " + err.message); }
  • package.json
{ "dependencies": { "dmdb": "^1.0.14280" } }

验证

demo在gitee上

图片.png

总结

除了上面介绍的驱动达梦还挺了php、doNet、dpi等驱动接口,还有logmnr、fldr驱动。其中dpi类似ODBC,php在企业应用里面使用日趋减少,而doNet主要用于桌面应用上(另外一个原因就是我没用过),所以这3种没有介绍。logmnr、fldr用法比较特殊,后续单独对这两个驱动进行介绍。

评论
后发表回复

作者

文章

阅读量

获赞

扫一扫
联系客服