MSSQL #

# Ubuntu / Debian — install FreeTDS terlebih dahulu
sudo apt install freetds-dev freetds-bin

# macOS dengan Homebrew
brew install freetds

# CentOS / RHEL / Fedora
sudo dnf install freetds-devel

# Kemudian install gem
gem install tiny_tds

# Gemfile
gem 'tiny_tds', '~> 2.1'

require 'tiny_tds'

# Autentikasi SQL Server (username + password)
klien = TinyTds::Client.new(
  username:      "sa",
  password:      ENV["MSSQL_PASSWORD"],
  host:          "localhost",    # atau IP server
  port:          1433,           # port default SQL Server
  database:      "TokoDB",
  timeout:       30,
  connect_timeout: 10,
  encoding:      "UTF-8",
  azure:         false           # set true jika Azure SQL
)

puts "Terhubung: #{klien.active?}"
puts "SQL Server versi: #{klien.execute("SELECT @@VERSION").first[""]}"

# Autentikasi Windows (hanya dari Windows host)
klien_windows = TinyTds::Client.new(
  host:     "sql-server.domain.local",
  database: "TokoDB",
  azure:    false,
  # Tanpa username/password = Windows Integrated Authentication
)

# Azure SQL Database
klien_azure = TinyTds::Client.new(
  username: "#{ENV['AZURE_DB_USER']}@#{ENV['AZURE_SERVER_NAME']}",
  password: ENV["AZURE_DB_PASSWORD"],
  host:     "#{ENV['AZURE_SERVER_NAME']}.database.windows.net",
  port:     1433,
  database: ENV["AZURE_DB_NAME"],
  azure:    true,
  ssl:      :require
)

# Tutup koneksi
klien.close

# /etc/freetds/freetds.conf
[sql-server-produksi]
  host = 192.168.1.100
  port = 1433
  tds version = 7.4
  client charset = UTF-8
  text size = 64512

[sql-server-dev]
  host = localhost
  port = 1433
  tds version = 7.4

# Gunakan nama server dari freetds.conf
klien = TinyTds::Client.new(
  dataserver: "sql-server-produksi",
  database:   "TokoDB",
  username:   "appuser",
  password:   ENV["MSSQL_PASSWORD"]
)

require 'tiny_tds'

klien = TinyTds::Client.new(
  username: "sa", password: ENV["MSSQL_PASSWORD"],
  host: "localhost", database: "TokoDB"
)

# Query dasar — mengembalikan TinyTds::Result (Enumerable)
hasil = klien.execute("SELECT TOP 10 * FROM Produk ORDER BY CreatedAt DESC")

# Iterasi hasil
hasil.each do |baris|
  puts "#{baris['Id']}: #{baris['Nama']} - Rp #{baris['Harga']}"
end

# Konversi ke Array of Hash
produk = klien.execute("SELECT Id, Nama, Harga FROM Produk WHERE Aktif = 1").to_a
puts produk.first.inspect
# => {"Id"=>1, "Nama"=>"Laptop", "Harga"=>15000000.0}

# Metadata — nama kolom
puts hasil.fields.inspect   # => ["Id", "Nama", "Harga", "Stok", "Aktif"]

# Satu baris
satu = klien.execute("SELECT * FROM Produk WHERE Id = 1").first

# ANTI-PATTERN: interpolasi langsung — SQL injection!
nama_input = params[:nama]
klien.execute("SELECT * FROM Produk WHERE Nama = '#{nama_input}'")

# BENAR: escape manual dengan TinyTds
def escape(klien, nilai)
  klien.escape(nilai.to_s)
end

nama_aman = escape(klien, nama_input)
hasil = klien.execute("SELECT * FROM Produk WHERE Nama = '#{nama_aman}'")

# BENAR: gunakan parameterized query via Sequel atau ActiveRecord
# (dibahas di bagian selanjutnya)

# BENAR: sp_executesql untuk parameterized query di T-SQL
# Ini cara paling aman menggunakan tiny_tds langsung
sql = <<~SQL
  EXEC sp_executesql
    N'SELECT * FROM Produk WHERE Nama = @Nama AND Harga < @Harga',
    N'@Nama NVARCHAR(200), @Harga DECIMAL(15,2)',
    @Nama = N'#{escape(klien, nama_input)}',
    @Harga = #{harga_maks.to_f}
SQL
hasil = klien.execute(sql)

class ProdukRepository
  def initialize(klien)
    @klien = klien
  end

  # CREATE — gunakan OUTPUT INSERTED untuk dapat ID baru
  def buat(nama:, harga:, stok:, kategori_id:)
    nama_aman    = @klien.escape(nama)
    kategori_aman = kategori_id.to_i

    sql = <<~SQL
      INSERT INTO Produk (Nama, Harga, Stok, KategoriId, Aktif, CreatedAt)
      OUTPUT INSERTED.Id, INSERTED.Nama, INSERTED.CreatedAt
      VALUES (N'#{nama_aman}', #{harga.to_f}, #{stok.to_i}, #{kategori_aman}, 1, GETDATE())
    SQL

    hasil = @klien.execute(sql)
    baris = hasil.first
    baris
  end

  # READ
  def temukan(id)
    hasil = @klien.execute(
      "SELECT * FROM Produk WHERE Id = #{id.to_i}"
    )
    hasil.first
  end

  def cari_semua(aktif: true, limit: 50, offset: 0)
    aktif_int = aktif ? 1 : 0
    sql = <<~SQL
      SELECT p.*, k.Nama AS KategoriNama
      FROM Produk p
      LEFT JOIN Kategori k ON p.KategoriId = k.Id
      WHERE p.Aktif = #{aktif_int}
      ORDER BY p.CreatedAt DESC
      OFFSET #{offset.to_i} ROWS
      FETCH NEXT #{limit.to_i} ROWS ONLY
    SQL
    @klien.execute(sql).to_a
  end

  # UPDATE
  def perbarui(id, harga: nil, stok: nil, nama: nil)
    update_parts = []
    update_parts << "Harga = #{harga.to_f}" if harga
    update_parts << "Stok = #{stok.to_i}"   if stok
    update_parts << "Nama = N'#{@klien.escape(nama)}'" if nama
    return false if update_parts.empty?

    sql = <<~SQL
      UPDATE Produk
      SET #{update_parts.join(', ')}, UpdatedAt = GETDATE()
      WHERE Id = #{id.to_i}
    SQL

    @klien.execute(sql)
    @klien.affected_rows > 0
  end

  # DELETE (soft delete)
  def nonaktifkan(id)
    @klien.execute(
      "UPDATE Produk SET Aktif = 0, UpdatedAt = GETDATE() WHERE Id = #{id.to_i}"
    )
    @klien.affected_rows > 0
  end
end

-- LIMITING ROWS
-- MySQL:       SELECT * FROM tabel LIMIT 10 OFFSET 20
-- PostgreSQL:  SELECT * FROM tabel LIMIT 10 OFFSET 20
-- SQL Server:  SELECT * FROM tabel ORDER BY Id
--              OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
-- SQL Server (lama): SELECT TOP 10 * FROM tabel  (tanpa OFFSET)

-- AUTO INCREMENT
-- MySQL:       id INT AUTO_INCREMENT PRIMARY KEY
-- PostgreSQL:  id SERIAL PRIMARY KEY
-- SQL Server:  Id INT IDENTITY(1,1) PRIMARY KEY

-- STRING CONCATENATION
-- MySQL/PostgreSQL: 'halo' || ' dunia'  atau CONCAT('halo', ' dunia')
-- SQL Server:  'halo' + ' dunia'

-- CURRENT TIMESTAMP
-- MySQL:       NOW(), CURRENT_TIMESTAMP
-- PostgreSQL:  NOW(), CURRENT_TIMESTAMP
-- SQL Server:  GETDATE(), GETUTCDATE(), SYSDATETIME()

-- IF NOT EXISTS
-- MySQL:       CREATE TABLE IF NOT EXISTS ...
-- SQL Server:  IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
--              WHERE TABLE_NAME = 'Produk') CREATE TABLE Produk (...)

-- BOOLEAN
-- MySQL:       TINYINT(1) atau BOOLEAN
-- PostgreSQL:  BOOLEAN
-- SQL Server:  BIT (0 atau 1, bukan TRUE/FALSE)

-- STRING TYPE
-- MySQL:       VARCHAR(255)
-- SQL Server:  VARCHAR(255) atau NVARCHAR(255)
--              N prefix untuk Unicode: N'teks unicode'
--              Gunakan NVARCHAR untuk teks yang bisa berisi karakter non-ASCII

# Pagination dengan OFFSET FETCH (SQL Server 2012+)
def ambil_halaman(halaman, per_halaman = 20, urut_kolom = "CreatedAt")
  offset = (halaman - 1) * per_halaman
  sql = <<~SQL
    SELECT Id, Nama, Harga, Stok
    FROM Produk
    WHERE Aktif = 1
    ORDER BY #{urut_kolom} DESC
    OFFSET #{offset} ROWS
    FETCH NEXT #{per_halaman} ROWS ONLY
  SQL
  @klien.execute(sql).to_a
end

# Untuk SQL Server 2008 ke bawah — gunakan ROW_NUMBER()
def ambil_halaman_lama(halaman, per_halaman = 20)
  offset_awal = (halaman - 1) * per_halaman + 1
  offset_akhir = halaman * per_halaman
  sql = <<~SQL
    SELECT * FROM (
      SELECT *, ROW_NUMBER() OVER (ORDER BY CreatedAt DESC) AS RowNum
      FROM Produk
      WHERE Aktif = 1
    ) AS BerpenomoranBaris
    WHERE RowNum BETWEEN #{offset_awal} AND #{offset_akhir}
  SQL
  @klien.execute(sql).to_a
end

# Memanggil stored procedure sederhana
hasil = klien.execute("EXEC sp_GetProdukById @Id = 1")
puts hasil.first.inspect

# Stored procedure dengan banyak parameter
def panggil_sp_cari_produk(klien, kata_kunci:, kategori_id: nil, maks_harga: nil)
  params = ["@KataKunci = N'#{klien.escape(kata_kunci)}'"]
  params << "@KategoriId = #{kategori_id.to_i}" if kategori_id
  params << "@MaksHarga = #{maks_harga.to_f}" if maks_harga

  sql = "EXEC sp_CariProduk #{params.join(', ')}"
  klien.execute(sql).to_a
end

produk = panggil_sp_cari_produk(
  klien,
  kata_kunci: "laptop",
  kategori_id: 2,
  maks_harga: 20_000_000
)

# Stored procedure dengan OUTPUT parameter
def dapatkan_jumlah_stok(klien, produk_id)
  sql = <<~SQL
    DECLARE @JumlahStok INT
    EXEC sp_GetJumlahStok
      @ProdukId = #{produk_id.to_i},
      @JumlahStok = @JumlahStok OUTPUT
    SELECT @JumlahStok AS JumlahStok
  SQL
  klien.execute(sql).first["JumlahStok"]
end

# Multiple result sets dari satu stored procedure
klien.execute("EXEC sp_DashboardData").each_with_object([]) do |set, hasil|
  # Setiap iterasi adalah result set berbeda
  hasil << set
end

def transfer_stok_aman(klien, dari_id, ke_id, jumlah)
  klien.execute("BEGIN TRANSACTION")

  begin
    # Cek dan lock baris dengan UPDLOCK + ROWLOCK
    sumber = klien.execute(
      "SELECT Stok FROM Produk WITH (UPDLOCK, ROWLOCK) WHERE Id = #{dari_id.to_i}"
    ).first

    raise "Produk sumber tidak ditemukan" unless sumber
    raise "Stok tidak mencukupi: #{sumber['Stok']} < #{jumlah}" if sumber["Stok"] < jumlah

    klien.execute(
      "UPDATE Produk SET Stok = Stok - #{jumlah.to_i} WHERE Id = #{dari_id.to_i}"
    )
    klien.execute(
      "UPDATE Produk SET Stok = Stok + #{jumlah.to_i} WHERE Id = #{ke_id.to_i}"
    )

    klien.execute("COMMIT TRANSACTION")
    true

  rescue => e
    klien.execute("ROLLBACK TRANSACTION")
    raise e
  end
end

# Helper transaksi
def dengan_transaksi(klien, savepoint: nil)
  if savepoint
    klien.execute("SAVE TRANSACTION #{savepoint}")
  else
    klien.execute("BEGIN TRANSACTION")
  end

  begin
    hasil = yield
    klien.execute("COMMIT TRANSACTION") unless savepoint
    hasil
  rescue => e
    if savepoint
      klien.execute("ROLLBACK TRANSACTION #{savepoint}")
    else
      klien.execute("ROLLBACK TRANSACTION")
    end
    raise e
  end
end

require 'sequel'

# Koneksi Sequel via tiny_tds
DB = Sequel.connect(
  adapter:   "tinytds",
  host:      "localhost",
  port:      1433,
  database:  "TokoDB",
  username:  "sa",
  password:  ENV["MSSQL_PASSWORD"],
  timeout:   30
)

# Query dasar
DB[:Produk].all
DB[:Produk].where(Aktif: true).all
DB[:Produk].where { Harga < 5_000_000 }.all
DB[:Produk].order(Sequel.desc(:CreatedAt)).limit(10).all

# Pagination — Sequel otomatis generate OFFSET FETCH untuk SQL Server
DB[:Produk].where(Aktif: true).limit(20).offset(40).all

# INSERT dengan OUTPUT
id_baru = DB[:Produk].insert(
  Nama:       "Monitor",
  Harga:      3_500_000,
  Stok:       15,
  KategoriId: 2,
  Aktif:      true,
  CreatedAt:  Time.now
)

# UPDATE
DB[:Produk].where(Id: id_baru).update(Harga: 3_200_000, UpdatedAt: Time.now)

# Parameterized query — Sequel otomatis escape dan parameterize
nama_input = params[:nama]   # input dari user
DB[:Produk].where(Nama: nama_input).all  # AMAN — tidak perlu escape manual

# JOIN
DB[:Produk]
  .join(:Kategori, Id: :KategoriId)
  .select(
    Sequel[:Produk][:Nama],
    Sequel[:Produk][:Harga],
    Sequel[:Kategori][:Nama].as(:KategoriNama)
  )
  .where(Sequel[:Produk][:Aktif] => true)
  .all

# Stored procedure via raw SQL
DB.fetch("EXEC sp_CariProduk @KataKunci = ?", "%laptop%").all

# Transaksi
DB.transaction do
  DB[:Rekening].where(Id: 1).update(Saldo: Sequel[:Saldo] - 500_000)
  DB[:Rekening].where(Id: 2).update(Saldo: Sequel[:Saldo] + 500_000)
end

gem install activerecord-sqlserver-adapter

# Gemfile
gem 'activerecord-sqlserver-adapter', '~> 7.1'
gem 'tiny_tds',                       '~> 2.1'

# config/database.yml
default: &default
  adapter: sqlserver
  encoding: utf8
  username: <%= ENV["MSSQL_USERNAME"] || "sa" %>
  password: <%= ENV["MSSQL_PASSWORD"] %>
  host:     <%= ENV["MSSQL_HOST"] || "localhost" %>
  port:     1433
  timeout:  30
  pool:     <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: TokoDB_Development

test:
  <<: *default
  database: TokoDB_Test

production:
  <<: *default
  database: <%= ENV["MSSQL_DATABASE"] %>
  host:     <%= ENV["MSSQL_HOST"] %>

# app/models/produk.rb
class Produk < ApplicationRecord
  # SQL Server menggunakan nama tabel PascalCase secara konvensional
  self.table_name = "Produk"
  self.primary_key = "Id"

  belongs_to :kategori, foreign_key: "KategoriId"

  # Kolom bit (0/1) di SQL Server — ActiveRecord membacanya sebagai boolean
  scope :aktif,  -> { where(Aktif: true) }
  scope :terbaru, -> { order(CreatedAt: :desc) }

  # Pagination — ActiveRecord sqlserver adapter otomatis generate OFFSET FETCH
  scope :halaman, ->(n, per = 20) { limit(per).offset((n - 1) * per) }

  validates :Nama,  presence: true, length: { maximum: 200 }
  validates :Harga, numericality: { greater_than: 0 }
end

# Penggunaan
Produk.aktif.terbaru.limit(10)
Produk.aktif.halaman(2, 20)
Produk.where("Harga BETWEEN ? AND ?", 100_000, 5_000_000)
Produk.includes(:kategori).aktif

# Memanggil stored procedure
hasil = ActiveRecord::Base.connection.execute("EXEC sp_GetProdukById @Id = 1")

# db/migrate/20240815000001_buat_tabel_produk_sqlserver.rb
class BuatTabelProdukSqlserver < ActiveRecord::Migration[7.1]
  def change
    create_table :Produk, primary_key: :Id do |t|
      t.string   :Nama,       null: false, limit: 200
      t.text     :Deskripsi
      t.decimal  :Harga,      null: false, precision: 15, scale: 2
      t.integer  :Stok,       null: false, default: 0
      t.boolean  :Aktif,      null: false, default: true   # → BIT di SQL Server
      t.integer  :KategoriId, null: false
      t.string   :SKU,        limit: 50

      # SQL Server menggunakan datetime2 untuk presisi lebih tinggi
      t.column :CreatedAt, :datetime2, null: false, default: -> { "GETDATE()" }
      t.column :UpdatedAt, :datetime2
    end

    add_index :Produk, :Aktif
    add_index :Produk, :Harga
    add_index :Produk, [:KategoriId, :Aktif]
    add_index :Produk, :SKU, unique: true

    # Foreign key
    add_foreign_key :Produk, :Kategori, column: :KategoriId
  end
end

# Ubuntu — install driver Microsoft ODBC
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo apt install msodbcsql17 unixodbc-dev

gem install ruby-odbc

require 'odbc'

# Koneksi via ODBC DSN (Data Source Name)
# DSN dikonfigurasi di /etc/odbc.ini atau ~/.odbc.ini
klien = ODBC.connect("MSSQLServer", "username", "password")

# Atau tanpa DSN — connection string langsung
conn_str = "Driver={ODBC Driver 17 for SQL Server};" \
           "Server=localhost,1433;" \
           "Database=TokoDB;" \
           "UID=sa;" \
           "PWD=#{ENV['MSSQL_PASSWORD']};"

klien = ODBC.connect(conn_str)
stmt = klien.run("SELECT TOP 10 * FROM Produk")
stmt.each { |baris| puts baris.inspect }
stmt.drop
klien.disconnect

# ANTI-PATTERN 1: Interpolasi string — SQL injection
klien.execute("SELECT * FROM Produk WHERE Nama = '#{params[:nama]}'")

# BENAR: escape atau gunakan Sequel/ActiveRecord
DB[:Produk].where(Nama: params[:nama])  # Sequel — aman otomatis

# ANTI-PATTERN 2: SELECT * selalu — ambil semua kolom termasuk yang tidak perlu
klien.execute("SELECT * FROM Produk")

# BENAR: pilih kolom yang dibutuhkan saja
klien.execute("SELECT Id, Nama, Harga, Stok FROM Produk WHERE Aktif = 1")

# ANTI-PATTERN 3: Tidak menutup koneksi
klien = TinyTds::Client.new(...)
# ... gunakan klien ...
# ← lupa klien.close! → connection leak

# BENAR: selalu tutup koneksi
klien = TinyTds::Client.new(...)
begin
  # gunakan klien
ensure
  klien.close
end

# ANTI-PATTERN 4: VARCHAR untuk teks yang mungkin berisi karakter non-ASCII
# "INSERT INTO Produk (Nama) VALUES ('#{nama}')"
# Jika nama = "Rina Wijaya" dengan karakter khusus → bisa corrupt

# BENAR: gunakan NVARCHAR dan prefix N untuk Unicode literal
# "INSERT INTO Produk (Nama) VALUES (N'#{klien.escape(nama)}')"