Direct Statement Execution and Prepared Statement Execution in the PDO_SQLSRV Driver

This topic discusses how you can use the PDO::SQLSRV_ATTR_DIRECT_QUERY attribute to specify direct statement execution instead of the default, which is prepared statement execution. When the driver prepares a statement, it can result in better performance if the statement will be executed more than once using bound parameters.

Remarks

If you want to send a Transact-SQL statement directly to the server without statement preparation by the driver, you can set the PDO::SQLSRV_ATTR_DIRECT_QUERY attribute with PDO::setAttribute (or as a driver option passed to PDO::__construct) or when you call PDO::prepare. By default, the value of PDO::SQLSRV_ATTR_DIRECT_QUERY is False (use prepared statement execution).

If you use PDO::query, you might want direct execution. Before calling PDO::query, call PDO::setAttribute and set PDO::SQLSRV_ATTR_DIRECT_QUERY to True. Each call to PDO::query can be executed with a different setting for PDO::SQLSRV_ATTR_DIRECT_QUERY.

If you use PDO::prepare and PDOStatement::execute to execute a query multiple times using bound parameters, prepared statement execution will optimize execution of the repeated query. In that situation, call PDO::prepare with PDO::SQLSRV_ATTR_DIRECT_QUERY set to False in the driver options array parameter. When necessary, you can execute prepared statements with PDO::SQLSRV_ATTR_DIRECT_QUERY set to False.

After you call PDO::prepare, the value of PDO::SQLSRV_ATTR_DIRECT_QUERY cannot change when executing the prepared query.

If a query requires the context that was set in a previous query, you should execute your queries with PDO::SQLSRV_ATTR_DIRECT_QUERY set to True. For example, if you use temporary tables in your queries, PDO::SQLSRV_ATTR_DIRECT_QUERY must be set to True.

The following sample shows that when context from a previous statement is required, you need to set PDO::SQLSRV_ATTR_DIRECT_QUERY to True. This sample uses temporary tables, which are only available to subsequent statements in your program when queries are executed directly.

<?php
   $conn = new PDO('sqlsrv:Server=(local)', '', '');
   $conn->setAttribute(constant('PDO::SQLSRV_ATTR_DIRECT_QUERY'), true);

   $stmt1 = $conn->query("DROP TABLE #php_test_table");

   $stmt2 = $conn->query("CREATE TABLE #php_test_table ([c1_int] int, [c2_int] int)");

   $v1 = 1;
   $v2 = 2;
   
   $stmt3 = $conn->prepare("INSERT INTO #php_test_table (c1_int, c2_int) VALUES (:var1, :var2)");

   if ($stmt3) {
      $stmt3->bindValue(1, $v1);
      $stmt3->bindValue(2, $v2);

      if ($stmt3->execute())
         echo "Execution succeeded\n";     
      else
         echo "Execution failed\n";
   }
   else
      var_dump($conn->errorInfo());
   
   $stmt4 = $conn->query("DROP TABLE #php_test_table");
?>

See Also

Other Resources

Programming Guide