JavaScript 與 MySQL、MSSQL、Db2

| | 0 Comments| 14:42
Categories:

由於前一陣子都是在用 JavaScript 透過 Node.js(官網)來寫各式各樣的腳本,自己也弄了個持續在運作的腳本系統;所以後來有需要寫腳本的時候,也都很自然地繼續用 JavaScript 來寫了。

而這一篇呢,則是稍微紀錄一下,前陣子因為在工作上的需求,透過 Node.js 腳本來存取資料庫的紀錄;由於不是專門搞這方面的,所以這邊主要就是弄到能動的紀錄了,給專門的人寫或許會有更好的方法吧?

由於各式各樣的狀況,這次連續搞了三種資料庫:MySQL/MariaDB、Microsoft SQL Server、IBM Db2…


MySQL(官網) / MariaDB(官網

這部分算是 Heresy 本來就比較熟系的部分了。

在 NPM 裡面,可以用 Node MySQL2(網頁)這個套件來做操作。

他的 API 基本上是 asynchronous 的架構,有傳統使用 callback 的設計,也有新的 promise 架構的設計,在使用上算是滿方便的~

他 promise API 基本上的使用方法,大致上是:

// get the client
const mysql = require('mysql2/promise');
// create the connection
const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test'
});
// query database
const [rows, fields] = await connection.execute(
    'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
    ['Morty', 14]); Console.log(rows); connection.end();

而要大量 insert 資料的時候,其實也很簡單,只要直接把參數都用二維振烈的形式丟進去就可以了。

conn.query(
  "INSERT INTO Table ( colA, colB ) VALUES ?",
  [
    [valA1, valB1],
    [valA2, valB2],
    ...
  ]
);

Microsoft SQL Server(官網

會跑去使用 MS SQL Server 主要是因為要使用的平台對於 MySQL 有支援性的問題,所以才需要切換到其他的資料庫;會選擇 MS SQL Server 的原因,也只是因為這邊有 MSDN 訂閱的測試授權可以用。

這邊是使用 NPM 的 node-mssql(網頁)來做存取。

他基本上是採用 promise 的架構,使用上也算方便。基本的使用大概是下面的樣子:

const sql = require('mssql');
const pool = await mssql.connect(config);
const request = pool.request();
const result = await request.query('select * from mytable where id=xxx');
console.log(result.recordset);
pool.close();

他回傳的結果的結構比較複雜一點,但是也算滿好操作的。

而連線參數的 config 則是下面的格式:

const config = {
  user: '...',
  password: '...',
  server: 'localhost',
  database: '...',
};

在大量插入資料的時候,主要是要透過 bulk() 這個函式。

相較於 mysql2,mssql 比較麻煩的是,在大量 insert 的時候,還需要先準備好 SQL 的資料表的細節,而且還需要指定格式…

const table = new sql.Table('table_name');
table.create = true;
table.columns.add('a', sql.Int, { nullable: true, primary: true });
table.columns.add('b', sql.VarChar(50), { nullable: false });
table.rows.add(777, 'test');
const request = new sql.Request();
request.bulk(table, (err, result) => {
  // error checks
});

由於感覺很麻煩,所以後來 Heresy 自己的解法,是直接透過 select 表格、然後拿它的結果來用。

const descTable = await request.query("SELECT TOP 0 * FROM " + sTableName);
return descTable.recordset.toTable(sTableName); 

IBM Db2(官網

後來會採用 Db2 呢,其實主要是最後才發現原來這個專案本來就有 Db2 的伺服器可以用。而考慮到整合問題,所以最後還是改用 Db2 了。

而這邊使用的,是 NPM 上的 node-ibm_db 這個套件(網頁)。

他的 API 風格…恩,基本上是透過 func()funcSync() 這樣的形式,來區分 synchronous 和 asynchronous 的版本。

雖然在 NPM 的頁面上是說 asynchronous 的版本則是採用 promise 架構的設計,不過在 API Documentation(網頁)這邊倒是沒有提及,所以 Heresy 沒有注意到,後來是都用 synchronous 的版本下去寫了。

它的使用形式,大致上如下:

var ibmdb = require('ibm_db');
var connStr = "DATABASE=<dbname>;HOSTNAME=<myhost>;"
         + "UID=<user>;PWD=<password>;PORT=<dbport>;PROTOCOL=TCPIP"; ibmdb.openSync(connStr); const err = conn.querySync('UPDATE tableA SET col1="X" WHERE col2="Y"'); const res = conn.queryResultSync('SELECT * FROM tableA WHERE col2="Y"'); const rows = res.fetchAllSync(); console.log(rows); rows.closeSync(); ibmdb.closeSync();

個人覺得,比較特別的,是他的 query 函式還有區分沒有回傳結果的 query() 以及有回傳結果的 queryResult();而如果是有結果的話,還要額外呼叫 fetchAll() 才能取得資料。

而在要大量插入資料的時候…個人只能說 ibm-db 的介面讓人覺得很謎了…

他和 mssql 類似,需要準備好一個完整的結構才能大量插入資料,這邊應該是要用「query options」形式的結構,裡面要包含 sqlparamsArraySize

下面則是從官方測試 test-array-insert.jsGitHub)撈出來的範例:

// prepare for each column
var param1 = {
  ParamType: "ARRAY",
  DataType: 1,
  Data: [null, 5, 6, 7, 8]
};
var param2 = {
  ParamType: "ARRAY",
  DataType: "DOUBLE",
  Data: [4.1, null, 6.14, 7, 8.3]
};
var param3 = {
  ParamType: "ARRAY",
  DataType: 1,
  Data: [0, 1, null, false, true]
};
var param4 = {
  ParamType: "ARRAY",
  DataType: 1,
  Data: ["", "Row 200", null, "Row 4000", "Last Row"],
  Length: 9
};
var queryOptions = {
  sql: "insert into arrtab values (?, ?, ?, ?)",
  params: [param1, param2, param3, param4],
  ArraySize: 5
};
conn.querySync(queryOptions);

可以看到,這邊透過 params 來記錄每個欄位的型別、資料,甚至「長度」(param4Length)…(更謎的是,那個 DataType 的 1 到底是什麼啊…)

而最讓 Heresy 覺得奇怪的地方,是一般直覺上資料應該都是一列一列處理的,但是這邊的資料需要針對每個欄位各自去處理… orz

總之,為了比較方便來處理,Heresy 就比照之前 mssql 的處理方式,透過從既有的資料表來讀取相關資訊了。寫法大致上如下:

iqo = {
  sql: "",
  params: [],
  ArraySize: 0
};
const res = conn.queryResultSync("SELECT * FROM tableA LIMIT 0");
for (let col of res.getColumnMetadataSync()) {
  let param = {
    Name: col.SQL_DESC_CONCISE_TYPE,
    ParamType: "ARRAY",
    DataType: col.SQL_DESC_TYPE_NAME,
    Data: []
  };
  if (param.DataType == 'VARCHAR')
    param.Length = col.SQL_DESC_LENGTH;
  iqo.params.push(param);
}
res.closeSync();

透過上面的程式,基本上就可以處理好針對 tableAparams,之後只要填入 SQL 語法、資料,理論上就可以完成大量資料插入了。


這篇記錄大概就這樣了。

這部分的東西沒意外的話也會移交給別人,所以除非之後還要做其他的東西,否則大概就這樣了。

Leave a Reply

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *