(8.0.22)
接続元：CentOS7
接続先：CentOS7
https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0073
https://qiita.com/toshiro3/items/b65b2ad744d8f3ecc734
https://github.com/brianmario/mysql2
※ドライバレベルでのトランザクション制御はできない模様
-- 1. 環境設定
-- 1.1. ruby2.6のインストール
yum update
yum -y install git bzip2 gcc gcc-c++ openssl-devel readline-devel zlib-devel
yum -y install epel-release
yum -y install nodejs
git clone https://github.com/rbenv/rbenv.git /usr/local/rbenv
git clone https://github.com/rbenv/ruby-build.git /usr/local/rbenv/plugins/ruby-build
vim /etc/profile.d/rbenv.sh
export RBENV_ROOT=/usr/local/rbenv
export PATH=${RBENV_ROOT}/bin:$PATH
eval "$(rbenv init --no-rehash -)"
source /etc/profile.d/rbenv.sh
visudo
Defaults env_keep += "RBENV_ROOT"
Defaults secure_path = /sbin:/bin:/usr/sbin:/usr/bin:/usr/local/rbenv/bin:/usr/local/rbenv/shims
rbenv install -l
rbenv install 2.6.6
rbenv global 2.6.6
rbenv rehash
ruby --version
-- 1.2. mysql2のインストール
yum install mysql-devel
gem install mysql2
-- 1.3. 接続先DBユーザの認証プラグインの変更
select host, user, plugin from mysql.user;
alter user 'test'@'%' identified with mysql_native_password by 'test';
select host, user, plugin from mysql.user;
-- 2. テストテーブル作成
drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 timestamp);
insert into tab1 values(1,'A',current_timestamp());
insert into tab1 values(2,'B',current_timestamp());
select * from tab1;
-- 3. DB接続確認
vim a.rb
---
begin
require 'mysql2'
conn = Mysql2::Client.new(host: "mmm066",username: "test",password: 'test',database: 'test',port: 3306)
puts "---- select(バインド変数有) ---- "
statement = conn.prepare('select * from tab1 where col1 >= ?')
results = statement.execute(1)
results.each do |res|
puts res['col1'].to_s + "\t" + res['col2'].to_s + "\t" + res['col3'].to_s
end
puts "---- "
results = statement.execute(2)
results.each do |res|
puts res['col1'].to_s + "\t" + res['col2'].to_s + "\t" + res['col3'].to_s
end
puts "---- select(バインド変数なし) ---- "
results = conn.query("select * from tab1 where col1 >= 1")
results.each do |res|
puts res['col1'].to_s + "\t" + res['col2'].to_s + "\t" + res['col3'].to_s
end
puts "---- insert(バインド変数有) ---- "
results = conn.query("start transaction")
statement = conn.prepare("insert into tab1(col1,col2,col3) values (?,?,?)")
results = statement.execute(100,"X","2021/01/01")
results = conn.query("select row_count() as ret")
results.each do |res|
puts "ret=" + res['ret'].to_s
end
results = conn.query("commit")
puts "---- insert(バインド変数なし) ---- "
results = conn.query("start transaction")
results = conn.query("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')")
results = conn.query("select row_count() as ret")
results.each do |res|
puts "ret=" + res['ret'].to_s
end
results = conn.query("commit")
ensure
conn.close if conn
end
(19c)
接続元：CentOS7
接続先：CentOS7
http://se-bikou.blogspot.com/2011/04/rubyoracle.html
https://www.nslabs.jp/rails_oracle.rhtml
https://www.oracle.com/webfolder/technetwork/jp/obe/db/11g/r2/prod/appdev/opensrclang/rubyrails/rubyrails.htm
-- 1. 環境設定
-- 1.1. Oracle Instant Clientのダウンロードとインストール
rpm -ivh oracle-instantclient19.9-basic-19.9.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.9-devel-19.9.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.9-sqlplus-19.9.0.0.0-1.x86_64.rpm
-- 1.2. ruby-oci8のインストール
yum install ruby ruby-devel gcc
gem install ruby-oci8
-- 2. テストテーブル作成
drop table tab1 purge;
create table tab1(col1 int,col2 varchar2(10),col3 timestamp);
insert into tab1 values(1,'A',sysdate);
insert into tab1 values(2,'B',sysdate);
commit;
select * from tab1;
-- 3. DB接続確認
vim a.rb
---
require 'oci8'
begin
conn = OCI8.new('test', 'test', 'mmm065:1521/pdb1.example.com')
puts "---- select(バインド変数有) ---- "
cur = conn.parse("select * from tab1 where col1 >= :col1")
cur.bind_param(":col1",1)
cur.exec()
while row = cur.fetch
puts row.join("\t")
end
puts "---- "
cur.bind_param(":col1",2)
cur.exec()
while row = cur.fetch
puts row.join("\t")
end
cur.close()
puts "---- select(バインド変数なし) ---- "
cur = conn.exec("select * from tab1 where col1 >= 1")
while row = cur.fetch
puts row.join("\t")
end
cur.close()
puts "---- insert(バインド変数有) ---- "
cur = conn.parse("insert into tab1(col1,col2,col3) values(:col1,:col2,:col3)")
cur.bind_param(":col1",100)
cur.bind_param(":col2","X")
cur.bind_param(":col3","2021/01/01")
cur.exec()
conn.commit()
puts "ret=#{cur.row_count}"
cur.close()
puts "---- insert(バインド変数なし) ---- "
cur = conn.parse("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')")
cur.exec()
conn.commit()
puts "ret=#{cur.row_count}"
cur.close()
ensure
conn.logoff() if conn
end
---
ruby a.rb
(13)
接続元：CentOS7
接続先：CentOS8
http://iteba.hateblo.jp/entry/2014/07/12/224544
https://github.com/ged/ruby-pg
https://rubygems.org/gems/pg/versions/1.0.0-x64-mingw32
https://qiita.com/suzuki-navi/items/7879b708b4f38b003444
https://www.ruby-forum.com/t/updating-row-with-pg-success-or-failure/161248/3
-- 1. 環境設定
-- 1.1. ruby2.6のインストール
yum update
yum -y install git bzip2 gcc gcc-c++ openssl-devel readline-devel zlib-devel
yum -y install epel-release
yum -y install nodejs
git clone https://github.com/rbenv/rbenv.git /usr/local/rbenv
git clone https://github.com/rbenv/ruby-build.git /usr/local/rbenv/plugins/ruby-build
vim /etc/profile.d/rbenv.sh
export RBENV_ROOT=/usr/local/rbenv
export PATH=${RBENV_ROOT}/bin:$PATH
eval "$(rbenv init --no-rehash -)"
source /etc/profile.d/rbenv.sh
visudo
Defaults env_keep += "RBENV_ROOT"
Defaults secure_path = /sbin:/bin:/usr/sbin:/usr/bin:/usr/local/rbenv/bin:/usr/local/rbenv/shims
rbenv install -l
rbenv install 2.6.6
rbenv global 2.6.6
rbenv rehash
ruby --version
-- 1.2. ruby-pgのインストール
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install epel-release centos-release-scl
yum install postgresql13-devel
export PATH=$PATH:/usr/pgsql-13/bin
gem install pg
-- 2. テストテーブル作成
drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 timestamp);
insert into tab1 values(1,'A',clock_timestamp());
insert into tab1 values(2,'B',clock_timestamp());
select * from tab1;
-- 3. DB接続確認
vim a.rb
---
begin
require 'pg'
conn = PG.connect(host: "mmm067", user: "postgres", password: "postgres", dbname: "test", port: "5432")
puts "---- select(バインド変数有) ---- "
conn.prepare('stmt1', "SELECT * from tab1 where col1 >= $1" )
results = conn.exec_prepared('stmt1',[1])
results.each{|res|
puts res["col1"].to_s + "\t" + res["col2"].to_s + "\t" + res["col3"].to_s
}
puts "---- "
results = conn.exec_prepared('stmt1',[2])
results.each do |res|
puts res["col1"].to_s + "\t" + res["col2"].to_s + "\t" + res["col3"].to_s
end
puts "---- select(バインド変数なし) ---- "
results = conn.exec("SELECT * from tab1 where col1 >= 1")
results.each do |res|
puts res["col1"].to_s + "\t" + res["col2"].to_s + "\t" + res["col3"].to_s
end
puts "---- insert(バインド変数有) ---- "
conn.prepare( 'stmt2', "INSERT INTO tab1(col1,col2,col3) VALUES ($1,$2,$3)" )
begin
conn.transaction do |conn|
ins = conn.exec_prepared('stmt2',[100,"X",'2021/01/01'])
puts "ret=#{ins.cmd_tuples()}"
end
rescue
puts "Error occured. rollback done."
end
puts "---- insert(バインド変数なし) ---- "
begin
conn.transaction do |conn|
ins = conn.exec("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')")
puts "ret=#{ins.cmd_tuples()}"
end
rescue
puts "Error occured. rollback done."
end
ensure
conn.close if conn
end
---
ruby a.rb
(2019)
接続元：CentOS7
接続先：Windows Server 2016
https://docs.microsoft.com/ja-jp/sql/connect/ruby/step-3-proof-of-concept-connecting-to-sql-using-ruby?view=sql-server-ver15
https://stackoverflow.com/questions/14512378/parameterizing-sql-queries-in-ruby-tinytds
https://qiita.com/weal/items/5a5a88a8606042206c0c
http://sequel.jeremyevans.net/documentation.html
http://sequel.jeremyevans.net/rdoc/files/doc/sql_rdoc.html
-- 1. 環境設定
-- 1.1. ruby2.6のインストール
yum update -y
yum -y install git bzip2 gcc gcc-c++ openssl-devel readline-devel zlib-devel
yum -y install epel-release
yum -y install nodejs
git clone https://github.com/rbenv/rbenv.git /usr/local/rbenv
git clone https://github.com/rbenv/ruby-build.git /usr/local/rbenv/plugins/ruby-build
vim /etc/profile.d/rbenv.sh
export RBENV_ROOT=/usr/local/rbenv
export PATH=${RBENV_ROOT}/bin:$PATH
eval "$(rbenv init --no-rehash -)"
source /etc/profile.d/rbenv.sh
visudo
Defaults env_keep += "RBENV_ROOT"
Defaults secure_path = /sbin:/bin:/usr/sbin:/usr/bin:/usr/local/rbenv/bin:/usr/local/rbenv/shims
rbenv install -l
rbenv install 2.6.6
rbenv global 2.6.6
rbenv rehash
ruby --version
-- 1.2. FreeTDSのインストール
yum install freetds freetds-devel
-- 1.3. TinyTDSのインストール
gem install tiny_tds
-- 1.4. Sequelのインストール
gem install sequel
-- 2. テストテーブル作成
drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 datetime2);
insert into tab1 values(1,'A',getdate());
insert into tab1 values(2,'B',getdate());
select * from tab1;
-- 3. DB接続確認
vim a.rb
---
require 'sequel'
conn = Sequel.connect(
adapter: 'tinytds',
host: "mmm064",
database: "test",
user: "sa",
password: "Aaa!1234"
)
puts "---- select(バインド変数有) ---- "
conn["select * from tab1 where col1 >= ?", 1].each do |row|
puts row
end
puts "---- "
conn["select * from tab1 where col1 >= ?", 2].each do |row|
puts row
end
puts "---- select(バインド変数なし) ---- "
conn["select * from tab1 where col1 >= 1"].each do |row|
puts row
end
puts "---- insert(バインド変数有) ---- "
conn.run("begin transaction")
insert_ds = conn["insert into tab1(col1,col2,col3) VALUES (?,?,?)", 100,"X","2021/01/01"]
insert_ds.insert
conn["select @@rowcount ret"].each do |row|
puts row
end
conn.run("commit")
puts "---- insert(バインド変数なし) ---- "
conn.run("begin transaction")
insert_ds = conn["insert into tab1 (col1,col2,col3) VALUES (200,'Y','2022/01/01')"]
insert_ds.insert
conn["select @@rowcount ret"].each do |row|
puts row
end
conn.run("commit")
---
ruby a.rb
