odbc-apiクレートに乗り換えたら、MS版ODBCドライバからSQLServerへの接続が色々と捗った

macOS, Linux 環境での話です。

先月 MS 版 ODBC ドライバが更新されたのを機に、改めて Rust で ODBC ドライバを扱うクレートを探したところ、odbc-api クレートを発見しました。

https://crates.io/crates/odbc-api

後述する内容は、私なりに調べた結果をまとめたものです。

※追記: 2022-05-29　前提が抜けてました

この記事は、既存環境との互換性を考慮して Unicode データの格納先を nvarchar 項目とし、 SQL Server 2019 でデータベースファイル作成時の照合順序に _UTF8 オプションを指定しなかった場合の顛末です。

例えば、CREATE DATABASE my_test_db COLLATE Japanese_XJIS_140_CI_AS_UTF8; とすれば、varchar 項目が UTF-8 で符号化されたデータの格納先になるので、項目パラメータはそのまま渡せます。

odbc-api クレートについて

最新の MS 版 ODBC ドライバと組み合わせて、SQL Server への接続を試したところ、私が ODBC 接続で処理したい範囲については、ほぼ網羅されてました。

概ね問題なく利用できたのですが、現状 odbc-api クレートと MS 版 ODBC ドライバの組み合わせでは、いくつか注意が必要です。

プレースホルダ経由で nvarchar 項目に文字列を渡す際、Unicode 補助文字を含む文字列を UTF-8 のまま渡すと補助文字が ?? に変換される。
varchar(max), nvarchar(max), varbinary(max) はバッファリングの仕様上、そのまま値を取得できない。SQL 文でサイズを固定するか、BufferDescription を記述する必要がある。
Text 系, Blob 系は、非推奨な text, ntext, imageにマップされるので、データベースの照合順序によっては利用不可。

私が利用する範囲で困ったのはこの 3 点です。
逆に言えば、この 3 点以外はまったく困りませんでした。とても便利です。

....

上記の項番 2, 3 については、使用頻度が稀なので、制約を考慮して個別対応しました。
項番 1 については回避策があるので、検証用コードで確認してみます。

・検証用コード (main.rs)

文字列'𠮷原 🍑子' は𠮷 (つちよし: U+20BB7) と絵文字の桃 (U+1F351) を含みます。
この文字列を 4 つのパターンでテーブルに INSERT します。

use odbc_api::IntoParameter;

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let environment = odbc_api::Environment::new()?;
    let conn_str = "Driver={ODBC Driver 18 for SQL Server};Server=tcp:localhost,1833;TrustServerCertificate=yes;UID=sa;PWD=abcd1234$;Database=my_test_db";
    let conn = environment.connect_with_connection_string(conn_str)?;

    conn.execute("DROP TABLE IF EXISTS ins_test", ())?;
    conn.execute("CREATE TABLE ins_test (id int, name nvarchar(40))", ())?;

    // 文字列リテラル
    conn.execute("INSERT INTO ins_test (id, name) VALUES (111, '𠮷原 🍑子')", ())?;
    conn.execute("INSERT INTO ins_test (id, name) VALUES (222, N'𠮷原 🍑子')", ())?;

    // プレースホルダ経由
    let mut prepared = conn.prepare("INSERT INTO ins_test (id, name) VALUES (?,?)")?;
    let name = "𠮷原 🍑子"; 

    // &str    
    prepared.execute(( &333_i32, &name.into_parameter() ))?; 

    // &str -> UTF-16LE -> &[u8]
    let name_utf16 = utf16string::WString::<utf16string::LE>::from(name);
    let name_bin = name_utf16.as_bytes();
    prepared.execute(( &444_i32, &name_bin.into_parameter() ))?; 
    Ok(())
}

・実行結果の確認

コード実行後、テーブルの内容を Azure Data Studio で確認した画面がこちらです。

Unicode 文字列を含むクエリは、その文字列に N プレフィックスをつける必要があるので、N 無しは文字化け、N 有りは問題なく処理されます。
どちらも想定通りです。

一方、プレースホルダを経由する場合、nvarchar 項目 (UTF-16LE) に対し、UTF-8 の &str をそのままパラメータに渡すと、Unicode 補助文字がすべて??(0x003F, 0x003F) に置き換わります。※バイトオーダーでは (3F 00 3F 00)

これは意図した結果ではありません。

プレースホルダを経由しなければ気にする必要はないのですが、スマートに処理できないのは困ります。

....

回避策のひとつに、UTF-8 を UTF-16LE に変換して、バイトオーダーを保ったまま、そのバイト列 &[u8] をパラメータに渡す方法があるのですが、これが少々面倒です。

odbc-api クレートには widestring クレート由来のodbc_api::U16Stringが用意されていますが、これはワイド文字単位に処理するものです。
そもそも用途が異なるのでバイト列に変換するメソッドは実装されていません。

調べてみたところ、utf16string クレートが条件を満たしてました。
エンディアンを考慮し、内部バッファをバイト列に変換するメソッド as_bytes() が実装されていたので、今回はこちらを採用しました。

結果、バイト列 &[u8] は odbc-api では varbinary のパラメータとして処理され、無事にプレースホルダ経由で Unicode 文字列を渡せています。

※この仕様が変更される可能性はありますが、現在はこの方法で対処可能です。

CRUD なコードを試してみた

前述の内容を踏まえて、Unicode の検証ついでに CRUD なコードを書いてみました。

まずはcargo new等で用意したプロジェクトを修正します。
私はcargo new crud_utf16としました。

・Cargo.toml の編集

[dependencies]
odbc-api = "0.36.1"
utf16string = "0.2"
anyhow = "1"

・ソースコード: src/main.rs

接続したデータベースに「会員名簿」テーブルを作成します。
※ ODBC ドライバの接続文字列 conn_str は試す環境に合わせて変更してください。

use odbc_api::{Cursor, IntoParameter, ResultSetMetadata};

mod msodbc;

fn main() -> anyhow::Result<()> {
    println!("#### Start ####");

    let environment = odbc_api::Environment::new()?;
    //let conn_str = "Driver={ODBC Driver 17 for SQL Server};Server=tcp:localhost,1433;TrustServerCertificate=yes;UID=sa;PWD=abcd1234$;Database=my_test_db";
    let conn_str = "Driver={ODBC Driver 18 for SQL Server};Server=tcp:localhost,1833;TrustServerCertificate=yes;UID=sa;PWD=abcd1234$;Database=my_xjis140_db";
    let conn = environment.connect_with_connection_string(conn_str)?;

    println!("-- DROP & CREATE TABLE");
    conn.execute("DROP TABLE IF EXISTS 会員名簿", ())?;
    conn.execute("CREATE TABLE 会員名簿 (番号 int, 氏名 nvarchar(40), 誕生日 date)", ())?;

    println!("-- INSERT");
    let mut prepared = conn
        .prepare("INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (?,?,?)")
        .unwrap();
    let members = [
        (110, "岸本 龍也", "1989-11-06"),
        (210, "荒井 伸次郎", "1974-01-30"),
        (105, "江口 美奈", "1979-06-23"),
        (222, "渡辺 蹴斗", "2002-05-29"),
        (304, "長田 隆次", "1991-05-25"),
        (444, "𠮷原 🍑子", "2002-05-05"), // 吉,桃 ではなく つちよし,絵文字
    ];
    for (id, name, birthday) in members {
        let params = (
            &(id as i32),
            &msodbc::NVarChar::from(name).into_parameter(),
            &birthday.into_parameter(),
        );
        prepared.execute(params).unwrap();
    }
    print_resultset(&conn, "SELECT * FROM 会員名簿 ORDER BY 番号")?;

    println!("-- UPDATE");
    let mut prepared = conn
        .prepare("UPDATE 会員名簿 SET 氏名=? WHERE 番号=?")
        .unwrap();
    let new_name = msodbc::NVarChar::from("渡\u{908A}\u{E0104} 蹴斗⚽");   // 邊の異体字, 絵文字
    let params = (
        &new_name.into_parameter(), 
        &222_i32,
    );
    prepared.execute(params).unwrap();
    print_resultset(&conn, "SELECT * FROM 会員名簿 ORDER BY 番号")?;

    println!("-- DELETE");
    let mut prepared = conn.prepare("DELETE FROM 会員名簿 WHERE 番号=?").unwrap();
    let delete_id: i32 = 210;
    prepared.execute(&delete_id).unwrap();
    print_resultset(&conn, "SELECT * FROM 会員名簿 ORDER BY 番号")?;

    println!("#### Finish ####");
    Ok(())
}

// テーブル内容の表示
fn print_resultset(cn: &odbc_api::Connection, qry: &str) -> anyhow::Result<()> {
    if let Some(cursor) = cn.execute(qry, ()).unwrap() {
        // カラム名表示
        let headline: Vec<String> = cursor.column_names()?.collect::<Result<_, _>>()?;
        for col_name in headline {
            print!(" | {}", col_name);
        }
        println!(" |");
        // 取得ロー表示
        let mut buffers =
            odbc_api::buffers::TextRowSet::for_cursor(5000, &cursor, Some(4096)).unwrap();
        let mut row_set_cursor = cursor.bind_buffer(&mut buffers).unwrap();
        let mut count = 0;
        while let Some(batch) = row_set_cursor.fetch().unwrap() {
            let num_rows = batch.num_rows();
            for row_index in 0..num_rows {
                for col_index in 0..batch.num_cols() {
                    match batch.at(col_index, row_index) {
                        Some(val) => print!(" | {}", std::str::from_utf8(val).unwrap()),
                        None => print!(" |  (NULL) "),
                    };
                }
                println!(" |");
            }
            count += num_rows;
        }
        println!("結果 {} 行", count);
    }
    Ok(())
}

・ソースコード: src/msodbc.rs

プレースホルダのパラメータ作成を補助するコードを別モジュールにしました。

一時的な用途、しかも変換結果を Vec<u8> で返す関数なら数行書けば済むところ、大袈裟すぎる感じはしますが後悔はしてません。

pub struct NVarChar {
    text: utf16string::WString<utf16string::LE>,
}

impl NVarChar {
    pub fn as_bytes(&self) -> &[u8] {
        self.text.as_bytes()
    }
}

impl From<&str> for NVarChar {
    fn from(val: &str) -> Self {
        Self {
            text: utf16string::WString::from(val),
        }
    }
}

impl odbc_api::IntoParameter for NVarChar {
    type Parameter = odbc_api::parameter::VarBinaryBox;
    fn into_parameter(self) -> Self::Parameter {
        odbc_api::parameter::VarBinaryBox::from_vec(self.text.as_bytes().to_vec())
    }
}

impl<'a> odbc_api::IntoParameter for &'a NVarChar {
    type Parameter = odbc_api::parameter::VarBinarySlice<'a>;
    fn into_parameter(self) -> Self::Parameter {
        odbc_api::parameter::VarBinarySlice::new(self.text.as_bytes())
    }
}

今回の src/main.rs では impl NVarChar のas_bytes()を使用してません。
これは BufferDescription から確保したバッファで、AnyColumnViewMut::Binary()等の処理の際、バイト列が必要になるので、意図を込めて実装してあります。

utf16string クレート完全依存なら std::ops::Deref トレイトを書くと簡潔なのですが...

環境

macOS と Linux 環境で試してみました。

(1) macOS Big Sir 11.6.5 (Apple Silicon) ※ Intel も同様

rustc 1.60.0
Docker Desktop 4.7.1
Microsoft Azure SQL Edge Developer (RTM) - 15.0.2000.1559 (ARM64)
ODBC Driver for Microsoft SQL Server 18.0.1.1

ソースコードの ODBC 接続文字列は、 macOS の Docker で動作する Azure SQL Edge の TCP:1433 を localhost の TCP:1833 にマップした環境、に対してのものです。

(2) KDE neon 5.24.4 (Ubuntu 20.04 ベース)

rustc 1.62.0-nightly
Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64)
ODBC Driver for Microsoft SQL Server 17.9.1.1

実行結果

cargo run で実行した結果がこちらです。

$ cargo run
    Finished dev [unoptimized + debuginfo] target(s) in 0.07s
     Running `target/debug/crud_utf16`
#### Start ####
-- DROP & CREATE TABLE
-- INSERT
 | 番号 | 氏名 | 誕生日 |
 | 105 | 江口 美奈 | 1979-06-23 |
 | 110 | 岸本 龍也 | 1989-11-06 |
 | 210 | 荒井 伸次郎 | 1974-01-30 |
 | 222 | 渡辺 蹴斗 | 2002-05-29 |
 | 304 | 長田 隆次 | 1991-05-25 |
 | 444 | 𠮷原 🍑子 | 2002-05-05 |
結果 6 行
-- UPDATE
 | 番号 | 氏名 | 誕生日 |
 | 105 | 江口 美奈 | 1979-06-23 |
 | 110 | 岸本 龍也 | 1989-11-06 |
 | 210 | 荒井 伸次郎 | 1974-01-30 |
 | 222 | 渡邊󠄄 蹴斗⚽ | 2002-05-29 |
 | 304 | 長田 隆次 | 1991-05-25 |
 | 444 | 𠮷原 🍑子 | 2002-05-05 |
結果 6 行
-- DELETE
 | 番号 | 氏名 | 誕生日 |
 | 105 | 江口 美奈 | 1979-06-23 |
 | 110 | 岸本 龍也 | 1989-11-06 |
 | 222 | 渡邊󠄄 蹴斗⚽ | 2002-05-29 |
 | 304 | 長田 隆次 | 1991-05-25 |
 | 444 | 𠮷原 🍑子 | 2002-05-05 |
結果 5 行
#### Finish ####

無事に Unicode のサロゲート文字、異体字、絵文字を処理できてます。
macOS, Linux 環境どちらでも同じ結果になりました。

※Apple Silicon で`ld: library not found for -lodbc`が発生する場合

homebrew 経由で mssql-tools18 または msodbcsql18 をインストールすると、依存関係で unixodbc もインストールされます。

ライブラリは /opt/homebrew/lib に存在するのですが、これを見つけてくれません。
私は~/.zshrcにexport RUSTFLAGS='-L /opt/homebrew/lib'を追加して対処しました。

所感

記事の内容は FreeTDS 版 ODBC ドライバでも発生しますが、同様に対処可能です。

一応 PostgreSQL でも試してみましたが、プレースホルダの問題は発生しません。
UTF-8 対応なので、varchar 項目に対し、普通にパラメータを渡すだけです。

....

2 年程前、 ODBC ドライバ経由での接続には odbc クレートを利用しました。

私は MS 版 ODBC ドライバを利用したくて調査したのですが、苦労した割に結果は思わしくありません。当時はたまたま FreeTDS 版 ODBC ドライバで回避できたのですが、いつもこの手を使えるとは限りません。

ここ最近は async/await 対応で直接 TDS プロトコルを扱う tiberius クレートばかりで、ODBC ドライバ経由で SQL Server を利用する機会はありませんでした。

Rust での ODBC に関して半ば諦めてたところ、発見したのが odbc-api クレートです。
これ幸いと試してみたら、もう完全に乗り換えても良いくらい便利でした。

特に odbc クレートと MS 版 ODBC ドライバの組み合わせで致命的な、

そもそも setlocale() しないと日本語を扱えない。
SQLDescribeCol() の NameLengthPtr は文字数を指すのに、文字サイズを考慮せずバッファを確保するため、日本語名だと欠落or文字化けする。

に関しては、何もしなくて大丈夫です。最高です！

....

並列処理については r2d2_odbc_api クレートが利用できますが、非同期ランタイムで ODBC を扱う場合は deadpool_r2d2 クレートと連携する手段もあります。

deadpool-r2d2とr2d2_odbc_apiを組み合わせたら、非同期処理でODBCからRDBを扱えた

odbc-api クレートの更新は継続されているようなので、今後にも期待してます。

刹那（せつな）の瞬き

Willkömmen! Ich heiße Setsuna. Haben Sie etwas Zeit für mich?

odbc-apiクレートに乗り換えたら、MS版ODBCドライバからSQLServerへの接続が色々と捗った

odbc-api クレートについて

・検証用コード (main.rs)

・実行結果の確認

CRUD なコードを試してみた

・Cargo.toml の編集

・ソースコード: src/main.rs

・ソースコード: src/msodbc.rs

環境

(1) macOS Big Sir 11.6.5 (Apple Silicon) ※ Intel も同様

(2) KDE neon 5.24.4 (Ubuntu 20.04 ベース)

実行結果

※Apple Silicon で`ld: library not found for -lodbc`が発生する場合

所感

odbc-api クレートについて

・検証用コード (main.rs)

・実行結果の確認

CRUD なコードを試してみた

・Cargo.toml の編集

・ソースコード: src/main.rs

・ソースコード: src/msodbc.rs

環境

(1) macOS Big Sir 11.6.5 (Apple Silicon) ※ Intel も同様

(2) KDE neon 5.24.4 (Ubuntu 20.04 ベース)

実行結果

※Apple Silicon でld: library not found for -lodbcが発生する場合

所感

※Apple Silicon で`ld: library not found for -lodbc`が発生する場合