Masa's blog(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)から切断しました。

[ツッコミを入れる]

2010年08月31日 dbi-0.4.3.gem and sqlite3-ruby-1.2.5 works right, but sqlite3-ruby-1.3.1 dosen't

_ dbi-0.4.3.gem and sqlite3-ruby-1.2.5 works right, but sqlite3-ruby-1.3.1 dosen't

# gem install sqlite3-ruby-1.2.5.gem
# gem install dbi-0.4.3.gem
# gem install dbd-sqlite3-1.2.5.gem
# gem list

*** LOCAL GEMS ***

dbd-sqlite3 (1.2.5)
dbi (0.4.3)
sqlite3-ruby (1.2.5)

な環境だと

sql = <<SQL
    insert into member (id, name, job) values (?, ?, ?)
SQL
sth = dbh.prepare(sql)
sth.execute(1, '伊藤 太郎', 'サラリーマン')
sth.execute(2, '山田 太郎', '野球選手')
sth.finish

が問題なく実行できるが

# gem install sqlite3-ruby-1.3.1.gem
# gem install dbi-0.4.3.gem
# gem install dbd-sqlite3-1.2.5.gem
# gem list

*** LOCAL GEMS ***

dbd-sqlite3 (1.2.5)
dbi (0.4.3)
sqlite3-ruby (1.3.1)

な環境だと2発目の

sth.execute(2, '山田 太郎', '野球選手')

で以下のような

/usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.3.1/lib/sqlite3/statement.rb:41:in `bind_param': library routine called out of sequence (SQLite3::MisuseException)
        from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.3.1/lib/sqlite3/statement.rb:41:in `bind_params'
        from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.3.1/lib/sqlite3/statement.rb:37:in `each'
        from /usr/local/lib/ruby/gems/1.8/gems/sqlite3-ruby-1.3.1/lib/sqlite3/statement.rb:37:in `bind_params'
        from /usr/local/lib/ruby/gems/1.8/gems/dbd-sqlite3-1.2.5/lib/dbd/sqlite3/statement.rb:71:in `bind_params'
        from /usr/local/lib/ruby/site_ruby/1.8/dbi/handles/statement.rb:115:in `execute'
        from ./test2.rb:86
        from /usr/local/lib/ruby/site_ruby/1.8/dbi/handles/database.rb:209:in `transaction'
        from ./test2.rb:76

例外が発生する。

コーディングを以下のように

sql = <<SQL
    insert into member (id, name, job) values (?, ?, ?)
SQL
sth = dbh.prepare(sql)
sth.execute(1, '伊藤 太郎', 'サラリーマン')
sth.finish
sth = dbh.prepare(sql)
sth.execute(2, '山田 太郎', '野球選手')
sth.finish

executeするたびにステートメントハンドルの廃棄/生成を行えば問題無く動くようだが、 dbi-0.4.3.gemはまだsqlite3-ruby-1.3.1に対応していないという事なのだろう。

[ツッコミを入れる]

2012年08月31日 sample for setting to do NAPT(IP MASQUERADE) by iptables

_ iptables's chain

[Recieved packet] -> [PREROUTING] -> [FORWARD] -> [POSTROUTING] -> [Sending packet]

                          |                             ^
                          V                             |

                       [INPUT]                       [OUTPUT]

                          |                             ^
                          V                             |

                     [ (recieve)    Local process     (send)  ]

_ iptables's command sequence

eth0 : Global network
eth1 : Private network

/usr/sbin/iptables -F        # Flush `filter table'
/usr/sbin/iptables -t nat -F # Flush `nat table'
#
/usr/sbin/iptables -P OUTPUT  ACCEPT # Set policy for OUTPUT
/usr/sbin/iptables -P FORWARD DROP   # Set policy for FORWARD
/usr/sbin/iptables -P INPUT   DROP   # Set policy for INPUT
#
/usr/sbin/iptables -A FORWARD -p udp -i eth1 -o eth0 -s 192.168.0.?? -j ACCEPT # Accept UDP from eth1 to eth0
/usr/sbin/iptables -A FORWARD -p tcp -i eth1 -o eth0 -s 192.168.0.?? -j ACCEPT # Accept TCP from eth1 to eth0
#
/usr/sbin/iptables -A FORWARD -i eth0 -o eth1 -d 192.168.0.??  -m state --state ESTABLISHED,RELATED -j ACCEPT # Accept all established packet from eth0 to eth1
#
/usr/sbin/iptables -t nat -A POSTROUTING -o eth0 -s 192.168.0.?? -j MASQUERADE # NAPT for out going from eth0
#
/bin/echo 1 >/proc/sys/net/ipv4/ip_forward # Enable IP forward
#
/usr/sbin/iptables -A INPUT -p all -i eth0 -j DROP # Drop all unknown packet

[ツッコミを入れる]

Masa's blog

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

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

unixODBC-2.2.14

ruby-odbc-0.9997

dbi-0.4.2

freetds-0.82

isql

サンプルプログラム

実行結果

2010年08月31日 dbi-0.4.3.gem and sqlite3-ruby-1.2.5 works right, but sqlite3-ruby-1.3.1 dosen't

_ dbi-0.4.3.gem and sqlite3-ruby-1.2.5 works right, but sqlite3-ruby-1.3.1 dosen't

2012年08月31日 sample for setting to do NAPT(IP MASQUERADE) by iptables

_ iptables's chain

_ iptables's command sequence

最近の日記

最近のツッコミ

最近のTrackBack


		2009年 8月
日	月	火	水	木	金	土
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31