Modern C++ Interface

wxSQLite3 provides a number of convenience features that make working with SQLite result sets and prepared statements more natural in modern C++ code. These features complement the traditional cursor-based interface and the existing type-specific methods.

Type-aware access to result-set values

In addition to the existing type-specific methods for retrieving values from a result set, wxSQLite3 provides a template-based Get<T>() method. The method returns a std::optional<T>, making the distinction between an SQL NULL value and a value of the requested C++ type explicit.

For example:

const std::optional<int> id = resultSet.Get<int>(0);
const std::optional<wxString> name = resultSet.Get<wxString>(1);
 
if (id)
{
    // The column contains a non-NULL integer value.
}

If the corresponding SQLite value is NULL, the returned std::optional is empty. Otherwise, it contains the value converted to the requested C++ type.

The template-based interface is implemented in terms of the existing type-specific result-set methods. It therefore provides a convenient and type-oriented interface without introducing a separate value-conversion mechanism.

Type-aware binding of statement parameters

Prepared-statement parameters can likewise be bound using the template-based Bind<T>() interface. The method accepts a std::optional<T>, allowing an SQL NULL value to be represented naturally by an empty std::optional.

For example:

std::optional<int> id = ...;
std::optional<wxString> name = ...;
 
stmt.Bind(1, id);
stmt.Bind(2, name);

An engaged std::optional binds its contained value, while an empty std::optional binds an SQL NULL.

As with Get<T>(), the template-based binding methods use the existing type-specific binding methods internally.

This makes std::optional a convenient way to handle nullable database values without having to treat SQL NULL as a special case in application code.

Binding and retrieving tuples of values

For situations where several values need to be bound to a prepared statement or retrieved from a result set, wxSQLite3 also provides BindTuple() and GetTuple().

The first variant uses consecutive parameter or column indices. The values are therefore associated with indices starting at the specified first index.

For example:

const auto values = resultSet.GetTuple<int, wxString, double>(0);

retrieves three values from consecutive columns.

Similarly, values can be bound to consecutive parameters:

stmt.BindTuple(1, id, name, amount);

The tuple-based methods use the same type-aware conversion and std::optional handling as the individual Get<T>() and Bind() methods. Consequently, nullable database values can be represented directly in the resulting or supplied tuple.

A second variant of GetTuple() and BindTuple() accepts an array of indices. This allows the values to be associated with arbitrary, non-consecutive columns or parameters.

For example:

constexpr std::array<int, 3> columns{0, 3, 7};
 
const auto values =
    resultSet.GetTuple<int, wxString, double>(columns);

This is useful when the values of interest are distributed across a result set and their positions do not form a consecutive sequence.

The corresponding binding operation can use an index array in the same way:

constexpr std::array<int, 3> parameters{1, 4, 6};
 
stmt.BindTuple(parameters, id, name, amount);

The tuple-based interface is particularly useful when several database values correspond directly to members or values of a C++ data structure and should be handled as a group.

Range-based result-set processing

Traditionally, a wxSQLite3ResultSet is processed using its cursor-oriented interface:

while (resultSet.NextRow())
{
    const auto id = resultSet.Get<int>(0);
    const auto name = resultSet.Get<wxString>(1);
 
    // Process row
}

For applications using modern C++ features, a result set can also be processed with a range-based for loop:

for (const auto& row : resultSet)
{
    const auto id = row.Get<int>(0);
    const auto name = row.Get<wxString>(1);
 
    // Process row
}

This provides a concise and familiar way to iterate over all rows of a result set.

The iterator interface can also be used directly when more control over the iteration is required:

for (auto it = resultSet.begin(); it != resultSet.end(); ++it)
{
    const auto& row = *it;
 
    // Process row
}

The iterator and range-based interfaces are alternatives to the traditional NextRow() loop. They do not change the underlying SQLite result-set processing model; they provide an additional interface that integrates with the standard C++ iteration mechanisms.

Combining the features

The individual template methods, tuple operations, and range-based result-set processing can be combined to write concise and type-oriented database code.

For example:

for (const auto& row : resultSet)
{
    const auto values =
        row.GetTuple<int, wxString, double>({0, 2, 5});
 
    // Process values
}

The tuple returned by GetTuple() contains std::optional values, so SQL NULL values remain distinguishable from actual C++ values.

These additions are intended to complement rather than replace the established wxSQLite3 API. Existing applications can continue to use the traditional cursor-based and type-specific methods without modification.