Ruby + Sinatra + DBI + ODBC で、MS Access(accdb)へ接続する

MS Access(accdb)を使うデスクトップアプリがあるのですが、ブラウザからもaccdbへ接続することが必要になりそうだったので、調べた時のメモを残します。

環境

Windows7 x64 + MS Access(accdb)
Ruby 2.0.0p451 (RubyInstaller) + Devkit
Sinatra 1.4.5

Rubyからaccdbへの接続

良さそうな方法がなかったため、DBI + ODBC でaccdbへと接続することにしました。
DBIでの書き方は、以下のページがとても参考になりました。 Ruby DBI モジュールを使う

ODBCでの接続文字列

以下を参考に、Rubyのプログラム内で接続文字列を設定しました。
Microsoft Access accdb ODBC Driver connection strings - ConnectionStrings.com

なお、WindowsのODBCデータソースアドミニストレーターの設定から接続することもできますが、GUI上では自分のローカルやネットワークドライブに対しては設定できるものの、IPアドレスを指定することはできませんでした。

ちなみに、Windows7 x64では、32bitと64bitの2つのODBCを設定することができますが、コントロールパネル経由では64bitのみになります。そのため、32bitのODBCが必要な場合には、以下のコマンドで起動します。

%systemdrive%\Windows\SysWoW64\Odbcad32

64 ビット版の Windows で、ODBC アドミニストレーターツールが 32 ビットと 64 ビット両方のユーザー DSN を表示する - Microsoftサポート

Gemfile

以下を参考に必要なgemをGemfileに書き、Bundlerでローカルインストールしました。
Windows環境でのRuby/DBI ODBCのセットアップ(追記あり) - KONO's Diary-休むに似たり

source 'https://rubygems.org'

gem 'sinatra'
gem 'sinatra-contrib'

gem 'dbi'
gem 'dbd-odbc'
gem 'ruby-odbc'

gem 'thin'

DBI + ODBCを使ったMS Access(accdb)への接続

以下のような列を持つMS AccessのHogeテーブルがあるとします。

フィールド名	データ型
ID	オートナンバー型(主キー)
Fuga	テキスト型
Piyo	テキスト型

MS Accessとのやりとりでは、以下の点に注意します。

SQL文とプレースホルダーの値は、Shift_JIS にエンコードする
抽出した結果は、UTF-8 にエンコードする (特に日本語の文字列)

参考: RubyからODBC経由でFileMaker Proにアクセスする方法 - このブログは証明できない。

実際のコードは以下の通りです。

require 'nkf'
require 'sinatra'
require 'dbi'
require 'sinatra/reloader'

get '/:hoge' do
  
  begin
    # SQL・プレースホルダーとも、Shift_JISにエンコード
    sql = to_sjis("SELECT Piyo From Hoge WHERE Fuga = ?")
    bind = to_sjis(params[:hoge].to_s)

    dbh = create_database_handle
    results = dbh.execute(sql, bind)

    @fuga = ""
    results.each { |r| @fuga += to_utf8(r[:Piyo]) }

    erb :index

  rescue Exception => e
    logger.error to_utf8(e.to_s)
  ensure
    dbh.disconnect if dbh
  end
end


def to_sjis(str)
  str ? NKF.nkf('-s', str) : ""
end


def to_utf8(str)
  str ? NKF.nkf('-w', str) : ""
end


def create_database_handle
  # プログラムでODBCの接続文字列を指定する場合
  # 上：ローカルドライブ、下：ネットワーク越しの書き方
  # dsn = %q(DBI:ODBC:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=c://db/Sample.accdb;)
  dsn = %q(DBI:ODBC:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=//127.0.0.1/db/Sample.accdb;)
  DBI.connect(dsn)

  # ODBCデータソースアドミニストレーターで設定してある場合
  # DBI.connect('DBI:ODBC:Sample')
end