Salesforce Docs Portal

SqlRowIterator Class

Synchronous iterator for executing Data 360 SQL queries and retrieving results row-by-row within the current transaction. Implements Iterator<Row> and Iterable<Row> for use in both for-each and while loops. The iterator is single-use; once all rows are consumed, iterating again does not restart from the beginning.

Namespace

sfsqlquery

Usage

Use SqlRowIterator for queries that return manageable result sets within a single transaction's governor limits. The query isn't submitted until iteration begins or until getQueryId() is called. For large result sets that may exceed single-transaction governor limits, use SqlQueueable instead.

Example

Basic query using a for-each loop:

sfsqlquery.SqlRowIterator iterator = sfsqlquery.SqlStatement.create('SELECT Id__c, Name__c FROM accounts__dlm LIMIT 100', 'default')
    .execute();

for (sfsqlquery.Row row : iterator) {
    System.debug(row.getString('Id__c') + ': ' + row.getString('Name__c'));
}

Resume from a previously saved query ID:

sfsqlquery.QueryHandle handle = sfsqlquery.QueryHandle.create('my-query-id-123', 'default')
    .withOffset(500);  // Skip first 500 rows already processed

sfsqlquery.SqlRowIterator iterator = new sfsqlquery.SqlRowIterator(handle);
while (iterator.hasNext()) {
    sfsqlquery.Row row = iterator.next();
    System.debug(row.getString('Name__c'));
}

SqlRowIterator Constructors

The following are constructors for SqlRowIterator.

SqlRowIterator(handle)

Creates a SqlRowIterator that resumes fetching results for a previously submitted query.

Signature

public SqlRowIterator(sfsqlquery.QueryHandle handle)

Parameters

SqlRowIterator(input)

Creates a SqlRowIterator from a SqlStatement. Equivalent to calling execute() on the statement.

Signature

public SqlRowIterator(sfsqlquery.SqlStatement input)

Parameters

SqlRowIterator Methods

The following are methods for SqlRowIterator.

cancel()

Cancels the query and releases server-side resources. Any subsequent call to hasNext() returns false.

Signature

public void cancel()

Return Value

Type: void

Example

Cancel the query based on a condition:

sfsqlquery.SqlRowIterator iterator = sfsqlquery.SqlStatement.create('SELECT Id__c, Amount__c FROM accounts__dlm', 'default')
    .execute();

Decimal totalAmount = 0;
for (sfsqlquery.Row row : iterator) {
    Decimal amount = row.getDecimal('Amount__c');
    if (amount != null) {
        totalAmount += amount;
        if (totalAmount > 1000000) {
            iterator.cancel();  // Stop when threshold is reached
            break;
        }
    }
}

getColumnNames()

Returns the ordered list of column names from the query result set. Available after the first page of results is fetched.

Signature

public List<String> getColumnNames()

Return Value

Type: List<String>

An ordered list of column names as they appear in the result set.

Example

See getMetadata() for a combined example.

getMetadata()

Returns the column metadata for the query result set, including column names and their data types. Available after the first page of results is fetched.

Signature

public List<ConnectApi.QuerySqlMetadataItem> getMetadata()

Return Value

Type: List<ConnectApi.QuerySqlMetadataItem>

A list of ConnectApi.QuerySqlMetadataItem objects describing each column's name and data type.

Example

Use metadata and column names for dynamic column handling:

sfsqlquery.SqlRowIterator iterator = sfsqlquery.SqlStatement.create('SELECT * FROM accounts__dlm LIMIT 10', 'default')
    .execute();

List<String> columns = iterator.getColumnNames();
List<ConnectApi.QuerySqlMetadataItem> metadata = iterator.getMetadata();

System.debug('Query returned ' + columns.size() + ' columns');
for (Integer i = 0; i < metadata.size(); i++) {
    System.debug(columns[i] + ' (' + metadata[i].type + ')');
}

// Process rows using the metadata
for (sfsqlquery.Row row : iterator) {
    for (String columnName : columns) {
        Object value = row.getObject(columnName);
        System.debug(columnName + ': ' + value);
    }
}

getQueryId()

Returns the server-assigned ID for this query. Triggers query submission if the query hasn't started yet. Save this value to resume the query in a later transaction using QueryHandle.create().

Signature

public String getQueryId()

Return Value

Type: String

The server-assigned query ID string.

Example

Save query ID for resuming later:

sfsqlquery.SqlRowIterator iterator = sfsqlquery.SqlStatement.create('SELECT Id__c FROM accounts__dlm', 'default')
    .execute();

String queryId = iterator.getQueryId();
// Store queryId to resume in a later transaction

hasNext()

Returns true if more rows remain to be fetched, or false if all rows have been consumed or the query was canceled.

Signature

public Boolean hasNext()

Return Value

Type: Boolean

true if more rows are available; false otherwise.

Example

Use in a while loop:

sfsqlquery.SqlRowIterator iterator = sfsqlquery.SqlStatement.create('SELECT Name__c FROM accounts__dlm', 'default')
    .execute();

while (iterator.hasNext()) {
    sfsqlquery.Row row = iterator.next();
    System.debug(row.getString('Name__c'));
}

iterator()

Returns this SqlRowIterator as an Iterator, enabling use in for-each loops.

Signature

public Iterator iterator()

Return Value

Type: Iterator

This SqlRowIterator instance as an Iterator.

next()

Returns the next Row in the result set. Throws NoSuchElementException if hasNext() is false.

Signature

public sfsqlquery.Row next()

Return Value

Type: sfsqlquery.Row

The next Row in the result set.

Example

Process rows one at a time:

sfsqlquery.SqlRowIterator iterator = sfsqlquery.SqlStatement.create('SELECT Id__c FROM accounts__dlm LIMIT 10', 'default')
    .execute();

if (iterator.hasNext()) {
    sfsqlquery.Row firstRow = iterator.next();
    System.debug('First ID: ' + firstRow.getString('Id__c'));
}

toString()

Returns a string representation of this SqlRowIterator.

Signature

public String toString()

Return Value

Type: String

A string representation of this SqlRowIterator.