Mssql Bundle Laravel Package

Getting Started

Minimal Setup

1. Installation Add the bundle to your composer.json:

   composer require isoft/mssql-bundle
   

   Enable it in config/bundles.php:

   Intellectsoft\MssqlBundle\IntellectsoftMssqlBundle::class => ['all' => true],
   

2. Configuration Update config/packages/intellectsoft_mssql.yaml (or create it):

   intellectsoft_mssql:
       driver: "pdo_sqlsrv"
       host: "your_host"
       port: "1433"
       dbname: "your_db"
       user: "your_user"
       password: "your_password"
       charset: "utf8"
       options:
           PDO::ATTR_PERSISTENT => true
   

3. First Use Case Inject the Doctrine\DBAL\Connection service into a controller or service:

   use Doctrine\DBAL\Connection;
   
   class MyController extends AbstractController
   {
       public function __construct(private Connection $connection)
       {
       }
   
       public function index()
       {
           $query = $this->connection->query('SELECT * FROM users');
           $users = $query->fetchAllAssociative();
           return $this->json($users);
       }
   }
   

Implementation Patterns

Doctrine ORM Integration

1. Entity Manager Configuration Extend Intellectsoft\MssqlBundle\DependencyInjection\Compiler\DoctrineCompilerPass to configure Doctrine for MSSQL:

   # config/packages/doctrine.yaml
   doctrine:
       dbal:
           driver: "pdo_sqlsrv"
           url: "%env(DATABASE_URL)%"
           server_version: "12.0"
           charset: "utf8"
           default_table_options:
               charset: utf8mb4
               collate: utf8mb4_unicode_ci
   

2. Schema Management Use doctrine:schema:update with --complete flag for MSSQL-specific schema generation:

   php bin/console doctrine:schema:update --complete --force
   

3. QueryBuilder Patterns Leverage MSSQL-specific functions in QueryBuilder:

   $queryBuilder->select('CONVERT(varchar, created_at, 120) as formatted_date');
   

Query Optimization

1. Batch Processing Use executeStatement for bulk operations:

   $this->connection->executeStatement(
       'UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE id IN (:ids)',
       ['ids' => [1, 2, 3]]
   );
   

2. Stored Procedures Call MSSQL stored procedures directly:

   $stmt = $this->connection->prepare('EXEC sp_get_users @type = :type');
   $stmt->execute(['type' => 'active']);
   $users = $stmt->fetchAllAssociative();
   

Event Listeners

1. Connection Events Subscribe to connection.connect and connection.disconnect events for logging/retries:

   $connection->getEventManager()->addListener(
       ConnectionEvents::CONNECT,
       function (ConnectEventArgs $event) {
           // Pre-connection logic
       }
   );
   

Gotchas and Tips

Common Pitfalls

1. Driver-Specific Quirks

   • Case Sensitivity: MSSQL is case-insensitive by default. Use COLLATE for case-sensitive queries:

     SELECT * FROM users WHERE username COLLATE SQL_Latin1_General_CP1_CS_AS = 'Admin'
     

   • Date Handling: Use CONVERT or FORMAT for consistent date strings:

     SELECT FORMAT(created_at, 'yyyy-MM-dd HH:mm:ss') as formatted_date
     

2. Connection Pooling

   • Enable persistent connections in config to reduce overhead:

     options:
         PDO::ATTR_PERSISTENT => true
     

   • Monitor connection leaks with doctrine:query-sql:

     php bin/console doctrine:query-sql
     

3. Character Encoding

   • Force UTF-8 encoding in queries to avoid mojibake:

     SELECT N'Your Unicode String' as text
     

   • Configure DBAL charset explicitly:

     charset: "utf8mb4"
     

Debugging Tips

1. Query Logging Enable SQL logging in config/packages/monolog.yaml:

   handlers:
       doctrine:
           type: stream
           path: "%kernel.logs_dir%/%kernel.environment%.doctrine.log"
           level: debug
           channels: ["doctrine"]
   

2. Error Handling Catch PDOException for MSSQL-specific errors:

   try {
       $this->connection->executeQuery('SELECT * FROM nonexistent_table');
   } catch (PDOException $e) {
       if ($e->getCode() === '42S02') { // Table not found
           // Handle gracefully
       }
   }
   

Extension Points

1. Custom Types Register MSSQL-specific Doctrine types (e.g., sql_variant):

   use Doctrine\DBAL\Types\Types;
   
   Types::addType('sql_variant', SqlVariantType::class);
   

2. Schema Filters Override Intellectsoft\MssqlBundle\Doctrine\MssqlSchemaManager to customize schema generation:

   class CustomSchemaManager extends MssqlSchemaManager
   {
       public function createTableSql(Table $table)
       {
           // Add MSSQL-specific constraints (e.g., FILESTREAM)
           $sql = parent::createTableSql($table);
           return $sql . " WITH (FILESTREAM_ON = N'MyContainer')";
       }
   }
   

3. Event Subscribers Extend Intellectsoft\MssqlBundle\EventListener\MssqlListener for custom logic:

   class CustomMssqlListener extends MssqlListener
   {
       public function postLoad(PostLoadEventArgs $event)
       {
           // Transform MSSQL-specific data types
       }
   }