JavaScript 與 MySQL、MSSQL、Db2

這部分算是 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],
    ...
  ]
);

會跑去使用 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); 

後來會採用 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」形式的結構，裡面要包含 sql、params、ArraySize。

下面則是從官方測試 test-array-insert.js（GitHub）撈出來的範例：

// 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 來記錄每個欄位的型別、資料，甚至「長度」（param4 的 Length）…（更謎的是，那個 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();

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