Rustでasync/awaitに対応したTiberiusからSQLServerに接続する
一年くらい前に Rust から ODBC ドライバで接続する方法を試してました。
その当時、直接 TDS プロトコルを扱う tiberius クレートも試してみたのですが、依存するクレートのバージョンを固定する必要があり、コードが書き辛い状態でした。
しかし、改めて確認したところ、いつの間にやら async/await に対応した tiberius クレートの開発が進んでます。
tokio だけでなく async-std にも対応しているとの事なので、今回は async-std を利用して CRUD なコードを試してみました。
1. 環境
- OS: KDE neon 5.21.4 (Ubuntu 20.04 ベース) / macOS Mojave v10.14.6
- rustc 1.51.0
- SQL Server 2019 (RTM-CU10) (KB5001090) - 15.0.4123.1 (X64) on Linux
- SQL Server Command Line Tool Version 17.7
2. プロジェクト
(1) 準備
適当なディレクトリにプロジェクトを作成します。(今回は ~/work/crud_mssql)
$ cd ~/work $ cargo new crud_mssql $ cd crud_mssql
(2) データベース
テスト用のデータベースを用意します。(今回は my_test_db)
※既存のデータベースを利用する場合は不要です。
$ sqlcmd -S localhost -U sa -P abcd1234$ -Q "CREATE DATABASE my_test_db;"
(3) ファイル
・Cargo.toml の編集
[dependencies] セクションに下記の内容を追加します。
[dependencies] tiberius = { version = "0.5", features = ["chrono"] } async-std = { version = "1.9", features = ["attributes"] } chrono = "0.4"
・ソースコード: src/main.rs
下記のソースコードをコピーして src/main.rs を書き換えます。
※ソース中の conn_str の内容は、試す環境に合わせて変更してください。
use async_std::net::TcpStream; use tiberius::{Client, ColumnData, Config, FromSqlOwned, QueryResult}; #[async_std::main] async fn main() -> Result<(), Box<dyn std::error::Error>> { println!("#### Start ####"); let conn_str = "Server=tcp:localhost,1433;TrustServerCertificate=yes;Database=my_test_db;UID=sa;PWD=abcd1234$"; let config = Config::from_ado_string(conn_str).unwrap(); let tcp = TcpStream::connect(config.get_addr()).await?; tcp.set_nodelay(true)?; let mut client = Client::connect(config, tcp).await?; println!("-- DROP & CREATE TABLE"); client.execute("DROP TABLE IF EXISTS 会員名簿", &[]).await?; client .execute( "CREATE TABLE 会員名簿 (番号 int, 氏名 nvarchar(40), 誕生日 date)", &[], ) .await?; println!("-- INSERT"); let members = vec![ (110, "岸本 龍也", "1989-11-06"), (210, "荒井 伸次郎", "1974-01-30"), (105, "江口 美奈", "1979-06-23"), (304, "長田 隆次", "1991-05-25"), (307, "中居 雄樹", "1984-02-29"), ]; for (id, name, birthday) in members { let inserted = client .execute( "INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (@P1, @P2, @P3)", &[&id, &name, &birthday], ) .await?; assert_eq!(&[1], inserted.rows_affected()); } let result = client.simple_query("SELECT * FROM 会員名簿").await?; display(result).await?; println!("-- UPDATE"); let update_id = 307; let new_name = "中井 雄樹"; let updated = client .execute( "UPDATE 会員名簿 SET 氏名=@P1 WHERE 番号=@P2", &[&new_name, &update_id], ) .await?; assert_eq!(&[1], updated.rows_affected()); let result = client .simple_query("SELECT 番号,氏名 FROM 会員名簿 ORDER BY 番号") .await?; display(result).await?; println!("-- DELETE"); let delete_id = 210; let deleted = client .execute("DELETE FROM 会員名簿 WHERE 番号=@P1", &[&delete_id]) .await?; assert_eq!(&[1], deleted.rows_affected()); let result = client .simple_query("SELECT 番号,氏名,誕生日 FROM 会員名簿 ORDER BY 番号") .await?; display(result).await?; println!("#### Finish ####"); Ok(()) } async fn display(result_set: QueryResult<'_>) -> Result<(), Box<dyn std::error::Error>> { let cols = result_set.columns().unwrap(); for col in cols { print!(" | {}", col.name()); } println!(" |"); let rows = result_set.into_first_result().await?; let rows_affcted = rows.len(); let mut row_count = 0; for row in rows { for col in row { match col { ColumnData::I32(Some(v)) => print!(" | {}", v), ColumnData::String(Some(v)) => print!(" | {}", v), ColumnData::Date(_) => { print!(" | {}", chrono::NaiveDate::from_sql_owned(col)?.unwrap()) } _ => print!(" | {:?}", col), }; } println!(" |"); row_count += 1; } println!("結果 {} 行 ({})", row_count, rows_affcted); Ok(()) }
準備は以上です。
3. 実行結果
$ cargo run Finished dev [unoptimized + debuginfo] target(s) in 1.81s Running `target/debug/crud_mssql` #### Start #### -- DROP & CREATE TABLE -- INSERT | 番号 | 氏名 | 誕生日 | | 110 | 岸本 龍也 | 1989-11-06 | | 210 | 荒井 伸次郎 | 1974-01-30 | | 105 | 江口 美奈 | 1979-06-23 | | 304 | 長田 隆次 | 1991-05-25 | | 307 | 中居 雄樹 | 1984-02-29 | 結果 5 行 (5) -- UPDATE | 番号 | 氏名 | | 105 | 江口 美奈 | | 110 | 岸本 龍也 | | 210 | 荒井 伸次郎 | | 304 | 長田 隆次 | | 307 | 中井 雄樹 | 結果 5 行 (5) -- DELETE | 番号 | 氏名 | 誕生日 | | 105 | 江口 美奈 | 1979-06-23 | | 110 | 岸本 龍也 | 1989-11-06 | | 304 | 長田 隆次 | 1991-05-25 | | 307 | 中井 雄樹 | 1984-02-29 | 結果 4 行 (4) #### Finish ####
4. 補足
前述のコードでは async-std で属性によるランタイムの起動を指定してます。
初めての async-std だったのですが、async_std::task::spawn() する良い例が思い浮かばなかったので、CRUDの処理をそのまま書いてます。
この件とは別にコードを書いて試したところ、ODBC 接続を試した際に微妙だった日本語や絵文字や結合文字等は、特に問題ありませんでした。
日付型や数値型についても、それぞれ chrono, rust_decimal を features に指定すれば利用できてます。
現段階では、接続プールが実装されていないため、非同期な接続プールを利用するには、少々工夫が必要です。
※と書きましたが、tokio にして bb8 / bb8-tiberius で進める方が無難です。
何れにしても、以前より格段に書きやすく喜ばしい限りです。
SQLServerでサロゲートペアや結合文字列を扱う前に照合順序を確認しよう
SQL Server 2019 での話です。
Unicode の𠮷(つちよし), 𩸽(ほっけ)や 🍺(ビール), 👨👩👧👦(家族: 父母と女の子と男の子)のような絵文字等を扱う場合、データベースがサロゲートペアや結合文字列に対応している必要があります。
SQL Server の Unicode データ型は SQL Server 2005 から対応済みで、SQL Server 2019 では新しい UTF-8 対応の照合順序も加わり、より扱いやすくなってます。
公式サイトにも説明があります。
しかし、この説明の中に補助文字に関する記述があり、SCA 照合順序なしで Unicode を扱う場合、結果として「 0 – 0xFFFF の範囲外の文字は無視されてしまう」事が示唆されています。
これが地味に影響する部分がクエリというか T-SQL の式に Unicode 文字列を使用する場合で、前述の範囲外の文字に該当する場合、その文字コードが除外された状態で式が評価される事があります。
つまり、照合順序の設定次第では「式の評価が異なるため、意図しない結果を得てしまう場合がある」という事です。
検証してみる
後述する作業は Azure Data Studio (または SSMS) から SQL Server 2019 に接続した状態で行いました。※Windows版 / Linux版 どちらも同じ結果でした。
(1) 検証用データベースの作成と照合順序の確認
検証用データベースを 2 種類用意します。
my_test_db | 既定の照合順序のまま ※Japanese_CI_AS |
---|---|
my_xjis140_db | 照合順序に Japanese_XJIS_140_CI_AS を指定 |
ここでは、次の SQL 文を実行してデータベースを作成します。
CREATE DATABASE my_test_db; CREATE DATABASE my_xjis140_db COLLATE Japanese_XJIS_140_CI_AS;
照合順序の確認ではデータベースが Auto Closed のままだと情報を取得できません。
なので Azure Data Studio であれば、Manage や New Query のデータベース切替等で活性化します。
活性化が済んだら照合順序を確認します。
SELECT SERVERPROPERTY('Collation') as N'サーバの照合順序'; SELECT name, collation_name FROM sys.databases WHERE name LIKE 'my_%';
※ここで collation_name が NULL の場合、データベースが活性化してません。
(2) Unicode 文字の格納
テーブル変数に非Unicode データ型, Unicode データ型, UTF8 エンコード対応のデータ型を用意します。そこにサロゲートペアで表現される絵文字🍺と結合絵文字👨👩👧👦を格納し、それぞれの値を確認します。
DECLARE @a table ( non_unicode varchar(40), unicode_utf16 nvarchar(40), unicode_utf8 varchar(40) COLLATE Japanese_XJIS_140_CI_AS_UTF8 ); INSERT INTO @a VALUES (N'🍺', N'🍺', N'🍺'); INSERT INTO @a VALUES (N'👨👩👧👦', N'👨👩👧👦', N'👨👩👧👦'); SELECT * FROM @a; SELECT CAST(non_unicode AS varbinary), CAST(unicode_utf16 AS varbinary), CAST(unicode_utf8 AS varbinary) FROM @a;
データベースに対してクエリを発行します。
※下画面の例は my_test_db で実行した結果です。
これは my_test_db, my_xjis140_db どちらで実行しても同じ結果になります。
varchar では文字が化けて ?? で埋められていますが、nvarchar と UTF8 エンコーディングを指定した varchar では、それぞれ UTF-16LE, UTF-8 で符号化された値が格納されます。
データの格納先としては、データベースの照合順序に関わらず、問題なく扱える事が確認できました。
(3) クエリの式で Unicode 文字列を使用
テーブル変数に絵文字を含む値を格納し、データを抽出してみます。
DECLARE @b table ( name_utf16 nvarchar(40), name_utf8 varchar(40) COLLATE Japanese_XJIS_140_CI_AS_UTF8 ); INSERT INTO @b VALUES (N'超🍺ドライ', N'超🍺ドライ'); INSERT INTO @b VALUES (N'墨🍺🍺ラベル', N'墨🍺🍺ラベル'); INSERT INTO @b VALUES (N'最速🍺絞り', N'最速🍺絞り'); SELECT * FROM @b; SELECT * FROM @b WHERE name_utf16 LIKE N'%🍺🍺%'; SELECT * FROM @b WHERE name_utf8 LIKE N'%🍺🍺%';
このクエリを実行したところ、my_test_db (照合順序: Japanese_CI_AS) と my_xjis140_db (照合順序: Japanese_XJIS_140_CI_AS) で異なる結果になりました。
my_xjis140_db では特に問題なく、意図した結果が得られました。
しかし、my_test_db では格納したデータが期待通りに抽出できません。
照合順序 Japanese_CI_AS に補助文字のサポートが含まれないからです。
....
式の評価では、サポート対象外な文字を除いての評価になるようです。
例えば、次のクエリを my_test_db で実行すると、墨🍺ラベル
が返却されます。
DECLARE @c table ( name_utf16 nvarchar(40) ); INSERT INTO @c VALUES (N'超🍺ドライ'); INSERT INTO @c VALUES (N'中トロ🍣'); INSERT INTO @c VALUES (N'こはだ🍣'); INSERT INTO @c VALUES (N'墨🍺ラベル'); INSERT INTO @c VALUES (N'あじ🍣'); INSERT INTO @c VALUES (N'最速🍺絞り'); SELECT * FROM @c WHERE name_utf16 = N'墨🗻𩸽🍣𠮷🍺ラベル';
式の評価の際、墨🍺ラベル
と墨🗻𩸽🍣𠮷🍺ラベル
に含まれるサポート対象外の文字を省略すると、どちらも墨ラベル
になります。
この状態で式を評価すると元の文字列が異なるのに「等しい」と評価されてしまいます。
影響は WHERE 句だけでなく、SELECT 句でも同様です。
DECLARE @d table ( name_utf16 nvarchar(40) ); INSERT INTO @d VALUES (N'超🍺ドライ'); INSERT INTO @d VALUES (N'中トロ🍣'); INSERT INTO @d VALUES (N'こはだ🍣'); INSERT INTO @d VALUES (N'墨🍺ラベル'); INSERT INTO @d VALUES (N'あじ🍣'); INSERT INTO @d VALUES (N'最速🍺絞り'); SELECT SUM(t.is_beer) as beer, SUM(t.is_sushi) as sushi FROM ( SELECT (CASE WHEN name_utf16 LIKE N'%🍺%' THEN 1 ELSE 0 END) as is_beer, (CASE WHEN name_utf16 LIKE N'%🍣%' THEN 1 ELSE 0 END) as is_sushi FROM @d ) as t
特定の絵文字を含むデータ数を集計するようなクエリでは誤った集計結果になります。
一応、データベースの照合順序が Japanese_CI_AS のままでも、式の評価時に _140 系等の補助文字をサポートする照合順序を指定すれば、一時的に回避できます。
SELECT (CASE WHEN name_utf16 COLLATE Japanese_XJIS_140_CI_AS LIKE N'%🍺%' THEN 1 ELSE 0 END) as is_beer, (CASE WHEN name_utf16 COLLATE Japanese_XJIS_140_CI_AS LIKE N'%🍣%' THEN 1 ELSE 0 END) as is_sushi FROM @d
しかし、この方法は性能面では期待できない上、根本的な解決にはなってません。
そもそも、照合順序を Japanese_XJIS_140_CI_AS に設定して作成したデータベースの方は、問題なく意図した結果が得られています。
結論
SQLServerでサロゲートペアや結合文字列を扱う場合、データベース作成時に適切な照合順序を指定しましょう。
....
前述した公式サイトには、注意書きとして「SQL Server 2014 (12.x) 以降では、すべての新しい _140 照合順序で補助文字が自動的にサポートされます。」とあります。
ちなみに日本語の照合順序で指定可能な _140 系の一覧は次のクエリで取得できます。
SELECT * FROM fn_helpcollations() WHERE name LIKE 'Ja%' AND name LIKE '%_140%';
不要なトラブルを避ける意味でも、照合順序は指定するべきかと。
Knex.js ver 0.95.0以降でSQLServerのdatabase libraryがtediousに変わってた
Knex.js を利用して SQLServer に接続するコードを書いて実行したところ、
Error: No event 'socketConnect' in state 'SentPrelogin'
のようなエラーが発生して接続できませんでした。
他の DB には接続できるのに SQLServer だけ接続できないのは釈然としません。
非常に困ってたのですが、同じ問題に直面している記事を見つけました。
- node.js - Error: No event 'socketConnect' in state 'SentPrelogin' - Stack Overflow
- knex/UPGRADING.md at master · knex/knex · GitHub
要するに、node-mssql だといろいろ問題があったので、Knex.js ver 0.95.0以降では直接 tedious を使うようになったそうです。
そのため、今後はnpm install mssql
ではなく、npm install tedious
にする必要があります。
当該ブロジェクトの package.json を確認したら、"knex": "^0.95.4",
だったので、
$ npm uninstall mssql $ npm install tedious
のように処理したところ、問題なく接続できました。
※Linux (KDE neon 5.21.3)と Mac (Intel macOS Mojave10.14.6)で確認済み
おまけ
当初、SQL Builder として Squel.js を利用しようとしたら、Knex.js を奨められたので、そのまま成り行きで利用してます。
ついでに、以前に書いた node-mssql を利用したコードを同じ条件で Knex.js に書き換えてみました。
const knex = require('knex')({ client: 'mssql', // ここは 'mssql' のまま connection: { server: 'localhost', user: 'sa', password: 'abcd1234$', database: 'my_test_db', options: { enableArithAbort: true, } } }); (async () => { console.log("#### Start ####"); try { console.log("-- DROP & CREATE TABLE"); await knex.schema.dropTableIfExists("会員名簿"); await knex.schema.createTable("会員名簿", table => { table.integer("番号"); table.string("氏名", 40); table.date("誕生日"); }); console.log("-- INSERT"); for (const [id, name, birthday] of [ [ 110, "岸本 龍也", "1989-11-06" ], [ 210, "荒井 伸次郎", "1974-01-30" ], [ 105, "江口 美奈", "1979-06-23" ], [ 304, "長田 隆次", "1991-05-25" ], [ 307, "中居 雄樹", "1984-02-29" ], ]) { await knex("会員名簿").insert({ "番号": id, "氏名": name, "誕生日": birthday, }); } display(await knex.select().table("会員名簿")); console.log("-- UPDATE"); const update_id = 307; const new_name = "中井 雄樹"; await knex("会員名簿").where("番号", "=", update_id) .update({ "氏名": new_name }); display( await knex.select("番号","氏名").from("会員名簿") .orderBy("番号") ); console.log("-- DELETE"); const delete_id = 210; await knex("会員名簿").where("番号", "=", delete_id).del(); display( await knex.select("番号","氏名","誕生日").from("会員名簿") .orderBy("誕生日", "desc") ); } catch (e) { console.log("#### Catch !! ####"); console.log(e); } finally { knex.destroy(); } console.log("#### Finish ####"); })() function display(rs) { let row_count = 0; if (rs.length > 0) { // カラム名 const columns = Object.keys(rs[0]); const header = []; for (const col_name of columns) { header.push(` | ${col_name}`); } header.push(" |"); console.log(header.join("")); // ロー for (const row of rs) { const buff = []; for (const col_name of columns) { buff.push(` | ${row[col_name]}`); } buff.push(" |"); console.log(buff.join("")); ++row_count; } } console.log(`結果 ${row_count} 行 (${rs.length})`); }
ソースコードの生SQL文字列を QueryBuilder / SchemaBuilder で書き換えてます。
個人的には、生SQL文字列と比較しても、あまり違和感はないかと。
検証用はともかく、新規なのに大量の埋め込みSQL文は見たくないですね。
LinuxのNode.jsでnode-odbcとMS版ODBCドライバからSQLServerに接続する
node-odbc モジュールから SQLServer への接続は FreeTDS の ODBC ドライバとの組み合わせで確認できました。
しかし、同じソースコードを Microsoft 版 ODBC ドライバで実行すると、文字化けしてしまい、接続に失敗します。
プログラミング ガイドラインに書いてあるようにsetlocale(LC_ALL, "")
相当で解決するのは承知しているのですが、これを Node.js でどうすれば良いのか解りません。
MDNのIntl.localeあたりを読んで試してみたけど思う結果になりません。
仕方なく、setlocale(LC_ALL, "")
相当を処理する Node.js のアドオンを作成したところ、無事に接続できるようになりました。
正規な設定方法は不明ですが、条件は整ったので node-mssql モジュールの場合と同様なコードを試してみます。
1. 環境
- OS: KDE neon 5.21.3 (Ubuntu 20.04 ベース)
- Node.js v14.16.0 (npm / npx 6.14.11)
- SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) on Linux
- SQL Server Command Line Tool (w/ Microsoft ODBC Driver) Version 17.7
- unixODBC 2.3.7
- gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0
2. プロジェクト
(1) 準備
まずは適当なディレクトリを用意します。(今回は ~/work/crud_msodbc)
$ cd ~/work $ mkdir crud_msodbc $ cd crud_msodbc
初期化してモジュールをインストールします。
$ npm init -y $ npm install odbc
(2) データベース
テスト用のデータベースを用意します。(今回は my_test_db)
※既存のデータベースを利用する場合は不要です。
$ sqlcmd -S localhost -U sa -P abcd1234$ -Q "CREATE DATABASE my_test_db;"
(3) ファイル
下記のソースコードをコピーして crud.js というファイルを作成します。
※ソース中の conn_str の値は、試す環境に適した接続文字列を指定してください。
const addon = require('./addon/build/Release/addon').setlocale(); const odbc = require("odbc"); const conn_str = 'Driver={ODBC Driver 17 for SQL Server};Server=localhost;UID=sa;PWD=abcd1234$;Database=my_test_db;'; (async () => { console.log("#### Start ####"); try { const conn = await odbc.connect(conn_str); console.log("-- DROP & CREATE TABLE"); await conn.query("DROP TABLE IF EXISTS 会員名簿"); await conn.query("CREATE TABLE 会員名簿 (番号 int, 氏名 nvarchar(40), 誕生日 date)"); console.log("-- INSERT"); for (const row of [ [ 110, "岸本 龍也", "1989-11-06" ], [ 210, "荒井 伸次郎", "1974-01-30" ], [ 105, "江口 美奈", "1979-06-23" ], [ 304, "長田 隆次", "1991-05-25" ], [ 307, "中居 雄樹", "1984-02-29" ], ]) { const stmt_ins = await conn.createStatement(); await stmt_ins.prepare("INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (?,?,?)"); await stmt_ins.bind(row); const inserted = await stmt_ins.execute(); console.dir([inserted.statement, inserted.parameters]); } display(await conn.query("SELECT * FROM 会員名簿")); console.log("-- UPDATE"); const update_id = 307; const new_name = "中井 雄樹"; const stmt_upd = await conn.createStatement(); await stmt_upd.prepare("UPDATE 会員名簿 SET 氏名=? WHERE 番号=?"); await stmt_upd.bind([new_name, update_id]); const updated = await stmt_upd.execute(); console.dir([updated.statement, updated.parameters]); display(await conn.query("SELECT 番号,氏名 FROM 会員名簿 ORDER BY 番号")); console.log("-- DELETE"); const delete_id = 210; const stmt_del = await conn.createStatement(); await stmt_del.prepare("DELETE FROM 会員名簿 WHERE 番号=?"); await stmt_del.bind([delete_id]); const deleted = await stmt_del.execute(); console.dir([deleted.statement, deleted.parameters]); display(await conn.query("SELECT 番号,氏名,誕生日 FROM 会員名簿 ORDER BY 番号")); conn.close(); } catch (e) { console.log("#### Catch !! ####") console.log(e) } console.log("#### Finish ####"); })() function display(rs) { // カラム名表示 const columns = rs.columns; const header = []; for (const col of columns) { header.push(` | ${col.name}`); } header.push(" |"); console.log(header.join("")); // ロー表示 let row_count = 0; for (const row of rs) { const buff = []; for (const col of columns) { buff.push(` | ${row[col.name]}`); } buff.push(" |"); console.log(buff.join("")); ++row_count; } console.log(`結果 ${row_count} 行 (${rs.length})`); }
(4) Node.js addon 作成
続いて setlocale() に必要な C++ addon を作成します。
現在のディレクトリ配下に作業用ディレクトリ addon を作成します。
$ mkdir addon $ cd addon
下記のソースコードをコピーしてそれぞれファイルを作成します。
・ファイル: binding.gyp
{ "targets": [ { "target_name": "addon", "sources": [ "setlocale.cc" ] } ] }
・ファイル: setlocale.cc
#include <node.h> #include <locale> void Method(const v8::FunctionCallbackInfo<v8::Value>& args) { std::locale::global(std::locale("")); } void Initialize(v8::Local<v8::Object> exports) { NODE_SET_METHOD(exports, "setlocale", Method); } NODE_MODULE(NODE_GYP_MODULE_NAME, Initialize)
それぞれのファイルを作成したら、確認後npx node-gyp configure build
を実行して addon を作成します。
$ ls binding.gyp setlocale.cc $ npx node-gyp configure build npx: 97個のパッケージを4.475秒でインストールしました。 gyp info it worked if it ends with ok gyp info using node-gyp@7.1.2 gyp info using node@14.16.0 | linux | x64 gyp info find Python using Python version 3.8.5 found at "/usr/bin/python3" gyp info spawn /usr/bin/python3 ・・・(ざっくり省略)・・・ gyp info ok $ ls binding.gyp build setlocale.cc $ ls build/Release/ addon.node obj.target $ cd .. $ ls addon crud.js node_modules package-lock.json package.json
エラーがなければ、./addon/build/Release/ にaddon.node
ファイルが生成されます。
準備は以上です。
3. 実行結果
$ node crud.js #### Start #### -- DROP & CREATE TABLE -- INSERT [ 'INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (?,?,?)', [ 110, '岸本 龍也', '1989-11-06' ] ] [ 'INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (?,?,?)', [ 210, '荒井 伸次郎', '1974-01-30' ] ] [ 'INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (?,?,?)', [ 105, '江口 美奈', '1979-06-23' ] ] [ 'INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (?,?,?)', [ 304, '長田 隆次', '1991-05-25' ] ] [ 'INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (?,?,?)', [ 307, '中居 雄樹', '1984-02-29' ] ] | 番号 | 氏名 | 誕生日 | | 110 | 岸本 龍也 | 1989-11-06 | | 210 | 荒井 伸次郎 | 1974-01-30 | | 105 | 江口 美奈 | 1979-06-23 | | 304 | 長田 隆次 | 1991-05-25 | | 307 | 中居 雄樹 | 1984-02-29 | 結果 5 行 (5) -- UPDATE [ 'UPDATE 会員名簿 SET 氏名=? WHERE 番号=?', [ '中井 雄樹', 307 ] ] | 番号 | 氏名 | | 105 | 江口 美奈 | | 110 | 岸本 龍也 | | 210 | 荒井 伸次郎 | | 304 | 長田 隆次 | | 307 | 中井 雄樹 | 結果 5 行 (5) -- DELETE [ 'DELETE FROM 会員名簿 WHERE 番号=?', [ 210 ] ] | 番号 | 氏名 | 誕生日 | | 105 | 江口 美奈 | 1979-06-23 | | 110 | 岸本 龍也 | 1989-11-06 | | 304 | 長田 隆次 | 1991-05-25 | | 307 | 中井 雄樹 | 1984-02-29 | 結果 4 行 (4) #### Finish ####
node-odbc モジュールでは .query() の戻り値に発行した SQL 文とパラメータ値が保持されているので、ついでに表示してます。
4. 補足
Microsoft 版 ODBC ドライバでは、無事にプレースホルダが使えました。
日本語も問題なく通ります。
ちなみに setlocale() の処理を消すと ODBC ドライバのエラーが発生します。
#### Catch !! #### [Error: [odbc] Error executing the sql statement] { odbcErrors: [ { state: '42000', code: 102, message: "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]'�' ��k\ri\x07jˇLB�~Y\x02" } ] }
今回はアドオンの追加で回避しましたが、正規の方法があるなら知りたいです。
LinuxのNode.jsでnode-odbcとFreeTDSのODBCドライバからSQLServerに接続する
Node.js から SQLServer に接続する場合、node-mssql または直接 tedious モジュールを利用すればいいのですが、ODBC ドライバでの接続方法も調べてみました。
www.npmjs.com で調べたところ node-odbc モジュールを見つけました。
ODBC ドライバマネージャの unixODBC とそれに対応した ODBC ドライバであれば接続できるとの事なので、node-mssql モジュールの場合と同様なコードで試してみます。
1. 環境
- OS: KDE neon 5.21.3 (Ubuntu 20.04 ベース)
- Node.js v14.16.0 (npm 6.14.11)
- SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) on Linux
- SQL Server Command Line Tool (w/ Microsoft ODBC Driver) Version 17.7
- unixODBC 2.3.7
- Ubuntuパッケージ: focal / freetds-dev (1.1.6-1.1)
- Ubuntuパッケージ: focal / tdsodbc (1.1.6-1.1)
Microsoft 版 ODBC ドライバだと文字化けが発生するため、FreeTDS の ODBC ドライバ (tdsodbc) で試してます。
2. プロジェクト
(1) 準備
まずは適当なディレクトリを用意します。(今回は ~/work/crud_tdsodbc)
$ cd ~/work $ mkdir crud_tdsodbc $ cd crud_tdsodbc
初期化してモジュールをインストールします。
$ npm init -y $ npm install odbc
(2) データベース
テスト用のデータベースを用意します。(今回は my_test_db)
※既存のデータベースを利用する場合は不要です。
$ sqlcmd -S localhost -U sa -P abcd1234$ -Q "CREATE DATABASE my_test_db;"
(3) ファイル
下記のソースコードをコピーして crud.js というファイルを作成します。
なお、ソース中の conn_str の値は、freetds.conf を参照しない DSN-less 接続です。
試す場合は環境に適した接続文字列を指定してください。
const odbc = require("odbc"); const conn_str = "Driver={FreeTDS};Server=localhost;Port=1433;TDS_Version=7.4;Client_Charset=UTF-8;UID=sa;PWD=abcd1234$;Database=my_test_db;"; (async () => { console.log("#### Start ####"); try { const conn = await odbc.connect(conn_str); console.log("-- DROP & CREATE TABLE"); await conn.query("DROP TABLE IF EXISTS 会員名簿"); await conn.query("CREATE TABLE 会員名簿 (番号 int, 氏名 nvarchar(40), 誕生日 date)"); console.log("-- INSERT"); for (const [id, name, birthday] of [ [ 110, "岸本 龍也", "1989-11-06" ], [ 210, "荒井 伸次郎", "1974-01-30" ], [ 105, "江口 美奈", "1979-06-23" ], [ 304, "長田 隆次", "1991-05-25" ], [ 307, "中居 雄樹", "1984-02-29" ], ]) { const inserted = await conn.query(`INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (${id},N'${name}','${birthday}')`); console.log(inserted.statement); } display(await conn.query("SELECT * FROM 会員名簿")); console.log("-- UPDATE"); const update_id = 307; const new_name = "中井 雄樹"; const updated = await conn.query(`UPDATE 会員名簿 SET 氏名=N'${new_name}' WHERE 番号=${update_id}`); console.log(updated.statement); display(await conn.query("SELECT 番号,氏名 FROM 会員名簿 ORDER BY 番号")); console.log("-- DELETE"); const delete_id = 210; const deleted = await conn.query(`DELETE FROM 会員名簿 WHERE 番号=${delete_id}`); console.log(deleted.statement); display(await conn.query("SELECT 番号,氏名,誕生日 FROM 会員名簿 ORDER BY 番号")); conn.close(); } catch (e) { console.log("#### Catch !! ####") console.log(e) } console.log("#### Finish ####"); })() function display(rs) { // カラム名表示 const columns = rs.columns; const header = []; for (const col of columns) { header.push(` | ${col.name}`); } header.push(" |"); console.log(header.join("")); // ロー表示 let row_count = 0; for (const row of rs) { const buff = []; for (const col of columns) { buff.push(` | ${row[col.name]}`); } buff.push(" |"); console.log(buff.join("")); ++row_count; } console.log(`結果 ${row_count} 行 (${rs.length})`); }
準備は以上です。
3. 実行結果
$ node crud.js #### Start #### -- DROP & CREATE TABLE -- INSERT INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (110,N'岸本 龍也','1989-11-06') INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (210,N'荒井 伸次郎','1974-01-30') INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (105,N'江口 美奈','1979-06-23') INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (304,N'長田 隆次','1991-05-25') INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (307,N'中居 雄樹','1984-02-29') | 番号 | 氏名 | 誕生日 | | 110 | 岸本 龍也 | 1989-11-06 | | 210 | 荒井 伸次郎 | 1974-01-30 | | 105 | 江口 美奈 | 1979-06-23 | | 304 | 長田 隆次 | 1991-05-25 | | 307 | 中居 雄樹 | 1984-02-29 | 結果 5 行 (5) -- UPDATE UPDATE 会員名簿 SET 氏名=N'中井 雄樹' WHERE 番号=307 | 番号 | 氏名 | | 105 | 江口 美奈 | | 110 | 岸本 龍也 | | 210 | 荒井 伸次郎 | | 304 | 長田 隆次 | | 307 | 中井 雄樹 | 結果 5 行 (5) -- DELETE DELETE FROM 会員名簿 WHERE 番号=210 | 番号 | 氏名 | 誕生日 | | 105 | 江口 美奈 | 1979-06-23 | | 110 | 岸本 龍也 | 1989-11-06 | | 304 | 長田 隆次 | 1991-05-25 | | 307 | 中井 雄樹 | 1984-02-29 | 結果 4 行 (4) #### Finish ####
node-odbc モジュールでは .query() の戻り値に発行した SQL 文が保持されているので、ついでに表示してます。
4. 補足
パラメータ値を扱う際はプレースホルダを利用したいのですが、私の環境では node-odbc モジュールと FreeTDS の ODBC ドライバの組み合わせでクエリを発行すると unixODBC のエラーが発生します。
#### Catch !! #### [Error: [odbc] Error retrieving information about the parameters in the statement] { odbcErrors: [ { state: 'IM001', code: 0, message: '[unixODBC][Driver Manager]Driver does not support this function' } ] }
仕方ないので、この件についてはパラメータ値をテンプレートリテラルで処理して回避しました。
なお、この現象は C++ の nanodbc ライブラリでも確認してます。
node-odbc モジュールだけの問題ではありません。
ちなみに node-odbc モジュールと Microsoft 版の ODBC ドライバの組み合わせではプレースホルダが使えます。※文字化けを回避できれば、の話ですが...
Node.jsでnode-mssqlを利用してSQLServerに接続する
Node.js から SQLServer へ接続について、Microsoft のサイトにはtedious モジュールでの開発方法が掲載されています。
tedious モジュールを直接利用してコーディングする場合、どうしてもイベントの記述が煩雑になりがちなので、ずっと敬遠してました。
しかし、何気に開発元の GitHub を覗いたところ、便利そうなモジュールがありました。
README.md を読んで興味が湧いたので、CRUD なコードで試してみました。
1. 環境
- OS: KDE neon 5.21.3 (Ubuntu 20.04 ベース)
- Node.js v14.16.0 (npm 6.14.11)
- SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) on Linux
- SQL Server Command Line Tool Version 17.7
2. プロジェクト
(1) 準備
まずは適当なディレクトリを用意します。(今回は ~/work/crud_mssql)
$ cd ~/work $ mkdir crud_mssql $ cd crud_mssql
初期化してモジュールをインストールします。
$ npm init -y $ npm install mssql
(2) データベース
テスト用のデータベースを用意します。(今回は my_test_db)
※既存のデータベースを利用する場合は不要です。
$ sqlcmd -S localhost -U sa -P abcd1234$ -Q "CREATE DATABASE my_test_db;"
(3) ファイル
下記のソースコードをコピーして crud.js というファイルを作成します。
※ソース中の config の内容は、試す環境に合わせて変更してください。
const sql = require('mssql'); const config = { server: 'localhost', user: 'sa', password: 'abcd1234$', database: 'my_test_db', options: { enableArithAbort: true, } }; (async () => { console.log("#### Start ####"); try { const conn = await sql.connect(config); console.log("-- DROP & CREATE TABLE"); await conn.query("DROP TABLE IF EXISTS 会員名簿"); await conn.query("CREATE TABLE 会員名簿 (番号 int, 氏名 nvarchar(40), 誕生日 date)"); console.log("-- INSERT"); for (const [id, name, birthday] of [ [ 110, "岸本 龍也", "1989-11-06" ], [ 210, "荒井 伸次郎", "1974-01-30" ], [ 105, "江口 美奈", "1979-06-23" ], [ 304, "長田 隆次", "1991-05-25" ], [ 307, "中居 雄樹", "1984-02-29" ], ]) { await conn.request() .input('id', id) .input('name', name) .input('birthday', birthday) .query("INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (@id,@name,@birthday)"); } display(await conn.query("SELECT * FROM 会員名簿")); console.log("-- UPDATE"); const update_id = 307; const new_name = "中井 雄樹"; await conn.request() .input('name', new_name) .input('id', update_id) .query("UPDATE 会員名簿 SET 氏名=@name WHERE 番号=@id"); display(await conn.query("SELECT 番号,氏名 FROM 会員名簿 ORDER BY 番号")); console.log("-- DELETE"); const delete_id = 210; await conn.request() .input('id', delete_id) .query("DELETE FROM 会員名簿 WHERE 番号=@id"); display(await conn.query("SELECT 番号,氏名,誕生日 FROM 会員名簿 ORDER BY 番号")); conn.close(); } catch (e) { console.log("#### Catch !! ####"); console.log(e); } console.log("#### Finish ####"); })() function display(rs) { // カラム名 const columns = Object.keys(rs.recordset.columns); const header = []; for (const col_name of columns) { header.push(` | ${col_name}`); } header.push(" |"); console.log(header.join("")); // ロー let row_count = 0; for (const row of rs.recordset) { const buff = []; for (const col_name of columns) { buff.push(` | ${row[col_name]}`); } buff.push(" |"); console.log(buff.join("")); ++row_count; } console.log(`結果 ${row_count} 行 (${rs.rowsAffected})`); }
準備は以上です。
3. 実行結果
$ node crud.js #### Start #### -- DROP & CREATE TABLE -- INSERT | 番号 | 氏名 | 誕生日 | | 110 | 岸本 龍也 | Mon Nov 06 1989 09:00:00 GMT+0900 (日本標準時) | | 210 | 荒井 伸次郎 | Wed Jan 30 1974 09:00:00 GMT+0900 (日本標準時) | | 105 | 江口 美奈 | Sat Jun 23 1979 09:00:00 GMT+0900 (日本標準時) | | 304 | 長田 隆次 | Sat May 25 1991 09:00:00 GMT+0900 (日本標準時) | | 307 | 中居 雄樹 | Wed Feb 29 1984 09:00:00 GMT+0900 (日本標準時) | 結果 5 行 (5) -- UPDATE | 番号 | 氏名 | | 105 | 江口 美奈 | | 110 | 岸本 龍也 | | 210 | 荒井 伸次郎 | | 304 | 長田 隆次 | | 307 | 中井 雄樹 | 結果 5 行 (5) -- DELETE | 番号 | 氏名 | 誕生日 | | 105 | 江口 美奈 | Sat Jun 23 1979 09:00:00 GMT+0900 (日本標準時) | | 110 | 岸本 龍也 | Mon Nov 06 1989 09:00:00 GMT+0900 (日本標準時) | | 304 | 長田 隆次 | Sat May 25 1991 09:00:00 GMT+0900 (日本標準時) | | 307 | 中井 雄樹 | Wed Feb 29 1984 09:00:00 GMT+0900 (日本標準時) | 結果 4 行 (4) #### Finish ####
4. 補足
tedious モジュールを直接扱った例が Microsoft のサイトにあります。
同じような処理ですが、Request で SQL クエリを発行した後に、request.on() でイベントを拾う必要があり、その部分がどうしても煩雑になります。
node-mssql モジュールでは async / await や method chaining が使えるので、コードの見通しが随分良くなってると思います。
接続プールについて考慮されてるもの良いですね。
テーブル名や項目名は敢えて日本語にしてますが、文字化けせずに処理できてます。ODBC ドライバより安心して使えるとは。
※ ODBC ドライバでの接続についてはこちらにまとめてあります。
- LinuxのNode.jsでnode-odbcとFreeTDSのODBCドライバからSQLServerに接続する
- LinuxのNode.jsでnode-odbcとMS版ODBCドライバからSQLServerに接続する
特に問題が見つからなければ、node-mssql モジュールでいけそうです。
5. Mac でも動作します ※追記 2021.3.24
前述のソースコードは Mac 環境の Node.js でも動作しました。
※SQLServer のサービスが別の IP アドレス なので、localhost の書き換えは必要です。
検証した Linux 環境をそのままに、Mac からアクセスできました。
- OS: macOS Mojave v10.14.6
- Node.js v14.16.0 (npm 6.14.11) ※nodebrew より
npm install mssql
を実行する際、 openssl のエラーが発生しましたが、エラーログの内容から、Homebrew が導入済みの環境であればbrew install openssl
を実行して対処できました。