prepareStatement example

// @todo Remove the __construct in Drupal 11.     // @see https://www.drupal.org/project/drupal/issues/3256524     parent::__construct($connection, $table, $options);
    unset($this->queryOptions['return']);
  }

  public function execute() {
    if (!$this->preExecute()) {
      return NULL;
    }

    $stmt = $this->connection->prepareStatement((string) $this, $this->queryOptions);

    // Fetch the list of blobs and sequences used on that table.     $table_information = $this->connection->schema()->queryTableInformation($this->table);

    $max_placeholder = 0;
    $blobs = [];
    $blob_count = 0;
    foreach ($this->insertValues as $insert_values) {
      foreach ($this->insertFields as $idx => $field) {
        if (isset($table_information->blob_fields[$field]) && $insert_values[$idx] !== NULL) {
          $blobs[$blob_count] = fopen('php://memory', 'a');
          

  public function execute() {
    $stmt = $this->connection->prepareStatement((string) $this, $this->queryOptions, TRUE);
    try {
      $stmt->execute([], $this->queryOptions);
      return $stmt->rowCount();
    }
    catch (\Exception $e) {
      $this->connection->exceptionHandler()->handleExecutionException($e, $stmt, [], $this->queryOptions);
    }

    return NULL;
  }

  

  public function execute() {
    $values = [];
    if (count($this->condition)) {
      $this->condition->compile($this->connection, $this);
      $values = $this->condition->arguments();
    }

    $stmt = $this->connection->prepareStatement((string) $this, $this->queryOptions, TRUE);
    try {
      $stmt->execute($values, $this->queryOptions);
      return $stmt->rowCount();
    }
    catch (\Exception $e) {
      $this->connection->exceptionHandler()->handleExecutionException($e, $stmt, $values, $this->queryOptions);
    }
  }

  /** * Implements PHP magic __toString method to convert the query to a string. * * @return string * The prepared statement. */

$values = [];
      foreach ($this->insertValues as $insert_values) {
        foreach ($insert_values as $value) {
          $values[':db_insert_placeholder_' . $max_placeholder++] = $value;
        }
      }
    }
    else {
      $values = $this->fromQuery->getArguments();
    }

    $stmt = $this->connection->prepareStatement((string) $this, $this->queryOptions);
    try {
      $stmt->execute($values, $this->queryOptions);
      $last_insert_id = $this->connection->lastInsertId();
    }
    catch (\Exception $e) {
      $this->connection->exceptionHandler()->handleExecutionException($e, $stmt, $values, $this->queryOptions);
    }

    // Re-initialize the values array so that we can re-use this query.     $this->insertValues = [];

    

$num_records_before = $this->connection->select('test')->countQuery()->execute()->fetchField();

    $sql = "INSERT INTO {test} ([name], [age]) VALUES (:name, :age)";
    $args = [
      ':name' => 'Larry',
      ':age' => '30',
    ];
    $options = [
      'allow_square_brackets' => FALSE,
    ];

    $stmt = $this->connection->prepareStatement($sql, $options);
    $this->assertInstanceOf(StatementInterface::class, $stmt);
    $this->assertTrue($stmt->execute($args, $options));

    // We should be able to specify values in any order if named.     $args = [
      ':age' => '31',
      ':name' => 'Curly',
    ];
    $this->assertTrue($stmt->execute($args, $options));

    $num_records_after = $this->connection->select('test')->countQuery()->execute()->fetchField();
    

    // If we're selecting from a SelectQuery, finish building the query and     // pass it back, as any remaining options are irrelevant.     if (!empty($this->fromQuery)) {
      $sql = (string) $this;
      // The SelectQuery may contain arguments, load and pass them through.       return $this->connection->query($sql, $this->fromQuery->getArguments(), $this->queryOptions);
    }

    $last_insert_id = 0;
    $stmt = $this->connection->prepareStatement((string) $this, $this->queryOptions);
    try {
      // Per https://en.wikipedia.org/wiki/Insert_%28SQL%29#Multirow_inserts,       // not all databases implement SQL-92's standard syntax for multi-row       // inserts. Therefore, in the degenerate case, execute a separate query       // for each row, all within a single transaction for atomicity and       // performance.       $transaction = $this->connection->startTransaction();
      foreach ($this->insertValues as $insert_values) {
        $stmt->execute($insert_values, $this->queryOptions);
        $last_insert_id = $this->connection->lastInsertId();
      }
    }

    // If we're selecting from a SelectQuery, finish building the query and     // pass it back, as any remaining options are irrelevant.     if (!empty($this->fromQuery)) {
      // The SelectQuery may contain arguments, load and pass them through.       return $this->connection->query((string) $this, $this->fromQuery->getArguments(), $this->queryOptions);
    }

    // If there are any fields in the query, execute normal INSERT statements.     if (count($this->insertFields)) {
      $stmt = $this->connection->prepareStatement((string) $this, $this->queryOptions);

      if (count($this->insertValues) === 1) {
        // Inserting a single row does not require a transaction to be atomic,         // and executes faster without a transaction wrapper.         $insert_values = $this->insertValues[0];
        try {
          $stmt->execute($insert_values, $this->queryOptions);
        }
        catch (\Exception $e) {
          $this->connection->exceptionHandler()->handleExecutionException($e, $stmt, $insert_values, $this->queryOptions);
        }
      }

    // placeholders will all match up properly.     $max_placeholder = 0;
    foreach ($fields as $value) {
      $update_values[':db_update_placeholder_' . ($max_placeholder++)] = $value;
    }

    if (count($this->condition)) {
      $this->condition->compile($this->connection, $this);
      $update_values = array_merge($update_values, $this->condition->arguments());
    }

    $stmt = $this->connection->prepareStatement((string) $this, $this->queryOptions, TRUE);
    try {
      $stmt->execute($update_values, $this->queryOptions);
      return $stmt->rowCount();
    }
    catch (\Exception $e) {
      $this->connection->exceptionHandler()->handleExecutionException($e, $stmt, $update_values, $this->queryOptions);
    }
  }

  /** * Implements PHP magic __toString method to convert the query to a string. * * @return string * The prepared statement. */

// Use default values if not already set.     $options += $this->defaultOptions();

    if (isset($options['return'])) {
      @trigger_error('Passing "return" option to ' . __METHOD__ . '() is deprecated in drupal:9.4.0 and is removed in drupal:11.0.0. For data manipulation operations, use dynamic queries instead. See https://www.drupal.org/node/3185520', E_USER_DEPRECATED);
    }

    assert(!isset($options['target']), 'Passing "target" option to query() has no effect. See https://www.drupal.org/node/2993033');

    $this->expandArguments($query, $args);
    $stmt = $this->prepareStatement($query, $options);

    try {
      $stmt->execute($args, $options);

      // Depending on the type of query we may need to return a different value.       // See DatabaseConnection::defaultOptions() for a description of each       // value.       // @todo the block below is deprecated and as of Drupal 11 will be       // removed, query() will only return a StatementInterface object.       // @see https://www.drupal.org/project/drupal/issues/3256524       switch ($options['return'] ?? Database::RETURN_STATEMENT) {
        

class DatabaseExceptionWrapperTest extends DriverSpecificKernelTestBase {

  /** * Tests Connection::prepareStatement exception on execution. */
  public function testPrepareStatementFailOnExecution() {
    $this->expectException(\PDOException::class);
    $stmt = $this->connection->prepareStatement('bananas', []);
    $stmt->execute();
  }

}

  public function testMultipleStatementsQuery() {
    $this->expectException(\InvalidArgumentException::class);
    Database::getConnection('default', 'default')->query('SELECT * FROM {test}; SELECT * FROM {test_people}');
  }

  /** * Ensure that you cannot prepare multiple statements. */
  public function testMultipleStatements() {
    $this->expectException(\InvalidArgumentException::class);
    Database::getConnection('default', 'default')->prepareStatement('SELECT * FROM {test}; SELECT * FROM {test_people}', []);
  }

  /** * Tests that the method ::condition() returns a Condition object. */
  public function testCondition() {
    $connection = Database::getConnection('default', 'default');
    $namespace = (new \ReflectionObject($connection))->getNamespaceName() . "\\Condition";
    if (!class_exists($namespace)) {
      $namespace = Condition::class;
    }
    

unset($this->queryOptions['return']);
  }

  /** * {@inheritdoc} */
  public function execute() {
    if (!$this->preExecute()) {
      return NULL;
    }

    $stmt = $this->connection->prepareStatement((string) $this, $this->queryOptions, TRUE);

    // Fetch the list of blobs and sequences used on that table.     $table_information = $this->connection->schema()->queryTableInformation($this->table);

    $max_placeholder = 0;
    $blobs = [];
    $blob_count = 0;
    foreach ($this->insertValues as $insert_values) {
      foreach ($this->insertFields as $idx => $field) {
        if (isset($table_information->blob_fields[$field]) && $insert_values[$idx] !== NULL) {
          $blobs[$blob_count] = fopen('php://memory', 'a');
          

  public function testPrepareStatementFailOnPreparation() {
    $connection_info = Database::getConnectionInfo('default');
    $connection_info['default']['pdo'][\PDO::ATTR_EMULATE_PREPARES] = FALSE;
    Database::addConnectionInfo('default', 'foo', $connection_info['default']);
    $foo_connection = Database::getConnection('foo', 'default');
    $this->expectException(DatabaseExceptionWrapper::class);
    $stmt = $foo_connection->prepareStatement('bananas', []);
  }

  /** * Tests Connection::prepareStatement exception on execution. */
  public function testPrepareStatementFailOnExecution() {
    $this->expectException(\PDOException::class);
    $stmt = $this->connection->prepareStatement('bananas', []);
    $stmt->execute();
  }

}

// $this->exceptionHandler()->handleExecutionException()       // requires a $statement argument, so we cannot use that.       throw new DatabaseExceptionWrapper($e->getMessage(), 0, $e);
    }

    // We can safely use literal queries here instead of the slower query     // builder because if a given database breaks here then it can simply     // override nextId. However, this is unlikely as we deal with short strings     // and integers and no known databases require special handling for those     // simple cases. If another transaction wants to write the same row, it will     // wait until this transaction commits.     $stmt = $this->prepareStatement('UPDATE {sequences} SET [value] = GREATEST([value], :existing_id) + 1', [], TRUE);
    $args = [':existing_id' => $existing_id];
    try {
      $stmt->execute($args);
    }
    catch (\Exception $e) {
      $this->exceptionHandler()->handleExecutionException($e, $stmt, $args, []);
    }
    if ($stmt->rowCount() === 0) {
      $this->query('INSERT INTO {sequences} ([value]) VALUES (:existing_id + 1)', $args);
    }
    // The transaction gets committed when the transaction object gets destroyed

class DatabaseExceptionWrapperTest extends DriverSpecificKernelTestBase {

  /** * Tests Connection::prepareStatement exception on execution. */
  public function testPrepareStatementFailOnExecution() {
    $this->expectException(\PDOException::class);
    $stmt = $this->connection->prepareStatement('bananas', []);
    $stmt->execute();
  }

}