Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Direct Statement Execution and Prepared Statement Execution in the PDO_SQLSRV Driver

SQL Server 2008 R2

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.

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");
?>

Community Additions

ADD
Show:
© 2015 Microsoft