Masa's blog(2009-08-31)

2009年08月31日 ruby(1.8.x)儂(わし)的解釈によるメモ(DBI + Microsoft SQL Server編) [長年日記]

_ ruby(1.8.x)儂(わし)的解釈によるメモ(DBI + Microsoft SQL Server編)

ruby(Linux)からMicrosoft SQL Server Express Edition(Windows)に接続してみる。色々と準備してやらないといけない...。

unixODBC-2.2.14

tar xvzf unixODBC-2.2.14.tar.gz
cd unixODBC-2.2.14
./configure
make
make install

vi /usr/local/etc/odbc.ini

[ODBC Data Sources]
freetds = FreeTDS ODBC Driver         <- たぶんコメント的な情報

[freetds]
Driver = /usr/local/lib/libtdsodbc.so
Description = Microsoft SQL Server    <- たぶんコメント的な情報
Servername = sqlserver                <- サーバ名は自由に付けてOK
Database = testdb                     <- 接続したいデータベース名 on SQLServer
Port = 1433                           <- SQLServerの(デフォルト)接続ポート

ruby-odbc-0.9997

tar xvzf ruby-odbc-0.9997.tar.gz
cd ruby-odbc-0.9997
ruby extconf.rb
make
make install

dbi-0.4.2

tar xvzf dbi-0.4.2.tar.gz
cd dbi-0.4.2
gem install dbi
gem install dbd-odbc

もし上記でdbiがうまくインストール出来なかった場合は、

ruby setup.rb config
ruby setup.rb setup
ruby setup.rb install

を試してみるとよいかも...。

freetds-0.82

オリジナルサイトのhttp://www.freetds.org/は消滅っぽいので、debianとかubuntuのサイトからソースを頂く。

tar xvzf freetds_0.82.orig.tar.gz
cd freetds-0.82
./configure
make
make install

vi /usr/local/etc/freetds.conf

末尾に以下の記述を追加する。

[sqlserver]                      <- odbc.iniのServernameの指定と合わせる
        host = 192.168.0.1       <- SQLServerのIPアドレス
        port = 1433              <- SQLServerの接続ポート
        tds version = 8.0        <- プロトコルバージョン
        charset = UTF-16LE       <- SQLServer側の文字コード
        client charset = EUC-JP  <- クライアント側の文字コード

charsetセットは未指定でも構わないかもしれない。

charset, client charsetには以下のような指定が出来る(freetds-0.82/src/tds/encodings.hより)

UTF-8
UCS-2LE
UCS-2BE
EUC-JP
ISO-2022-JP
ISO-2022-JP-1
ISO-2022-JP-2
SJIS
UCS-2
UCS-4
UCS-4BE
UCS-4LE
US-ASCII
UTF-16
UTF-16BE
UTF-16LE
UTF-32
UTF-32BE
UTF-32LE

isql

unixODBC-2.2.14にコンソールベースのクライアント(isql)が含まれている。

isql -v freetds USER USER-PASSWORD

サンプルプログラム

たぶん

ruby -> ruby dbi -> ruby dbd-odbc -> ruby-odbc -> unixODBC -> freetds -> SQL Server

のような経路でアクセスが実現されている。

#! /usr/bin/ruby
#
require 'rubygems'
require 'dbi'
#
# データベースへの接続
#
# 実際の接続先は
#
#   o /usr/local/etc/odbc.ini
#   o /usr/local/etc/freetds.conf
#
# の設定で決まる。
#
dbh = DBI.connect("DBI:ODBC:freetds", "USER", "USER-PASSWORD")
puts("データベース(testdb)に接続しました。")
#
# 自動コミットの開始
#
dbh['AutoCommit'] = true
puts("自動コミット開始しました。")
#
# トランザクション(1)
#
dbh.transaction {|dbh|
#
# テーブルの削除
#
	begin
		sql = <<SQL
			drop table member
SQL
		dbh.do(sql)
		puts "テーブルの削除に成功しました。"
	rescue => e
		puts "テーブルの削除に失敗しました(#{e.to_s})。"
	end
#
# テーブルの作成
#
	begin
		sql = <<SQL
			create table member (
				id INT PRIMARY KEY,
				name NVARCHAR(50),
				job NVARCHAR(50)
			)
SQL
		dbh.do(sql)
		puts "テーブルの作成に成功しました。"
	rescue => e
		puts "テーブルの作成に失敗しました(#{e.to_s})。"
	end
#
# わざと2重にテーブルを作成してみる
#
	begin
		sql = <<SQL
			create table member (
				id INT PRIMARY KEY,
				name NVARCHAR(50),
				job NVARCHAR(50)
			)
SQL
		dbh.do(sql)
		puts "テーブルの作成に成功しました(2)。"
	rescue => e
		puts "テーブルの作成に失敗しました(2)(#{e.to_s})。"
	end
#
# インデックスの作成
#
	sql = <<SQL
		create index i_member_name on member(name)
SQL
	dbh.do(sql)
	puts "インデックスの作成に成功しました。"
}
#
# 自動コミットの停止
#
dbh['AutoCommit'] = false
#
# トランザクション(2)
#
dbh.transaction {|dbh|
#
# データの追加
#
	sql = <<SQL
		insert into member (id, name, job) values (?, ?, ?)
SQL
	sth = dbh.prepare(sql)
	sth.execute(1, '伊藤 太郎', 'サラリーマン')
	puts "インサートに成功しました。"
	sth.execute(2, '山田 太郎', '野球選手')
	puts "インサートに成功しました。"
	sth.execute(3, '山田 花子', 'タレント')
	puts "インサートに成功しました。"
#
# わざと2重キーで登録してみる
#
	begin
		sth.execute(3, '山田 花子', 'タレント')
		puts "インサートに成功しました。"
	rescue => e
		puts "インサートに失敗しました(#{e.to_s})。"
	end
	sth.finish
}
#
# データの検索
#
sql = <<SQL
	select * from member where id >= ?
SQL
sth = dbh.prepare(sql)
sth.execute(1)
while (row = sth.fetch)
	puts "セレクトに成功しました(id=#{row['id']})(name=#{row['name']})(job=#{row['job']})"
end
sth.finish
#
# トランザクション(3)
#
dbh.transaction {|dbh|
#
# データの更新
#
	sql = <<SQL
		update member set job = ? where name = ?
SQL
	sth = dbh.prepare(sql)
	sth.execute('裁判官', '山田 太郎')
	puts ("アップデートに成功しました。")
	sth.finish
}
#
# 更新結果の確認
#
sql = <<SQL
	select * from member where name = ?
SQL
sth = dbh.prepare(sql)
sth.execute('山田 太郎')
while (row = sth.fetch)
	puts "アップデートの確認(id=#{row['id']})(name=#{row['name']})(job=#{row['job']})"
end
sth.finish
#
# トランザクション(4)
#
dbh.transaction {|dbh|
#
# データの削除
#
	sql = <<SQL
		delete from member where name like ?
SQL
	sth = dbh.prepare(sql)
	sth.execute('山田%')
	puts ("デリートに成功しました。")
	sth.finish
}
#
# 削除の確認
#
sql = <<SQL
	select * from member
SQL
sth = dbh.prepare(sql)
sth.execute()
while (row = sth.fetch)
	puts "デリートの確認(id=#{row['id']})(name=#{row['name']})(job=#{row['job']})"
end
sth.finish
#
# メタデータの取得
#
sql = <<SQL
	select * from member
SQL
sth = dbh.prepare(sql)
sth.execute()
puts "メタデータ カラム数=#{sth.column_names.size}"
print "メタデータ カラム名="
sth.column_names.each {|name|
	print "#{name}\t"
}
puts ""
#
sth.column_info.each_with_index {|info, i|
        print "項番 = #{i} / "
        print "カラム名 = #{info.name} / "
        print "精度 = #{info.precision} / "
        print "スケール = #{info.scale}\n"
}
sth.finish
#
# (手動で)のコミット(またはロールバック)の実行
#
#dbh.rollback
#puts "ロールバックしました。"
dbh.commit
puts "コミットしました。"
#
# データベースからの切断
#
dbh.disconnect
puts("データベース(testdb)から切断しました。")

実行結果

データベース(testdb)に接続しました。
自動コミット開始しました。
テーブルの削除に成功しました。
テーブルの作成に成功しました。
テーブルの作成に失敗しました(2)(S0001 (2714) [unixODBC][FreeTDS][SQL Server]There is already an object named 'member' in the database.)。
インデックスの作成に成功しました。
インサートに成功しました。
インサートに成功しました。
インサートに成功しました。
インサートに失敗しました(23000 (2627) [unixODBC][FreeTDS][SQL Server]Violation of PRIMARY KEY constraint 'PK__member__3213E83F7A672E12'. Cannot insert duplicate key in object 'dbo.member'.)。
セレクトに成功しました(id=1)(name=伊藤 太郎)(job=サラリーマン)
セレクトに成功しました(id=2)(name=山田 太郎)(job=野球選手)
セレクトに成功しました(id=3)(name=山田 花子)(job=タレント)
アップデートに成功しました。
アップデートの確認(id=2)(name=山田 太郎)(job=裁判官)
デリートに成功しました。
デリートの確認(id=1)(name=伊藤 太郎)(job=サラリーマン)
メタデータ カラム数=3
メタデータ カラム名=id	name	job
項番 = 0 / カラム名 = id / 精度 = 10 / スケール = 0
項番 = 1 / カラム名 = name / 精度 = 150 / スケール = 0
項番 = 2 / カラム名 = job / 精度 = 150 / スケール = 0
コミットしました。
データベース(testdb)から切断しました。

[ツッコミを入れる]

Masa's blog