Using PHP PDO With Apache Ignite | Ignite Documentation

Ignite Summit 2024 — Call For Speakers Now Open — Learn more

Edit

Using PHP PDO With Apache Ignite

Overview

PHP provides a lightweight, consistent interface for accessing databases named PHP Data Objects - PDO. This extension works with several database-specific PDO drivers. One of them is PDO_ODBC, which allows connecting to any database that provides its own ODBC driver implementation.

With the usage of Apache Ignite’s ODBC driver, it’s possible to connect to an Apache Ignite cluster from a PHP application accessing and modifying data that is stored there.

Setting Up ODBC Driver

Apache Ignite conforms to ODBC protocol and has its own ODBC driver that is delivered along with other functionality. This is the driver that will be used by PHP PDO framework going forward in order to connect to an Apache Ignite cluster.

Refer to the Ignite ODBC Driver documentation to configure and install the driver on a target system. Once the driver is installed and functional move to the next sections of this guide below.

Installing and Configuring PHP PDO

To install PHP, PDO and the PDO_ODBC driver refer to the generic PHP resources:

  • Download and install the desired PHP version. Note, that PDO driver is enabled by default in PHP as of PHP 5.1.0. On Windows environment you can download PHP binary from the following page.

  • Configure PHP PDO framework.

  • Enable PDO_ODBC driver.

    • On Windows it may be needed to uncomment extension=php_pdo_odbc.dll line in the php.ini and make sure that extension_dir points to a directory which contains php_pdo_odbc.dll. Moreover, this directory has to be added to PATH environment variable.

    • On Unix based systems most often it’s simply required to install a special PHP_ODBC package. For instance, php5-odbc package has to be installed on Ubuntu 14.04.

  • If necessary, configure and build PDO_ODBC driver for a specific system that does not fall under a general case. In most cases, however, simple installation of both PHP and PDO_ODBC driver is going to be enough.

Starting Ignite Cluster

After PHP PDO is installed and ready to be used let’s start an Ignite cluster with an exemplary configuration and connect to the cluster from a PHP application updating and querying cluster’s data:

  • First, the ODBC processor has to be enabled cluster wide. To do so, odbcConfiguration property has to be added to IgniteConfiguration of every cluster node.

  • Next, list configurations for all the caches related to specific data models inside of IgniteConfiguration. Since we’re going to execute SQL queries from PHP PDO side over the cluster, every cache configuration needs to contain a definition for QueryEntity. Alternatively, you can define SQL tables and indexes with Ignite DDL commands.

  • Finally, use the configuration template below to start an Ignite cluster

    <?xml version="1.0" encoding="UTF-8"?>
    
    <beans xmlns="http://www.springframework.org/schema/beans"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xmlns:util="http://www.springframework.org/schema/util"
           xsi:schemaLocation="
            http://www.springframework.org/schema/beans
            http://www.springframework.org/schema/beans/spring-beans.xsd
            http://www.springframework.org/schema/util
            http://www.springframework.org/schema/util/spring-util.xsd">
      <bean id="ignite.cfg" class="org.apache.ignite.configuration.IgniteConfiguration">
    
        <!-- Enabling ODBC. -->
        <property name="odbcConfiguration">
          <bean class="org.apache.ignite.configuration.OdbcConfiguration"></bean>
        </property>
    
        <!-- Configuring cache. -->
        <property name="cacheConfiguration">
          <list>
            <bean class="org.apache.ignite.configuration.CacheConfiguration">
              <property name="name" value="Person"/>
              <property name="cacheMode" value="PARTITIONED"/>
              <property name="atomicityMode" value="TRANSACTIONAL"/>
              <property name="writeSynchronizationMode" value="FULL_SYNC"/>
    
              <property name="queryEntities">
                <list>
                  <bean class="org.apache.ignite.cache.QueryEntity">
                    <property name="keyType" value="java.lang.Long"/>
                    <property name="valueType" value="Person"/>
    
                    <property name="fields">
                      <map>
                        <entry key="firstName" value="java.lang.String"/>
                        <entry key="lastName" value="java.lang.String"/>
                        <entry key="resume" value="java.lang.String"/>
                        <entry key="salary" value="java.lang.Integer"/>
                      </map>
                    </property>
    
                    <property name="indexes">
                      <list>
                        <bean class="org.apache.ignite.cache.QueryIndex">
                          <constructor-arg value="salary"/>
                        </bean>
                      </list>
                    </property>
                  </bean>
                </list>
              </property>
            </bean>
          </list>
        </property>
      </bean>
    </beans>

Connecting From PHP to Ignite Cluster

To connect to Ignite from PHP PDO side the DSN has to be properly configured for Ignite. Refer to the Configuring DSN documentation page for details.

In the example below it’s assumed that DSN’s name is "LocalApacheIgniteDSN". Once everything is configured and can be inter-connected it’s time to connect to the Apache Ignite cluster from a PHP PDO application and execute a number of queries like the ones shown below.

<?php
try {
    // Connecting to Ignite using pre-configured DSN.
    $dbh = new PDO('odbc:LocalApacheIgniteDSN');

    // Changing PDO error mode.
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Preparing query.
    $dbs = $dbh->prepare('INSERT INTO Person (_key, firstName, lastName, resume, salary)
        VALUES (?, ?, ?, ?, ?)');

    // Declaring parameters.
    $key = 777;
    $firstName = "James";
    $lastName = "Bond";
    $resume = "Secret Service agent";
    $salary = 65000;

    // Binding parameters.
    $dbs->bindParam(1, $key);
    $dbs->bindParam(2, $firstName);
    $dbs->bindParam(3, $lastName);
    $dbs->bindParam(4, $resume);
    $dbs->bindParam(5, $salary);

    // Executing the query.
    $dbs->execute();

} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "\n";
    die();
}
?>
<?php
try {
    // Connecting to Ignite using pre-configured DSN.
    $dbh = new PDO('odbc:LocalApacheIgniteDSN');

    // Changing PDO error mode.
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Executing the query. The salary field is an indexed field.
    $dbh->query('UPDATE Person SET salary = 42000 WHERE salary > 50000');

} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "\n";
    die();
}
?>
<?php
try {
    // Connecting to Ignite using pre-configured DSN.
    $dbh = new PDO('odbc:LocalApacheIgniteDSN');

    // Changing PDO error mode.
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Executing the query and getting a result set. The salary field is an indexed field.
    $res = $dbh->query('SELECT firstName, lastName, resume, salary from Person
        WHERE salary > 12000');

    if ($res == FALSE)
        print_r("Exception");

    // Printing results.
    foreach($res as $row) {
        print_r($row);
    }

} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "\n";
    die();
}
?>
<?php
try {
    // Connecting to Ignite using pre-configured DSN.
    $dbh = new PDO('odbc:LocalApacheIgniteDSN');

    // Changing PDO error mode.
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Performing query. Both firstName and lastName are non indexed fields.
    $dbh->query('DELETE FROM Person WHERE firstName = \'James\' and lastName = \'Bond\'');

} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "\n";
    die();
}
?>