Java*C#/.NETC++SQLIntegrationsIgnite for SparkTools
Home
JavadocExamplesAsk a Question
Start typing to search…

SQL

❗️

This is a legacy Apache Ignite documentation

The new documentation is hosted here: https://ignite.apache.org/docs/latest/

SQL Query

PHP client supports Ignite SQL queries. A query method returns a cursor object with the standard PHP Iterator interface which allows to iterate over the set with the query results lazily, one by one. Additionally, the cursor has methods to get the whole results at once.

First, define the query by creating and configuring an instance of the SqlQuery class.

Then, pass the SqlQuery instance to the query method of the CacheInterface.

Finally, use the returned object with the CursorInterface to iterate over or get all cache entries returned by the query.

use Apache\Ignite\Client;
use Apache\Ignite\ClientConfiguration;
use Apache\Ignite\Cache\CacheConfiguration;
use Apache\Ignite\Type\ObjectType;
use Apache\Ignite\Query\SqlFieldsQuery;
use Apache\Ignite\Exception\ClientException;

function performSqlFieldsQuery(): void
{
    $client = new Client();
    try {
        $client->connect(new ClientConfiguration('127.0.0.1:10800'));
        $cache = $client->getOrCreateCache('myPersonCache', (new CacheConfiguration())->
        setSqlSchema('PUBLIC'));

        // create table using SqlFieldsQuery
        $cache->query(new SqlFieldsQuery(
            'CREATE TABLE Person (id INTEGER PRIMARY KEY, firstName VARCHAR, lastName VARCHAR, salary DOUBLE)'))->getAll();

        // insert data into the table
        $insertQuery = (new SqlFieldsQuery('INSERT INTO Person (id, firstName, lastName, salary) values (?, ?, ?, ?)'))->
            setArgTypes(ObjectType::INTEGER);
        $cache->query($insertQuery->setArgs(1, 'John', 'Doe', 1000))->getAll();
        $cache->query($insertQuery->setArgs(2, 'Jane', 'Roe', 2000))->getAll();

        // obtain sql fields cursor
        $sqlFieldsCursor = $cache->query(
            (new SqlFieldsQuery("SELECT concat(firstName, ' ', lastName), salary from Person"))->
                setPageSize(1));

        // iterate over elements returned by the query
        foreach ($sqlFieldsCursor as $fields) {
            print_r($fields);
        }

        // drop the table
        $cache->query(new SqlFieldsQuery("DROP TABLE Person"))->getAll();
    } catch (ClientException $e) {
        echo($e->getMessage());
    } finally {
        $client->disconnect();
    }
}

performSqlFieldsQuery();

SQL Fields Query

This type of queries is used to obtain individual fields as a part of an SQL query result set, execute DML and DDL statements such as INSERT, UPDATE, DELETE, CREATE and other.

First, define the query by creating and configuring an instance of the SqlFieldsQuery class.

Then, pass the SqlFieldsQuery instance to the query method of the CacheInterface.

Finally, use the returned object with the SqlFieldsCursorInterface to iterate over or get all elements returned by the query.

use Apache\Ignite\Client;
use Apache\Ignite\ClientConfiguration;
use Apache\Ignite\Cache\CacheConfiguration;
use Apache\Ignite\Type\ObjectType;
use Apache\Ignite\Query\SqlFieldsQuery;
use Apache\Ignite\Exception\ClientException;

function performSqlFieldsQuery(): void
{
    $client = new Client();
    try {
        $client->connect(new ClientConfiguration('127.0.0.1:10800'));
        $cache = $client->getOrCreateCache('myPersonCache', (new CacheConfiguration())->
        setSqlSchema('PUBLIC'));

        // create table using SqlFieldsQuery
        $cache->query(new SqlFieldsQuery(
            'CREATE TABLE Person (id INTEGER PRIMARY KEY, firstName VARCHAR, lastName VARCHAR, salary DOUBLE)'))->getAll();

        // insert data into the table
        $insertQuery = (new SqlFieldsQuery('INSERT INTO Person (id, firstName, lastName, salary) values (?, ?, ?, ?)'))->
            setArgTypes(ObjectType::INTEGER);
        $cache->query($insertQuery->setArgs(1, 'John', 'Doe', 1000))->getAll();
        $cache->query($insertQuery->setArgs(2, 'Jane', 'Roe', 2000))->getAll();

        // obtain sql fields cursor
        $sqlFieldsCursor = $cache->query(
            (new SqlFieldsQuery("SELECT concat(firstName, ' ', lastName), salary from Person"))->
                setPageSize(1));

        // iterate over elements returned by the query
        foreach ($sqlFieldsCursor as $fields) {
            print_r($fields);
        }

        // drop the table
        $cache->query(new SqlFieldsQuery("DROP TABLE Person"))->getAll();
    } catch (ClientException $e) {
        echo($e->getMessage());
    } finally {
        $client->disconnect();
    }
}

performSqlFieldsQuery();

📘

PHP example files

PHP thin client contains fully workable examples to demonstrate the behavior of the client.

Updated 2 months ago