Ruby + Sequelを使って、MS Accessの選択クエリからデータを取得する

accdb_qeury.rb

require 'sequel'

# MS Accessの選択クエリ(AccdbQuery)のSQLビュー
# SELECT ID, 名前 AS Name, [ｺﾒﾝﾄ] AS Comment
# FROM 日本語ｻﾝﾌﾟﾙ;

def write(msg)
  puts "\n"
  puts '-' * 20
  puts msg
  puts '-' * 20
end


# Sequel + ADO による MS Accessへの接続
ACCDB_PATH = '//127.0.0.1/db/Sample.accdb'
OLEDB_PROVIDER = 'Microsoft.ACE.OLEDB.12.0'
ODBC_DRIVER = '{Microsoft Access Driver (*.mdb, *.accdb)}'

db = Sequel.ado(conn_string: "Provider=#{OLEDB_PROVIDER};Data Source=#{ACCDB_PATH}")
ds = db[:AccdbQuery]


# SELECT
write('SELECT')
r = ds.select(:Name, :Comment).where(ID: :$n).prepare(:first).call(n: 1)
p r             #=> {:Name=>"ほげ", :Comment=>"ﾎｹﾞ"}
p r[:Name]      #=> "ほげ"
p r[:Comment]   #=> "ﾎｹﾞ"
p ds.all
#=> [{:ID=>1, :Name=>"ほげ", :Comment=>"ﾎｹﾞ"}, {:ID=>2, :Name=>"ふが", :Comment=>"フガ"}]


# @@IDENTITYの動作を確認するため、一度データを投入・削除しておく
write('INSERT - Preparation')
2.times { |i| ds.insert(Name: i.to_s) }
p ds.all
#=> [{:ID=>1, :Name=>"ほげ", :Comment=>"ﾎｹﾞ"}, {:ID=>2, :Name=>"ふが", :Comment=>"フガ"}, {:ID=>3, :Name=>"0", :Comment=>nil}, {:ID=>4, :Name=>"1", :Comment=>nil}]

ds.where{id > 2}.delete
# ds.where(Sequel.expr(:ID) > 2).delete   # これでもOK
p ds.all
#=> [{:ID=>1, :Name=>"ほげ", :Comment=>"ﾎｹﾞ"}, {:ID=>2, :Name=>"ふが", :Comment=>"フガ"}]


# INSERT
ds.insert(Name: 'Piyo', Comment: 'insert')

# @@IDENTITYを使って、INSERT時に採番されたオートナンバー型のID列を取得する
id = db['SELECT @@IDENTITY as ID From AccdbQuery'].first[:ID]
puts "\n@@IDENTITY: #{id}"  #=> @@IDENTITY: 5

write('INSERT')
p ds.all
#=> [{:ID=>1, :Name=>"ほげ", :Comment=>"ﾎｹﾞ"}, {:ID=>2, :Name=>"ふが", :Comment=>"フガ"}, {:ID=>5, :Name=>"Piyo", :Comment=>"insert"}]


# UPDATE
ds.where(ID: id).update(Name: 'ぴよ', Comment: 'Update')
write('UPDATE')
p ds.all
#=> [{:ID=>1, :Name=>"ほげ", :Comment=>"ﾎｹﾞ"}, {:ID=>2, :Name=>"ふが", :Comment=>"フガ"}, {:ID=>5, :Name=>"ぴよ", :Comment=>"Update"}]


# DELETE
ds.where(ID: id).delete
write('DELETE')
p ds.all
#=> [{:ID=>1, :Name=>"ほげ", :Comment=>"ﾎｹﾞ"}, {:ID=>2, :Name=>"ふが", :Comment=>"フガ"}]