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
[ツッコミを入れる]