C++ SQL Server connection and then read table in a particular way

How to read a table from SQL Server 2008 to a a vector in C++ in the following way:

SQL TABLE 2008 table

1att 2att 3att 4att 5att 6att 7att ... attn
--------------------------------------------
565  526  472  527  483  529  476  470  502
497  491  483  488  488  483  496  515  491
467  516  480  477  494  497  478  519  471
488  466  547  498  477  466  475  480  516

Take every odd column into a only vector...

 array[565, 497, 467, 488开发者_如何学Python,  //1att column 
       472, 483, 480, 547,  //3att column
       483, 488, 494, 477,  //5att column
       ...
       502, 491, 471, 516,  //Last odd att column
       ]

I am using:

#include <vector>
size_t size = 10;                //HOW TO GET THE SIZE
std::vector<int> array(size);    // make room for ,
                                 // and initialize them to 0

for(int i=0; i<size; ++i){
    array[i] = i;
}
...

So my main problem is how to communicate c++ with SQL, and then read the table into the vector... I am using Visual Studio 2008

---

There are many ways to do this:

1. Fetching single columns from database
2. Fetching odd columns from database
3. Fetching entire table from database

Fetching Single Columns

In this method, you tell the data base to select one column. You fill in a single vector by iterating through the result set. Repeat for each column you are interested in, using different vectors for each column.

Fetching odd columns from database

Tell database to return all the columns you are interested in (use many columns in the SELECT statement). Read a record from the result set. Put append values from the fields into appropriate vectors. Repeat reading & extraction for each record in the result set.

Fetching entire table from database.

Similar to above. Tell database to fetch all columns. While there is a result set record, copy values from interested fields into appropriate vectors.

The latter method may be fastest since the database is returning rows from the table without filtering. The other methods require the database to create a new result set of records, filtering from the existing table. The fastest algorithm is obtained by profiling.

Edit 1: Sample Code

I don't use SQL Server 2008, but MySQL. So this code will not work exactly for your purposes.

const char * select_statement[] =
"SELECT 1att, 3att, 5att, 7att, 9att\n"
"FROM data_table;\n";

Result_Set_Type * p_result_set = 0;
p_result_set = database.execute_query(select_statement);
if (p_result_set)
{
    while (p_result_set->next())
    {
        att1_vector.push_back(p_result_set->get_value("1att");
        att3_vector.push_back(p_result_set->get_value("3att");
        att5_vector.push_back(p_result_set->get_value("5att");
        att7_vector.push_back(p_result_set->get_value("7att");
        att9_vector.push_back(p_result_set->get_value("9att");
    }
}

---

Assuming you have a mysql db named test with a table named Persons with a column named PersonId

   #include <cppconn/driver.h>
   #include <cppconn/exception.h>
   #include <cppconn/resultset.h>
   #include <cppconn/statement.h>
   #pragma comment(lib,"mysqlcppconn.lib")
   int _tmain(int argc, _TCHAR* argv[])
   {
    // create a connection
    sql::Driver* sql::Driver *driver = get_driver_instance();
    sql::Connection* con = driver->connect("tcp://127.0.0.1:3306", "user", "password");

    // get database
    con->setSchema("test");

    // create query
    sql::Statement* stmt = con->createStatement();
    sql::ResultSet* res = stmt->executeQuery("SELECT * from Persons");

    // parse result
    while (res->next()) {
        std::cout << res->getString("PersonId") << std::endl;
    }

    //cleanup
    delete res;
    delete stmt;
    delete con;

    return 0;
}