MSSQL #

# Download driver dari: https://docs.microsoft.com/en-us/sql/connect/php/download-drivers-php-sql-server
# Salin php_pdo_sqlsrv_83_ts_x64.dll dan php_sqlsrv_83_ts_x64.dll ke direktori ekstensi PHP

# Tambahkan ke php.ini:
# extension=php_sqlsrv_83_ts_x64.dll
# extension=php_pdo_sqlsrv_83_ts_x64.dll

# 1. Tambahkan repository Microsoft
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list \
    | sudo tee /etc/apt/sources.list.d/mssql-release.list

# 2. Install ODBC driver
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18 unixodbc-dev

# 3. Install ekstensi PHP via PECL
sudo pecl install sqlsrv pdo_sqlsrv

# 4. Aktifkan ekstensi
echo "extension=sqlsrv.so"     | sudo tee /etc/php/8.3/mods-available/sqlsrv.ini
echo "extension=pdo_sqlsrv.so" | sudo tee /etc/php/8.3/mods-available/pdo_sqlsrv.ini
sudo phpenmod sqlsrv pdo_sqlsrv

# 5. Verifikasi
php -m | grep -i sql

<?php
declare(strict_types=1);

// SQL Server Authentication (username/password)
$dsn = 'sqlsrv:Server=localhost,1433;Database=MyDatabase;Encrypt=yes;TrustServerCertificate=yes';

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::SQLSRV_ATTR_ENCODING    => PDO::SQLSRV_ENCODING_UTF8,
    PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE => true, // angka sebagai tipe PHP, bukan string
];

try {
    $pdo = new PDO($dsn, 'sa', 'Password123!', $options);
    echo "Koneksi SQL Server berhasil\n";
} catch (PDOException $e) {
    error_log("Gagal konek SQL Server: " . $e->getMessage());
    throw new \RuntimeException("Layanan database tidak tersedia");
}

// Windows Authentication (tidak pakai username/password — pakai identitas Windows)
$dsnWindows = 'sqlsrv:Server=localhost;Database=MyDatabase;Trusted_Connection=yes';
$pdoWindows = new PDO($dsnWindows, options: $options);

// Koneksi ke instance bernama (Named Instance)
$dsnInstance = 'sqlsrv:Server=MYSERVER\SQLEXPRESS;Database=MyDatabase';

// Koneksi ke SQL Server di Azure
$dsnAzure = 'sqlsrv:Server=myserver.database.windows.net,1433;'
          . 'Database=MyDatabase;Encrypt=yes;TrustServerCertificate=no;'
          . 'Authentication=ActiveDirectoryPassword';

<?php
// SQLSRV — API prosedural
$serverName = "localhost, 1433";
$koneksiInfo = [
    "Database"                 => "MyDatabase",
    "UID"                      => "sa",
    "PWD"                      => "Password123!",
    "CharacterSet"             => "UTF-8",
    "Encrypt"                  => true,
    "TrustServerCertificate"   => true,
    "ReturnDatesAsStrings"     => false,
];

$conn = sqlsrv_connect($serverName, $koneksiInfo);

if ($conn === false) {
    $errors = sqlsrv_errors();
    foreach ($errors as $error) {
        echo "SQLSTATE: {$error['SQLSTATE']}, Kode: {$error['code']}, Pesan: {$error['message']}\n";
    }
    throw new \RuntimeException("Koneksi gagal");
}

// Selalu tutup koneksi setelah selesai
sqlsrv_close($conn);

-- MySQL: LIMIT / OFFSET
SELECT * FROM produk ORDER BY id LIMIT 10 OFFSET 20;

-- SQL Server: OFFSET ... FETCH NEXT (SQL Server 2012+)
SELECT * FROM produk
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- MySQL: AUTO_INCREMENT
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(100)
);

-- SQL Server: IDENTITY
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    nama NVARCHAR(100)
);

-- MySQL: Backtick untuk nama identifier
SELECT `nama` FROM `users`;

-- SQL Server: Bracket atau double quote
SELECT [nama] FROM [users];
SELECT "nama" FROM "users"; -- dengan SET QUOTED_IDENTIFIER ON

-- MySQL: NOW()
SELECT NOW();

-- SQL Server: GETDATE() atau SYSDATETIME()
SELECT GETDATE();        -- datetime (presisi milidetik)
SELECT SYSDATETIME();    -- datetime2 (presisi nanodetik)
SELECT GETUTCDATE();     -- UTC
SELECT SYSUTCDATETIME(); -- UTC presisi tinggi

-- MySQL: IFNULL()
SELECT IFNULL(kolom, 'default');

-- SQL Server: ISNULL() atau COALESCE()
SELECT ISNULL(kolom, 'default');
SELECT COALESCE(kolom1, kolom2, 'default'); -- standar SQL

-- MySQL: CONCAT()
SELECT CONCAT(nama, ' ', email);

-- SQL Server: + atau CONCAT()
SELECT nama + ' ' + email; -- hati-hati: NULL + 'x' = NULL
SELECT CONCAT(nama, ' ', email); -- lebih aman, NULL diabaikan

<?php
// Query dasar dengan PDO_SQLSRV
$stmt = $pdo->prepare("
    SELECT id, nama, email, created_at
    FROM users
    WHERE aktif = :aktif AND role = :role
    ORDER BY nama
    OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
");

$stmt->execute([
    ':aktif'  => 1,
    ':role'   => 'admin',
    ':offset' => 0,
    ':limit'  => 10,
]);

$users = $stmt->fetchAll();

// INSERT dan dapatkan ID yang baru dibuat
// SQL Server: SCOPE_IDENTITY() atau @@IDENTITY atau OUTPUT INSERTED.id
$stmt = $pdo->prepare("
    INSERT INTO users (nama, email, password_hash, created_at)
    OUTPUT INSERTED.id
    VALUES (:nama, :email, :hash, GETDATE())
");

$stmt->execute([
    ':nama'  => 'Budi Santoso',
    ':email' => '[email protected]',
    ':hash'  => password_hash('password123', PASSWORD_BCRYPT),
]);

// OUTPUT INSERTED.id mengembalikan baris dengan kolom id
$baru = $stmt->fetch();
$newId = $baru['id'];

// Atau gunakan lastInsertId() — bekerja untuk kasus sederhana
$pdo->prepare("INSERT INTO produk (nama, harga) VALUES (:nama, :harga)")
    ->execute([':nama' => 'Laptop', ':harga' => 15000000]);
$newId = (int) $pdo->lastInsertId();

<?php
// UNIQUEIDENTIFIER (GUID/UUID)
$stmt = $pdo->prepare("
    INSERT INTO dokumen (id, judul, konten)
    VALUES (NEWID(), :judul, :konten)
");
$stmt->execute([':judul' => 'Laporan Q1', ':konten' => 'Isi laporan...']);

// Atau generate UUID dari PHP dan kirim sebagai string
$uuid = sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
    mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff),
    mt_rand(0, 0x0fff) | 0x4000,
    mt_rand(0, 0x3fff) | 0x8000,
    mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff)
);

$stmt = $pdo->prepare("INSERT INTO dokumen (id, judul) VALUES (:id, :judul)");
$stmt->execute([':id' => $uuid, ':judul' => 'Dokumen Baru']);

// MONEY dan DECIMAL — gunakan string untuk presisi
$stmt = $pdo->prepare("
    INSERT INTO transaksi (jumlah, kurs)
    VALUES (:jumlah, :kurs)
");
$stmt->execute([
    ':jumlah' => '15000000.00', // string untuk presisi
    ':kurs'   => '15750.50',
]);

// NVARCHAR vs VARCHAR — gunakan N' prefix untuk Unicode dalam SQL Server
// Di PHP via PDO, string UTF-8 otomatis ditangani jika encoding diset dengan benar
$stmt = $pdo->prepare("
    INSERT INTO produk (nama, deskripsi)
    VALUES (:nama, :deskripsi)
");
$stmt->execute([
    ':nama'       => 'Kaos Batik Motif Parang', // UTF-8 OK
    ':deskripsi'  => 'Deskripsi dengan karakter 日本語 dan Arab مرحبا',
]);

// DATETIME2 — format ISO 8601
$stmt = $pdo->prepare("
    INSERT INTO jadwal (nama_event, mulai, selesai)
    VALUES (:nama, :mulai, :selesai)
");
$stmt->execute([
    ':nama'    => 'Rapat Bulanan',
    ':mulai'   => '2024-03-15 09:00:00',
    ':selesai' => '2024-03-15 11:00:00',
]);

// BIT — boolean SQL Server (0 atau 1)
$stmt = $pdo->prepare("UPDATE users SET aktif = :aktif WHERE id = :id");
$stmt->execute([':aktif' => 1, ':id' => 42]); // 1 untuk true, 0 untuk false

<?php
// Panggil stored procedure sederhana (hanya input parameter)
$stmt = $pdo->prepare("EXEC sp_GetUserById :id");
$stmt->execute([':id' => 42]);
$user = $stmt->fetch();

// Stored procedure dengan OUTPUT parameter — butuh SQLSRV extension
// PDO punya keterbatasan untuk output parameter SQL Server

// Dengan SQLSRV:
$params = [
    42,         // input: user_id
    ["", SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR), SQLSRV_SQLTYPE_NVARCHAR(100)], // output: nama
    ["", SQLSRV_PARAM_OUT],  // output: total_order
];

$stmt = sqlsrv_query($conn, "EXEC sp_GetUserSummary ?, ?, ?", $params);
if ($stmt === false) {
    print_r(sqlsrv_errors());
}

sqlsrv_next_result($stmt); // pindah ke result set output parameter
echo "Nama: " . $params[1][0] . "\n";
echo "Total Order: " . $params[2][0] . "\n";

// Stored procedure yang mengembalikan result set
$stmt = $pdo->prepare("EXEC sp_GetTopProducts :limit, :kategori");
$stmt->execute([':limit' => 10, ':kategori' => 'elektronik']);

do {
    while ($row = $stmt->fetch()) {
        echo "{$row['id']}: {$row['nama']} - Rp {$row['harga']}\n";
    }
} while ($stmt->nextRowset()); // SQL Server bisa kembalikan beberapa result set

<?php
// Transaksi SQL Server mirip MySQL — begin/commit/rollback
function prosesOrderSqlServer(PDO $pdo, array $orderData): int
{
    $pdo->beginTransaction();

    try {
        // INSERT order
        $stmt = $pdo->prepare("
            INSERT INTO orders (user_id, total, status, created_at)
            OUTPUT INSERTED.id
            VALUES (:user_id, :total, 'pending', GETDATE())
        ");
        $stmt->execute([':user_id' => $orderData['user_id'], ':total' => $orderData['total']]);
        $orderId = $stmt->fetch()['id'];

        // INSERT items
        $stmtItem = $pdo->prepare("
            INSERT INTO order_items (order_id, produk_id, qty, harga_satuan)
            VALUES (:order_id, :produk_id, :qty, :harga)
        ");

        foreach ($orderData['items'] as $item) {
            $stmtItem->execute([
                ':order_id'  => $orderId,
                ':produk_id' => $item['produk_id'],
                ':qty'       => $item['qty'],
                ':harga'     => $item['harga'],
            ]);

            // Kurangi stok dengan UPDLOCK untuk cegah race condition
            $stmtStok = $pdo->prepare("
                UPDATE produk WITH (UPDLOCK)
                SET stok = stok - :qty
                WHERE id = :id AND stok >= :qty
            ");
            $stmtStok->execute([':qty' => $item['qty'], ':id' => $item['produk_id']]);

            if ($stmtStok->rowCount() === 0) {
                throw new \DomainException("Stok produk #{$item['produk_id']} tidak mencukupi");
            }
        }

        $pdo->commit();
        return $orderId;

    } catch (\Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

// Savepoint (SQL Server: SAVE TRANSACTION)
$pdo->beginTransaction();
$pdo->exec("SAVE TRANSACTION sp1");

try {
    // Operasi yang mungkin gagal
    $pdo->exec("INSERT INTO audit_log ...");
    // Sukses — commit keseluruhan
    $pdo->commit();
} catch (\Exception $e) {
    // Rollback ke savepoint saja, bukan seluruh transaksi
    $pdo->exec("ROLLBACK TRANSACTION sp1");
    // Transaksi utama masih aktif
    $pdo->commit(); // commit bagian lain yang berhasil
}

<?php
class SqlServerUserRepository implements UserRepositoryInterface
{
    public function __construct(private PDO $pdo) {}

    public function findAll(int $halaman = 1, int $perHalaman = 20): array
    {
        $offset = ($halaman - 1) * $perHalaman;

        // SQL Server menggunakan OFFSET...FETCH NEXT bukan LIMIT
        $stmt = $this->pdo->prepare("
            SELECT id, nama, email, role, created_at
            FROM users
            WHERE deleted_at IS NULL
            ORDER BY created_at DESC
            OFFSET :offset ROWS FETCH NEXT :per_halaman ROWS ONLY
        ");

        $stmt->bindValue(':offset',      $offset,      PDO::PARAM_INT);
        $stmt->bindValue(':per_halaman', $perHalaman,  PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function cari(string $keyword): array
    {
        // SQL Server: LIKE case-insensitive secara default (tergantung collation)
        // Full-text search: CONTAINS atau FREETEXT untuk pencarian lebih canggih
        $stmt = $this->pdo->prepare("
            SELECT id, nama, email
            FROM users
            WHERE deleted_at IS NULL
            AND (
                nama LIKE :keyword
                OR email LIKE :keyword
            )
            ORDER BY nama
        ");

        $stmt->execute([':keyword' => "%$keyword%"]);
        return $stmt->fetchAll();
    }

    public function save(array $data): int
    {
        // OUTPUT INSERTED.id untuk mendapatkan ID baru
        $stmt = $this->pdo->prepare("
            INSERT INTO users (nama, email, password_hash, role, created_at)
            OUTPUT INSERTED.id
            VALUES (:nama, :email, :hash, :role, GETDATE())
        ");

        $stmt->execute([
            ':nama'  => $data['nama'],
            ':email' => $data['email'],
            ':hash'  => password_hash($data['password'], PASSWORD_BCRYPT),
            ':role'  => $data['role'] ?? 'user',
        ]);

        return (int) $stmt->fetch()['id'];
    }

    public function softDelete(int $id): bool
    {
        // SQL Server juga mendukung soft delete
        $stmt = $this->pdo->prepare("
            UPDATE users
            SET deleted_at = GETDATE()
            WHERE id = :id AND deleted_at IS NULL
        ");
        $stmt->execute([':id' => $id]);
        return $stmt->rowCount() > 0;
    }
}