MSSQL #

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

# macOS with Homebrew
brew install freetds

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

# Then install the gem
gem install tiny_tds

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

require 'tiny_tds'

# SQL Server authentication (username + password)
client = TinyTds::Client.new(
  username:      "sa",
  password:      ENV["MSSQL_PASSWORD"],
  host:          "localhost",    # or the server IP
  port:          1433,           # SQL Server default port
  database:      "StoreDB",
  timeout:       30,
  connect_timeout: 10,
  encoding:      "UTF-8",
  azure:         false           # set true for Azure SQL
)

puts "Connected: #{client.active?}"
puts "SQL Server version: #{client.execute("SELECT @@VERSION").first[""]}"

# Windows authentication (only from a Windows host)
windows_client = TinyTds::Client.new(
  host:     "sql-server.domain.local",
  database: "StoreDB",
  azure:    false,
  # Without username/password = Windows Integrated Authentication
)

# Azure SQL Database
azure_client = 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
)

# Close the connection
client.close

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

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

# Use the server name from freetds.conf
client = TinyTds::Client.new(
  dataserver: "production-sql-server",
  database:   "StoreDB",
  username:   "appuser",
  password:   ENV["MSSQL_PASSWORD"]
)

require 'tiny_tds'

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

# Basic query — returns a TinyTds::Result (Enumerable)
result = client.execute("SELECT TOP 10 * FROM Products ORDER BY CreatedAt DESC")

# Iterate the result
result.each do |row|
  puts "#{row['Id']}: #{row['Name']} - Rp #{row['Price']}"
end

# Convert to an Array of Hashes
products = client.execute("SELECT Id, Name, Price FROM Products WHERE Active = 1").to_a
puts products.first.inspect
# => {"Id"=>1, "Name"=>"Laptop", "Price"=>15000000.0}

# Metadata — column names
puts result.fields.inspect   # => ["Id", "Name", "Price", "Stock", "Active"]

# A single row
one = client.execute("SELECT * FROM Products WHERE Id = 1").first

# ANTI-PATTERN: direct interpolation — SQL injection!
name_input = params[:name]
client.execute("SELECT * FROM Products WHERE Name = '#{name_input}'")

# CORRECT: manual escaping with TinyTds
def escape(client, value)
  client.escape(value.to_s)
end

safe_name = escape(client, name_input)
result = client.execute("SELECT * FROM Products WHERE Name = '#{safe_name}'")

# CORRECT: use parameterized queries via Sequel or ActiveRecord
# (covered in the following sections)

# CORRECT: sp_executesql for parameterized queries in T-SQL
# This is the safest way using tiny_tds directly
sql = <<~SQL
  EXEC sp_executesql
    N'SELECT * FROM Products WHERE Name = @Name AND Price < @Price',
    N'@Name NVARCHAR(200), @Price DECIMAL(15,2)',
    @Name = N'#{escape(client, name_input)}',
    @Price = #{max_price.to_f}
SQL
result = client.execute(sql)

class ProductRepository
  def initialize(client)
    @client = client
  end

  # CREATE — use OUTPUT INSERTED to get the new ID
  def create(name:, price:, stock:, category_id:)
    safe_name      = @client.escape(name)
    safe_category  = category_id.to_i

    sql = <<~SQL
      INSERT INTO Products (Name, Price, Stock, CategoryId, Active, CreatedAt)
      OUTPUT INSERTED.Id, INSERTED.Name, INSERTED.CreatedAt
      VALUES (N'#{safe_name}', #{price.to_f}, #{stock.to_i}, #{safe_category}, 1, GETDATE())
    SQL

    result = @client.execute(sql)
    row = result.first
    row
  end

  # READ
  def find(id)
    result = @client.execute(
      "SELECT * FROM Products WHERE Id = #{id.to_i}"
    )
    result.first
  end

  def find_all(active: true, limit: 50, offset: 0)
    active_int = active ? 1 : 0
    sql = <<~SQL
      SELECT p.*, c.Name AS CategoryName
      FROM Products p
      LEFT JOIN Categories c ON p.CategoryId = c.Id
      WHERE p.Active = #{active_int}
      ORDER BY p.CreatedAt DESC
      OFFSET #{offset.to_i} ROWS
      FETCH NEXT #{limit.to_i} ROWS ONLY
    SQL
    @client.execute(sql).to_a
  end

  # UPDATE
  def update(id, price: nil, stock: nil, name: nil)
    update_parts = []
    update_parts << "Price = #{price.to_f}" if price
    update_parts << "Stock = #{stock.to_i}" if stock
    update_parts << "Name = N'#{@client.escape(name)}'" if name
    return false if update_parts.empty?

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

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

  # DELETE (soft delete)
  def deactivate(id)
    @client.execute(
      "UPDATE Products SET Active = 0, UpdatedAt = GETDATE() WHERE Id = #{id.to_i}"
    )
    @client.affected_rows > 0
  end
end

-- LIMITING ROWS
-- MySQL:       SELECT * FROM table LIMIT 10 OFFSET 20
-- PostgreSQL:  SELECT * FROM table LIMIT 10 OFFSET 20
-- SQL Server:  SELECT * FROM table ORDER BY Id
--              OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
-- SQL Server (old): SELECT TOP 10 * FROM table  (no 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: 'hello' || ' world'  or CONCAT('hello', ' world')
-- SQL Server:  'hello' + ' world'

-- 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 = 'Products') CREATE TABLE Products (...)

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

-- STRING TYPE
-- MySQL:       VARCHAR(255)
-- SQL Server:  VARCHAR(255) or NVARCHAR(255)
--              N prefix for Unicode: N'unicode text'
--              Use NVARCHAR for text that may contain non-ASCII characters

# Pagination with OFFSET FETCH (SQL Server 2012+)
def fetch_page(page, per_page = 20, sort_column = "CreatedAt")
  offset = (page - 1) * per_page
  sql = <<~SQL
    SELECT Id, Name, Price, Stock
    FROM Products
    WHERE Active = 1
    ORDER BY #{sort_column} DESC
    OFFSET #{offset} ROWS
    FETCH NEXT #{per_page} ROWS ONLY
  SQL
  @client.execute(sql).to_a
end

# For SQL Server 2008 and below — use ROW_NUMBER()
def fetch_page_old(page, per_page = 20)
  start_offset = (page - 1) * per_page + 1
  end_offset = page * per_page
  sql = <<~SQL
    SELECT * FROM (
      SELECT *, ROW_NUMBER() OVER (ORDER BY CreatedAt DESC) AS RowNum
      FROM Products
      WHERE Active = 1
    ) AS RowNumbered
    WHERE RowNum BETWEEN #{start_offset} AND #{end_offset}
  SQL
  @client.execute(sql).to_a
end

# Calling a simple stored procedure
result = client.execute("EXEC sp_GetProductById @Id = 1")
puts result.first.inspect

# A stored procedure with many parameters
def call_sp_search_products(client, keyword:, category_id: nil, max_price: nil)
  params = ["@Keyword = N'#{client.escape(keyword)}'"]
  params << "@CategoryId = #{category_id.to_i}" if category_id
  params << "@MaxPrice = #{max_price.to_f}" if max_price

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

products = call_sp_search_products(
  client,
  keyword: "laptop",
  category_id: 2,
  max_price: 20_000_000
)

# A stored procedure with OUTPUT parameters
def get_stock_count(client, product_id)
  sql = <<~SQL
    DECLARE @StockCount INT
    EXEC sp_GetStockCount
      @ProductId = #{product_id.to_i},
      @StockCount = @StockCount OUTPUT
    SELECT @StockCount AS StockCount
  SQL
  client.execute(sql).first["StockCount"]
end

# Multiple result sets from one stored procedure
client.execute("EXEC sp_DashboardData").each_with_object([]) do |set, results|
  # Each iteration is a different result set
  results << set
end

def safe_transfer_stock(client, from_id, to_id, amount)
  client.execute("BEGIN TRANSACTION")

  begin
    # Check and lock the row with UPDLOCK + ROWLOCK
    source = client.execute(
      "SELECT Stock FROM Products WITH (UPDLOCK, ROWLOCK) WHERE Id = #{from_id.to_i}"
    ).first

    raise "Source product not found" unless source
    raise "Insufficient stock: #{source['Stock']} < #{amount}" if source["Stock"] < amount

    client.execute(
      "UPDATE Products SET Stock = Stock - #{amount.to_i} WHERE Id = #{from_id.to_i}"
    )
    client.execute(
      "UPDATE Products SET Stock = Stock + #{amount.to_i} WHERE Id = #{to_id.to_i}"
    )

    client.execute("COMMIT TRANSACTION")
    true

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

# Transaction helper
def with_transaction(client, savepoint: nil)
  if savepoint
    client.execute("SAVE TRANSACTION #{savepoint}")
  else
    client.execute("BEGIN TRANSACTION")
  end

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

require 'sequel'

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

# Basic queries
DB[:Products].all
DB[:Products].where(Active: true).all
DB[:Products].where { Price < 5_000_000 }.all
DB[:Products].order(Sequel.desc(:CreatedAt)).limit(10).all

# Pagination — Sequel automatically generates OFFSET FETCH for SQL Server
DB[:Products].where(Active: true).limit(20).offset(40).all

# INSERT with OUTPUT
new_id = DB[:Products].insert(
  Name:       "Monitor",
  Price:      3_500_000,
  Stock:      15,
  CategoryId: 2,
  Active:     true,
  CreatedAt:  Time.now
)

# UPDATE
DB[:Products].where(Id: new_id).update(Price: 3_200_000, UpdatedAt: Time.now)

# Parameterized queries — Sequel automatically escapes and parameterizes
name_input = params[:name]   # user input
DB[:Products].where(Name: name_input).all  # SAFE — no manual escaping needed

# JOIN
DB[:Products]
  .join(:Categories, Id: :CategoryId)
  .select(
    Sequel[:Products][:Name],
    Sequel[:Products][:Price],
    Sequel[:Categories][:Name].as(:CategoryName)
  )
  .where(Sequel[:Products][:Active] => true)
  .all

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

# Transactions
DB.transaction do
  DB[:Accounts].where(Id: 1).update(Balance: Sequel[:Balance] - 500_000)
  DB[:Accounts].where(Id: 2).update(Balance: Sequel[:Balance] + 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: StoreDB_Development

test:
  <<: *default
  database: StoreDB_Test

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

# app/models/product.rb
class Product < ApplicationRecord
  # SQL Server conventionally uses PascalCase table names
  self.table_name = "Products"
  self.primary_key = "Id"

  belongs_to :category, foreign_key: "CategoryId"

  # BIT columns (0/1) in SQL Server — ActiveRecord reads them as booleans
  scope :active,  -> { where(Active: true) }
  scope :newest,  -> { order(CreatedAt: :desc) }

  # Pagination — the ActiveRecord sqlserver adapter automatically generates OFFSET FETCH
  scope :page_of, ->(n, per = 20) { limit(per).offset((n - 1) * per) }

  validates :Name,  presence: true, length: { maximum: 200 }
  validates :Price, numericality: { greater_than: 0 }
end

# Usage
Product.active.newest.limit(10)
Product.active.page_of(2, 20)
Product.where("Price BETWEEN ? AND ?", 100_000, 5_000_000)
Product.includes(:category).active

# Calling a stored procedure
result = ActiveRecord::Base.connection.execute("EXEC sp_GetProductById @Id = 1")

# db/migrate/20240815000001_create_products_sqlserver.rb
class CreateProductsSqlserver < ActiveRecord::Migration[7.1]
  def change
    create_table :Products, primary_key: :Id do |t|
      t.string   :Name,       null: false, limit: 200
      t.text     :Description
      t.decimal  :Price,      null: false, precision: 15, scale: 2
      t.integer  :Stock,      null: false, default: 0
      t.boolean  :Active,     null: false, default: true   # → BIT in SQL Server
      t.integer  :CategoryId, null: false
      t.string   :SKU,        limit: 50

      # SQL Server uses datetime2 for higher precision
      t.column :CreatedAt, :datetime2, null: false, default: -> { "GETDATE()" }
      t.column :UpdatedAt, :datetime2
    end

    add_index :Products, :Active
    add_index :Products, :Price
    add_index :Products, [:CategoryId, :Active]
    add_index :Products, :SKU, unique: true

    # Foreign key
    add_foreign_key :Products, :Categories, column: :CategoryId
  end
end

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

gem install ruby-odbc

require 'odbc'

# Connect via an ODBC DSN (Data Source Name)
# The DSN is configured in /etc/odbc.ini or ~/.odbc.ini
client = ODBC.connect("MSSQLServer", "username", "password")

# Or without a DSN — direct connection string
conn_str = "Driver={ODBC Driver 17 for SQL Server};" \
           "Server=localhost,1433;" \
           "Database=StoreDB;" \
           "UID=sa;" \
           "PWD=#{ENV['MSSQL_PASSWORD']};"

client = ODBC.connect(conn_str)
stmt = client.run("SELECT TOP 10 * FROM Products")
stmt.each { |row| puts row.inspect }
stmt.drop
client.disconnect

# ANTI-PATTERN 1: String interpolation — SQL injection
client.execute("SELECT * FROM Products WHERE Name = '#{params[:name]}'")

# CORRECT: escape or use Sequel/ActiveRecord
DB[:Products].where(Name: params[:name])  # Sequel — automatically safe

# ANTI-PATTERN 2: Always SELECT * — fetching all columns including unnecessary ones
client.execute("SELECT * FROM Products")

# CORRECT: select only the columns you need
client.execute("SELECT Id, Name, Price, Stock FROM Products WHERE Active = 1")

# ANTI-PATTERN 3: Not closing the connection
client = TinyTds::Client.new(...)
# ... use the client ...
# ← forgot client.close! → connection leak

# CORRECT: always close the connection
client = TinyTds::Client.new(...)
begin
  # use the client
ensure
  client.close
end

# ANTI-PATTERN 4: VARCHAR for text that may contain non-ASCII characters
# "INSERT INTO Products (Name) VALUES ('#{name}')"
# If name = "Rina Wijaya" with special characters → could be corrupted

# CORRECT: use NVARCHAR and the N prefix for Unicode literals
# "INSERT INTO Products (Name) VALUES (N'#{client.escape(name)}')"