SQL Server 2022 : Ruby から利用する2023/11/30

	Ruby からの SQL Server 利用例です。
[1]	Ruby インストール済みを前提として例示します。追加で必要なパッケージをインストールしておきます。

# EPEL からインストール

[root@dlp ~]#

dnf -y install gcc make ruby-devel freetds-devel

[2]	基本的な利用例です。データベースや接続ユーザーは事前に適当に作成したものを使用します。

# [tiny_tds] インストール

[centos@dlp ~]$

gem install tiny_tds

# 事前準備のテスト用データベース

centos@dlp:~$

sqlcmd -S localhost -U centos -d SampleDB3 -Q 'select * from dbo.SampleTable;'

Password:
ID          First_Name           Last_Name
----------- -------------------- ---------------------
          1 CentOS               Linux
          2 Debian               Linux
          3 RedHat               Linux

(3 rows affected)

[centos@dlp ~]$

vi use_mssql.rb

require 'tiny_tds'
client = TinyTds::Client.new username: 'centos', password: 'P@ssw0rd01',
host: 'localhost', port: 1433,
database: 'SampleDB3', azure:false

# SampleTable を Select
results = client.execute("select * from SampleTable;")
results.each do |row|
puts row
end

# SampleTable に Insert
puts "\nInserting into SampleTable... \n\n"
results = client.execute("insert SampleTable (First_Name, Last_Name) values ('Windows', 'Microsoft' )")
results.each do |row|
puts row
end

# 特定の行を Update
puts "Updating 'Last_Name' for user CentOS \n\n"
results = client.execute("update SampleTable set Last_Name = 'Stream' where First_Name = 'CentOS'")
results.each do |row|
puts row
end

results = client.execute("select * from SampleTable;")
results.each do |row|
puts row
end

# 特定の行を Delete
puts "\nDeleting user 'Windows' \n"
results = client.execute("delete from SampleTable where First_Name = 'Windows';")
results.each do |row|
puts row
end

[centos@dlp ~]$

ruby use_mssql.rb

{"ID"=>1, "First_Name"=>"CentOS", "Last_Name"=>"Linux"}
{"ID"=>2, "First_Name"=>"Debian", "Last_Name"=>"Linux"}
{"ID"=>3, "First_Name"=>"RedHat", "Last_Name"=>"Linux"}

Inserting into SampleTable...

Updating 'Last_Name' for user CentOS

{"ID"=>1, "First_Name"=>"CentOS", "Last_Name"=>"Stream"}
{"ID"=>2, "First_Name"=>"Debian", "Last_Name"=>"Linux"}
{"ID"=>3, "First_Name"=>"RedHat", "Last_Name"=>"Linux"}
{"ID"=>4, "First_Name"=>"Windows", "Last_Name"=>"Microsoft"}

Deleting user 'Windows'

[centos@dlp ~]$

sqlcmd -S localhost -U centos -d SampleDB3 -Q 'select * from dbo.SampleTable;'

Password:
ID          First_Name          Last_Name
----------- ------------------- ---------------------
          1 CentOS              Stream
          2 Debian              Linux
          3 RedHat              Linux

(3 rows affected)