I'm trying to retrieve 2 million rows of data from SQL Server using poco library

The code I'm using:

Poco::Data::ODBC::Connector::registerConnector();

Poco::Data::Session session("ODBC", "Driver={SQL Server};" "Server=localhost;" "Database=sample_db;" "UID=<username>;" "PWD=<pwd>;");

std::vector<int> ids;
std::vector<std::string> timestamps;
std::vector<int> datatypes;
std::vector<Poco::Nullable<int>> int_values;
std::vector<Poco::Nullable<float>> float_values;
std::vector<std::string> string_values;
std::vector<bool> bStatus;

std::string sql = "SELECT id, timestamp, type, "
    "int_value, float_value, string_value, bStatus "
    "FROM sample_tbl "
    "WHERE id = ? "
    "AND timestamp >= ? "
    "AND timestamp <= ?";

std::cout << "Executing query for tagid: " << tagid << std::endl;

// Create and execute statement with parameters
Poco::Data::Statement select(session);

select << sql,
    Poco::Data::Keywords::into(ids),
    Poco::Data::Keywords::into(timestamps),
    Poco::Data::Keywords::into(datatypes),
    Poco::Data::Keywords::into(int_values),
    Poco::Data::Keywords::into(float_values),
    Poco::Data::Keywords::into(string_values),
    Poco::Data::Keywords::into(bStatus),
    Poco::Data::Keywords::use(tagid),
    Poco::Data::Keywords::use(starttime),
    Poco::Data::Keywords::use(endtime);
    
std::cout << "Executing query..." << std::endl;

select.execute();

In this line of code - select.execute(), im getting exception in poco library code in some line of code with std::bad_alloc exception. This exception is coming only if number of rows in for that select query is more than 1.5 million.

Is there any other way to retrieve large data from db using poco library? In our project, we are using poco C++ library. So i need to use this library.

I'm trying to retrieve 2 million rows of data from SQL Server using poco library

The code I'm using:

Poco::Data::ODBC::Connector::registerConnector();

Poco::Data::Session session("ODBC", "Driver={SQL Server};" "Server=localhost;" "Database=sample_db;" "UID=<username>;" "PWD=<pwd>;");

std::vector<int> ids;
std::vector<std::string> timestamps;
std::vector<int> datatypes;
std::vector<Poco::Nullable<int>> int_values;
std::vector<Poco::Nullable<float>> float_values;
std::vector<std::string> string_values;
std::vector<bool> bStatus;

std::string sql = "SELECT id, timestamp, type, "
    "int_value, float_value, string_value, bStatus "
    "FROM sample_tbl "
    "WHERE id = ? "
    "AND timestamp >= ? "
    "AND timestamp <= ?";

std::cout << "Executing query for tagid: " << tagid << std::endl;

// Create and execute statement with parameters
Poco::Data::Statement select(session);

select << sql,
    Poco::Data::Keywords::into(ids),
    Poco::Data::Keywords::into(timestamps),
    Poco::Data::Keywords::into(datatypes),
    Poco::Data::Keywords::into(int_values),
    Poco::Data::Keywords::into(float_values),
    Poco::Data::Keywords::into(string_values),
    Poco::Data::Keywords::into(bStatus),
    Poco::Data::Keywords::use(tagid),
    Poco::Data::Keywords::use(starttime),
    Poco::Data::Keywords::use(endtime);
    
std::cout << "Executing query..." << std::endl;

select.execute();

In this line of code - select.execute(), im getting exception in poco library code in some line of code with std::bad_alloc exception. This exception is coming only if number of rows in for that select query is more than 1.5 million.

Is there any other way to retrieve large data from db using poco library? In our project, we are using poco C++ library. So i need to use this library.

• c++
• database
• poco
• poco-libraries

• 10 you should probably avoid indian terms like "lakh" which are unlikely to be understood by non-indians. Are you building a 64-bit application? My guess would be that your standard library isn't able to create 1.5 million element std::vectors – Alan Birtles Commented Feb 17 at 8:22
• It makes little sense to extract that number of rows to process. Even if you are manipulating the data internally in your application you should use a constraint to limit the number of rows extracted each time. – OldBoy Commented Feb 17 at 9:26

1 Answer 1

Use select , Poco::Data::Limit(100'000) to retrieve that much data in one go. Do something with it, then call execute() again. Repeat until select.done() is true or execute returns 0.