MSSQLDriver Class

Overview

The Kotchasan\Connection\MSSQLDriver class is a specialized driver for Microsoft SQL Server databases. It supports SQL Server features such as encryption, index management, and database-specific SQL functions.

Installation and Requirements

Requirements

• PHP 7.4 or higher
• PDO SQL Server extension (pdo_sqlsrv)
• Microsoft SQL Server 2012+

Connection Configuration

Basic Configuration

use Kotchasan\Connection\MSSQLDriver;

$config = [
    'host' => 'localhost',
    'port' => 1433,
    'database' => 'myapp',
    'username' => 'sa',
    'password' => 'password',
    'charset' => 'utf8'
];

$driver = new MSSQLDriver();
$connected = $driver->connect($config);

Advanced Configuration

$config = [
    'host' => 'sqlserver.example.com',
    'port' => 1433,
    'database' => 'production_db',
    'username' => 'app_user',
    'password' => 'secure_pass',
    'appname' => 'MyKotchasanApp',
    'timeout' => 30,
    'encrypt' => true,
    'trust_server_certificate' => true,
    'pooling' => true
];

SQL Server-Specific Features

Table Optimization

// SQL Server uses ALTER INDEX REBUILD
$success = $driver->optimizeTable('users');
// Executes: ALTER INDEX ALL ON [users] REBUILD;

SQL Functions for SQL Server

// Date functions
$hourFunc = $driver->formatSqlFunction('HOUR', ['column' => 'created_at'], 'hour_created');
// Result: DATEPART(HOUR, [created_at]) AS [hour_created]

// String concatenation
$concatFunc = $driver->formatSqlFunction('CONCAT', [
    'fields' => ['first_name', 'last_name'],
    'separator' => ' '
], 'full_name');
// Result: ([first_name] + ' ' + [last_name]) AS [full_name]

// Group concatenation
$groupConcatFunc = $driver->formatSqlFunction('GROUP_CONCAT', [
    'column' => 'tag_name',
    'separator' => ', ',
    'order' => ['tag_name']
], 'tags');
// Result: STRING_AGG([tag_name], ', ') WITHIN GROUP (ORDER BY [tag_name]) AS [tags]

// Random
$randFunc = $driver->formatSqlFunction('RAND', [], 'random_value');
// Result: NEWID() AS [random_value]

// Handle NULL values
$ifnullFunc = $driver->formatSqlFunction('IFNULL', [
    'column1' => 'nickname',
    'column2' => 'first_name'
], 'display_name');
// Result: ISNULL([nickname], [first_name]) AS [display_name]

Usage Examples

Working with Identity Columns

// INSERT and get Identity value
$stmt = $driver->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(['John Doe', 'john@example.com']);
$newId = $driver->lastInsertId(); // No sequence name needed;

Advanced Transaction Management

try {
    $driver->beginTransaction();

    // Use savepoint
    $driver->prepare("SAVE TRANSACTION sp1")->execute();

    // Perform operations
    $stmt1 = $driver->prepare("INSERT INTO orders (user_id, total) VALUES (?, ?)");
    $stmt1->execute([1, 100.00]);

    // If there's an issue, rollback to savepoint
    // $driver->prepare("ROLLBACK TRANSACTION sp1")->execute();

    $driver->commit();

} catch (\Exception $e) {
    $driver->rollback();
    throw $e;
}

Error Handling

try {
    $driver->connect($config);
} catch (\PDOException $e) {
    $message = $e->getMessage();

    if (strpos($message, 'Login failed') !== false) {
        echo "Login failed";
    } elseif (strpos($message, 'Cannot open database') !== false) {
        echo "Cannot open database";
    } else {
        echo "SQL Server error: " . $message;
    }
}

Best Practices

Best Practices

1. Use Encryption: Enable encryption for security
2. Manage Connection Pooling: Use connection pooling
3. Use Indexes: Create appropriate indexes
4. Set Timeouts: Configure appropriate timeout values

Considerations

1. Identifier Quoting: Use square brackets [table_name]
2. Data Types: Be aware of data type differences between SQL Server and other databases.
3. Case Sensitivity: Depends on collation settings.
4. SQL Injection: Never concatenate user input directly into SQL strings; always use parameter binding.

Related Classes

• Connection - Basic connection class
• DriverInterface - Driver interface
• MySQLDriver - MySQL driver