如何使用 OpenLink ODBC 和 Virtuoso ODBC 驱动程序建立 Node.js ODBC 连接

温成济
2023-12-01

什么

你是一名数据科学家,主要使用 Node.js 2,需要独立于特定数据库管理系统 (DBMS) 的数据访问。

为什么

您不想为每个目标 DBMS 重复工作(例如,重写代码)。

如何

本文档详细介绍了如何从Node.js建立 ODBC 连接 2使用 OpenLink ODBC 和 Virtuoso ODBC 驱动程序到 ODBC 兼容的数据库。这可以使用 Node.js 节点 odbc来完成 28模块,它是Node.js的异步 ODBC 数据库接口 2 .

安装最新版本的Node.js 2(版本 14+)可从 Node.js下载服务器获得。注意 非虚拟机 1(Node Version Manager) 可用于手动更新Node.js的版本 2.

安装 unixODBC

安装unixODBC 7和 unixODBC-devel:在您的系统上编译node-odbc需要这些包提供正确的标头。

  • Ubuntu / Debiansudo apt-get install unixodbc unixodbc-dev
  • 红帽/ CentOSsudo yum install unixODBC unixODBC-devel
  • macOS

安装节点odbc 28使用 npm 命令的模块:

npm install odbc

并且可以使用以下命令获取已安装模块的列表:

npm list

安装必要的 ODBC 驱动程序,配置 DSN 以连接到目标数据库,并将ODBCINI环境变量设置为指向正在使用的文件的位置,以便unixODBCodbc.ini可以找到它 7.

对于 OpenLink ODBC 2艺术大师 1ODBC 驱动程序WideAsUTF16 = Y(这是Use UTF-16 for ODBC unicode calls设置对话框中的选项)驱动程序连接属性必须设置为与unixODBC一起使用 7.

一旦到位以下示例节点-odbc 28程序可用于从Node.js建立 ODBC 连接 2到具有适当配置的目标数据库DSN

const odbc = require('odbc');
 
async function queryData() {
const connection = await odbc.connect('DSN={DSN-name};UID={user-name};PWD={password}');
const data = await connection.query('SELECT * FROM table');
console.log(data);
}
 
queryData();

并且可以使用以下命令执行:

node file-name.js

macOS 上 OpenLink ODBC 和 Virtuoso ODBC 连接的示例输出

安装和设置

De-iMac:node hwilliams$ open ~/Downloads node-v14.15.4.pkg
De-iMac:~ hwilliams$ mkdir nodejs
De-iMac:~ hwilliams$ cd nodejs
De-iMac:nodejs hwilliams$ npm install odbc

> odbc@2.3.5 install /Users/hwilliams/node_modules/odbc
> node-pre-gyp install --fallback-to-build

node-pre-gyp WARN Using needle for node-pre-gyp https download 
node-pre-gyp WARN Tried to download(404): https://github.com/markdirish/node-odbc/releases/download/v2.3.5/odbc-v2.3.5-darwin-x64-napi-v3.tar.gz 
node-pre-gyp WARN Pre-built binaries not found for odbc@2.3.5 and node@14.15.4 (node-v83 ABI, unknown) (falling back to source compile with node-gyp) 
  CXX(target) Release/obj.target/odbc/src/odbc.o
  CXX(target) Release/obj.target/odbc/src/odbc_connection.o
  CXX(target) Release/obj.target/odbc/src/odbc_statement.o
../src/odbc_statement.cpp:83:20: warning: private field 'odbcStatement' is not
      used [-Wunused-private-field]
    ODBCStatement *odbcStatement;
                   ^
../src/odbc_statement.cpp:84:21: warning: private field 'odbcConnection' is not
      used [-Wunused-private-field]
    ODBCConnection *odbcConnection;
                    ^
../src/odbc_statement.cpp:221:20: warning: private field 'odbcStatement' is not
      used [-Wunused-private-field]
    ODBCStatement *odbcStatement;
                   ^
../src/odbc_statement.cpp:222:21: warning: private field 'odbcConnection' is not
      used [-Wunused-private-field]
    ODBCConnection *odbcConnection;
                    ^
4 warnings generated.
  CXX(target) Release/obj.target/odbc/src/dynodbc.o
  SOLINK_MODULE(target) Release/odbc.node
  COPY /Users/hwilliams/node_modules/odbc/lib/bindings/napi-v3/odbc.node
  TOUCH Release/obj.target/action_after_build.stamp
npm WARN saveError ENOENT: no such file or directory, open '/Users/hwilliams/package.json'
npm notice created a lockfile as package-lock.json. You should commit this file.
npm WARN enoent ENOENT: no such file or directory, open '/Users/hwilliams/package.json'
npm WARN hwilliams No description
npm WARN hwilliams No repository field.
npm WARN hwilliams No README data
npm WARN hwilliams No license field.

+ odbc@2.3.5
added 69 packages from 74 contributors and audited 70 packages in 21.982s

1 package is looking for funding
  run `npm fund` for details

found 0 vulnerabilities

De-iMac:nodejs hwilliams$ npm list
/Users/hwilliams
├─┬ odbc@2.3.5
│ ├── async@3.2.0
│ ├── node-addon-api@1.7.2
│ └─┬ node-pre-gyp@0.14.0
│   ├── detect-libc@1.0.3
│   ├─┬ mkdirp@0.5.5
│   │ └── minimist@1.2.5
│   ├─┬ needle@2.6.0
│   │ ├─┬ debug@3.2.7
│   │ │ └── ms@2.1.3
│   │ ├─┬ iconv-lite@0.4.24
│   │ │ └── safer-buffer@2.1.2
│   │ └── sax@1.2.4
│   ├─┬ nopt@4.0.3
│   │ ├── abbrev@1.1.1
│   │ └─┬ osenv@0.1.5
│   │   ├── os-homedir@1.0.2
│   │   └── os-tmpdir@1.0.2
│   ├─┬ npm-packlist@1.4.8
│   │ ├─┬ ignore-walk@3.0.3
│   │ │ └─┬ minimatch@3.0.4
│   │ │   └─┬ brace-expansion@1.1.11
│   │ │     ├── balanced-match@1.0.0
│   │ │     └── concat-map@0.0.1
│   │ ├─┬ npm-bundled@1.1.1
│   │ │ └── npm-normalize-package-bin@1.0.1 deduped
│   │ └── npm-normalize-package-bin@1.0.1
│   ├─┬ npmlog@4.1.2
│   │ ├─┬ are-we-there-yet@1.1.5
│   │ │ ├── delegates@1.0.0
│   │ │ └─┬ readable-stream@2.3.7
│   │ │   ├── core-util-is@1.0.2
│   │ │   ├── inherits@2.0.4 deduped
│   │ │   ├── isarray@1.0.0
│   │ │   ├── process-nextick-args@2.0.1
│   │ │   ├── safe-buffer@5.1.2 deduped
│   │ │   ├─┬ string_decoder@1.1.1
│   │ │   │ └── safe-buffer@5.1.2 deduped
│   │ │   └── util-deprecate@1.0.2
│   │ ├── console-control-strings@1.1.0
│   │ ├─┬ gauge@2.7.4
│   │ │ ├── aproba@1.2.0
│   │ │ ├── console-control-strings@1.1.0 deduped
│   │ │ ├── has-unicode@2.0.1
│   │ │ ├── object-assign@4.1.1
│   │ │ ├── signal-exit@3.0.3
│   │ │ ├─┬ string-width@1.0.2
│   │ │ │ ├── code-point-at@1.1.0
│   │ │ │ ├─┬ is-fullwidth-code-point@1.0.0
│   │ │ │ │ └── number-is-nan@1.0.1
│   │ │ │ └── strip-ansi@3.0.1 deduped
│   │ │ ├─┬ strip-ansi@3.0.1
│   │ │ │ └── ansi-regex@2.1.1
│   │ │ └─┬ wide-align@1.1.3
│   │ │   └── string-width@1.0.2 deduped
│   │ └── set-blocking@2.0.0
│   ├─┬ rc@1.2.8
│   │ ├── deep-extend@0.6.0
│   │ ├── ini@1.3.8
│   │ ├── minimist@1.2.5 deduped
│   │ └── strip-json-comments@2.0.1
│   ├─┬ rimraf@2.7.1
│   │ └─┬ glob@7.1.6
│   │   ├── fs.realpath@1.0.0
│   │   ├─┬ inflight@1.0.6
│   │   │ ├── once@1.4.0 deduped
│   │   │ └── wrappy@1.0.2
│   │   ├── inherits@2.0.4
│   │   ├── minimatch@3.0.4 deduped
│   │   ├─┬ once@1.4.0
│   │   │ └── wrappy@1.0.2 deduped
│   │   └── path-is-absolute@1.0.1
│   ├── semver@5.7.1
│   └─┬ tar@4.4.13
│     ├── chownr@1.1.4
│     ├─┬ fs-minipass@1.2.7
│     │ └── minipass@2.9.0 deduped
│     ├─┬ minipass@2.9.0
│     │ ├── safe-buffer@5.1.2 deduped
│     │ └── yallist@3.1.1 deduped
│     ├─┬ minizlib@1.3.3
│     │ └── minipass@2.9.0 deduped
│     ├── mkdirp@0.5.5 deduped
│     ├── safe-buffer@5.1.2
│     └── yallist@3.1.1
└── zeppelin-solidity@1.3.0

De-iMac:nodejs hwilliams$ export ODBCINI=/Library/ODBC/odbc.ini

OpenLink ODBC 驱动程序连接

De-iMac:nodejs hwilliams$ cat node-uda.js 
const odbc = require('odbc');
 
async function queryData() {
const connection = await odbc.connect('DSN=sqllu-xs4all;UID=openlink;PWD=xxx');
const data = await connection.query('SELECT TOP 5 CustomerID, ContactName FROM Customers');
console.log(data);
}
 
queryData();
De-iMac:nodejs hwilliams$
De-iMac:nodejs hwilliams$ node node-uda.js
[
  { CustomerID: 'ALFKI', ContactName: 'Maria Anders' },
  { CustomerID: 'ANATR', ContactName: 'Ana Trujillo' },
  { CustomerID: 'ANTON', ContactName: 'Antonio Moreno' },
  { CustomerID: 'AROUT', ContactName: 'Thomas Hardy' },
  { CustomerID: 'BERGS', ContactName: 'Christina Berglund' },
  statement: 'SELECT TOP 5 CustomerID, ContactName FROM Customers',
  parameters: undefined,
  return: undefined,
  count: -1,
  columns: [
    {
      name: 'CustomerID',
      dataType: -8,
      columnSize: 5,
      decimalDigits: 0,
      nullable: false
    },
    {
      name: 'ContactName',
      dataType: -9,
      columnSize: 30,
      decimalDigits: 0,
      nullable: true
    }
  ]
]
De-iMac:nodejs hwilliams$

OpenLink ODBC 驱动程序可以从下载服务器获得 2.

Virtuoso ODBC 驱动程序连接

De-iMac:nodejs hwilliams$ cat node-virt.js
const odbc = require('odbc');
async function queryData() {
const connection = await odbc.connect('DSN=virtdemo;UID=demo;PWD=demo');
const data = await connection.query('SELECT TOP 5 CustomerID, ContactName FROM Demo..Customers');
console.log(data);

}

queryData();
De-iMac:nodejs hwilliams$
De-iMac:nodejs hwilliams$ node node-virt.js 
[
  { CustomerID: 'ALFKI', ContactName: 'Maria Anders' },
  { CustomerID: 'ANATR', ContactName: 'Ana Trujillo' },
  { CustomerID: 'ANTON', ContactName: 'Antonio Moreno' },
  { CustomerID: 'AROUT', ContactName: 'Thomas Hardy' },
  { CustomerID: 'BERGS', ContactName: 'Christina Berglund' },
  statement: 'SELECT TOP 5 CustomerID, ContactName FROM Demo..Customers',
  parameters: undefined,
  return: undefined,
  count: -1,
  columns: [
    {
      name: 'CustomerID',
      dataType: 12,
      columnSize: 5,
      decimalDigits: 0,
      nullable: false
    },
    {
      name: 'ContactName',
      dataType: 12,
      columnSize: 30,
      decimalDigits: 0,
      nullable: true
    }
  ]
]
De-iMac:nodejs hwilliams$

Virtuoso ODBC 驱动程序安装程序可以直接从以下链接获得:

有关的

 类似资料: