MSSQL #

# Download the driver from: https://docs.microsoft.com/en-us/sql/connect/php/download-drivers-php-sql-server
# Copy php_pdo_sqlsrv_83_ts_x64.dll and php_sqlsrv_83_ts_x64.dll to the PHP extension directory

# Add to php.ini:
# extension=php_sqlsrv_83_ts_x64.dll
# extension=php_pdo_sqlsrv_83_ts_x64.dll

# 1. Add the Microsoft repository
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 the ODBC driver
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18 unixodbc-dev

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

# 4. Enable the extensions
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. Verify
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, // numbers as PHP types, not strings
];

try {
    $pdo = new PDO($dsn, 'sa', 'Password123!', $options);
    echo "SQL Server connection successful\n";
} catch (PDOException $e) {
    error_log("Failed to connect to SQL Server: " . $e->getMessage());
    throw new \RuntimeException("Database service unavailable");
}

// Windows Authentication (no username/password — uses the Windows identity)
$dsnWindows = 'sqlsrv:Server=localhost;Database=MyDatabase;Trusted_Connection=yes';
$pdoWindows = new PDO($dsnWindows, options: $options);

// Connecting to a named instance
$dsnInstance = 'sqlsrv:Server=MYSERVER\SQLEXPRESS;Database=MyDatabase';

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

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

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

if ($conn === false) {
    $errors = sqlsrv_errors();
    foreach ($errors as $error) {
        echo "SQLSTATE: {$error['SQLSTATE']}, Code: {$error['code']}, Message: {$error['message']}\n";
    }
    throw new \RuntimeException("Connection failed");
}

// Always close the connection when done
sqlsrv_close($conn);

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

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

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

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

-- MySQL: Backticks for identifier names
SELECT `name` FROM `users`;

-- SQL Server: Brackets or double quotes
SELECT [name] FROM [users];
SELECT "name" FROM "users"; -- with SET QUOTED_IDENTIFIER ON

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

-- SQL Server: GETDATE() or SYSDATETIME()
SELECT GETDATE();        -- datetime (millisecond precision)
SELECT SYSDATETIME();    -- datetime2 (nanosecond precision)
SELECT GETUTCDATE();     -- UTC
SELECT SYSUTCDATETIME(); -- high-precision UTC

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

-- SQL Server: ISNULL() or COALESCE()
SELECT ISNULL(column, 'default');
SELECT COALESCE(col1, col2, 'default'); -- standard SQL

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

-- SQL Server: + or CONCAT()
SELECT name + ' ' + email; -- beware: NULL + 'x' = NULL
SELECT CONCAT(name, ' ', email); -- safer, NULLs are ignored

<?php
// Basic query with PDO_SQLSRV
$stmt = $pdo->prepare("
    SELECT id, name, email, created_at
    FROM users
    WHERE active = :active AND role = :role
    ORDER BY name
    OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY
");

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

$users = $stmt->fetchAll();

// INSERT and get the newly created ID
// SQL Server: SCOPE_IDENTITY() or @@IDENTITY or OUTPUT INSERTED.id
$stmt = $pdo->prepare("
    INSERT INTO users (name, email, password_hash, created_at)
    OUTPUT INSERTED.id
    VALUES (:name, :email, :hash, GETDATE())
");

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

// OUTPUT INSERTED.id returns a row with the id column
$new = $stmt->fetch();
$newId = $new['id'];

// Or use lastInsertId() — works for simple cases
$pdo->prepare("INSERT INTO products (name, price) VALUES (:name, :price)")
    ->execute([':name' => 'Laptop', ':price' => 15000000]);
$newId = (int) $pdo->lastInsertId();

<?php
// UNIQUEIDENTIFIER (GUID/UUID)
$stmt = $pdo->prepare("
    INSERT INTO documents (id, title, content)
    VALUES (NEWID(), :title, :content)
");
$stmt->execute([':title' => 'Q1 Report', ':content' => 'Report content...']);

// Or generate a UUID from PHP and send it as a 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 documents (id, title) VALUES (:id, :title)");
$stmt->execute([':id' => $uuid, ':title' => 'New Document']);

// MONEY and DECIMAL — use strings for precision
$stmt = $pdo->prepare("
    INSERT INTO transactions (amount, exchange_rate)
    VALUES (:amount, :rate)
");
$stmt->execute([
    ':amount' => '15000000.00', // string for precision
    ':rate'   => '15750.50',
]);

// NVARCHAR vs VARCHAR — use the N' prefix for Unicode in SQL Server
// In PHP via PDO, UTF-8 strings are handled automatically if the encoding is set correctly
$stmt = $pdo->prepare("
    INSERT INTO products (name, description)
    VALUES (:name, :description)
");
$stmt->execute([
    ':name'        => 'Parang Pattern Batik Shirt', // UTF-8 OK
    ':description' => 'Description with Japanese 日本語 and Arabic مرحبا characters',
]);

// DATETIME2 — ISO 8601 format
$stmt = $pdo->prepare("
    INSERT INTO schedule (event_name, start_time, end_time)
    VALUES (:name, :start, :end)
");
$stmt->execute([
    ':name'  => 'Monthly Meeting',
    ':start' => '2024-03-15 09:00:00',
    ':end'   => '2024-03-15 11:00:00',
]);

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

<?php
// Call a simple stored procedure (input parameters only)
$stmt = $pdo->prepare("EXEC sp_GetUserById :id");
$stmt->execute([':id' => 42]);
$user = $stmt->fetch();

// Stored procedure with OUTPUT parameters — needs the SQLSRV extension
// PDO has limitations for SQL Server output parameters

// With SQLSRV:
$params = [
    42,         // input: user_id
    ["", SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR), SQLSRV_SQLTYPE_NVARCHAR(100)], // output: name
    ["", SQLSRV_PARAM_OUT],  // output: total_orders
];

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

sqlsrv_next_result($stmt); // move to the output parameter result set
echo "Name: " . $params[1][0] . "\n";
echo "Total Orders: " . $params[2][0] . "\n";

// Stored procedure returning a result set
$stmt = $pdo->prepare("EXEC sp_GetTopProducts :limit, :category");
$stmt->execute([':limit' => 10, ':category' => 'electronics']);

do {
    while ($row = $stmt->fetch()) {
        echo "{$row['id']}: {$row['name']} - Rp {$row['price']}\n";
    }
} while ($stmt->nextRowset()); // SQL Server can return multiple result sets

<?php
// SQL Server transactions are similar to MySQL — begin/commit/rollback
function processOrderSqlServer(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, product_id, qty, unit_price)
            VALUES (:order_id, :product_id, :qty, :price)
        ");

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

            // Reduce stock with UPDLOCK to prevent race conditions
            $stmtStock = $pdo->prepare("
                UPDATE products WITH (UPDLOCK)
                SET stock = stock - :qty
                WHERE id = :id AND stock >= :qty
            ");
            $stmtStock->execute([':qty' => $item['qty'], ':id' => $item['product_id']]);

            if ($stmtStock->rowCount() === 0) {
                throw new \DomainException("Insufficient stock for product #{$item['product_id']}");
            }
        }

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

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

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

try {
    // An operation that might fail
    $pdo->exec("INSERT INTO audit_log ...");
    // Success — commit everything
    $pdo->commit();
} catch (\Exception $e) {
    // Roll back only to the savepoint, not the whole transaction
    $pdo->exec("ROLLBACK TRANSACTION sp1");
    // The main transaction is still active
    $pdo->commit(); // commit the parts that succeeded
}

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

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

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

        $stmt->bindValue(':offset',   $offset,   PDO::PARAM_INT);
        $stmt->bindValue(':per_page', $perPage,  PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function search(string $keyword): array
    {
        // SQL Server: LIKE is case-insensitive by default (depends on collation)
        // Full-text search: CONTAINS or FREETEXT for more advanced searching
        $stmt = $this->pdo->prepare("
            SELECT id, name, email
            FROM users
            WHERE deleted_at IS NULL
            AND (
                name LIKE :keyword
                OR email LIKE :keyword
            )
            ORDER BY name
        ");

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

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

        $stmt->execute([
            ':name'  => $data['name'],
            ':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 also supports soft deletes
        $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;
    }
}