How to read values (possibly NULL) from database into std::vector via Poco::Data?

Question

I want to read many rows (values may be NULL) from a SQL-Server database into an std::vector. Reading a single value both NULL and non-NULL works. But I have problems reading multiple values.

Following the POCO Data User Guide, I came up with the following code:

Boilerplate code before:

Poco::Data::ODBC::Connector::registerConnector();
Poco::Data::Session session("ODBC", makeConnectionString());
Poco::Data::Statement select(session);

Code ok, reading a single value:

// note initialization here: otherwise ODBCHandleException will be thrown on non-NULL-value
Poco::Nullable<std::string> n = std::string("");

select << "SELECT col_name FROM table_name WHERE id=6;", into(n);
select.execute();

Code not ok, reading multiple values, throws Poco::Data::ODBC::HandleException:

// how to initialize here?
std::vector<Poco::Nullable<std::string>> ns;

select << "SELECT col_name FROM table_name;", into(ns);
select.execute(); // <- error thrown here

Any suggestion how I can fix my code?

Answer 1

Many approaches were tried out. In the end I replaced the std::vector by Poco::Data::RecordSet. Now I can iterate over its Poco::Data::Rows, which in turn gives me the column values typed as Poco::Dynamic::Var. Here Poco::Dynamic::Var::isEmpty() lets me check if the database entry is NULL.

See the simplified code for clarification:

Poco::Data::ODBC::Connector::registerConnector();
Poco::Data::Session session("ODBC", makeConnectionString());

Poco::Data::Statement select(session);
select << "SELECT col_name FROM table_name";
select.execute();

Poco::Data::RecordSet rows(select);        // use Recordset here instead of vector...

std::vector<std::string> beans;            // ...and convert it to vector...

auto convert = [&](Poco::Data::Row row)    // ...with this lambda function.
{
    const Poco::Dynamic::Var var = row["col_name"];

    if (var.isEmpty())                     // convert NULL values to empty string
        return std::string("");

    return var.convert<std::string>();
};

std::transform(rows.begin(), rows.end(), std::back_inserter(beans), convert);

for (const auto& b : beans)
    std::cout << b << std::endl;

The nice thing is, that it scales to a more sophisticated version (only read, if you're deeply interested):

class GenericTableManager
{
public:
    GenericTableManager(Poco::Data::Session& session, const std::string& tableName);

    template <class T_Bean>
    std::vector<T_Bean> selectAll(std::function<T_Bean(Poco::Data::Row)> convert) const
    {
        Poco::Data::Statement select(m_session);
        select << "SELECT * FROM " << m_tableName;
        select.execute();

        Poco::Data::RecordSet rows(select);

        std::vector<T_Bean> beans;
        std::transform(rows.begin(), rows.end(), std::back_inserter(beans), convert);
        return beans;
    }

    template <class T_Bean>
    size_t insert(
        const T_Bean& bean,
        std::function<Poco::Data::Row(const T_Bean&)> convert) const
    {
        const Poco::Data::Row row = convert(bean);

        Poco::Data::Statement stmt(m_session);
        stmt << "INSERT INTO " << m_tableName
            << " (" << StringUtil::join(*row.names()) << ") "
            << "VALUES (" << placeholders(row.fieldCount()) << ")";

        for (const auto& value : row.values())
            stmt.addBind(Poco::Data::Keywords::bind(value));

        return stmt.execute();
    }
    
private:
    Poco::Data::Session& m_session;
    const std::string m_tableName;

    static std::string placeholders(const size_t n);
};


// on caller side:

// once you have your ORM converters set up...
auto convertOOtoR = [](const MyBean& bean) -> Poco::Data::Row {
    Poco::Data::Row row;
    row.append("myattr1", bean.myattr1);
    row.append("myattr2", bean.myattr2);
    return row;
};

auto convertRtoOO = [](Poco::Data::Row row) -> MyBean{
    MyBean bean;
    Poco::Dynamic::Var var = row["myattr1"];
    bean.myattr1 = var.isEmpty() ? "" : var.convert<std::string>();
    // ...
    return bean;
};

// ...the SQL part boils down to a oneliner
GenericTableManager tm(session, "mytablename");
tm.insert<MyBean>(createMyBean(), convertOOtoR);
std::vector<MyBean> beans = tm.selectAll<MyBean>(convertRtoOO);