CODEWITHSUNDEEP
c++sqlsqlite
jobobo
jobobo

Reputation: 389

How to get columns names during a SELECT operation that returns zero items in SQLite

I'm calling 

sqlite3_exec(db, "SELECT a,b,Z FROM t1 WHERE t1.id = 1", func_ptr, arg_ptr)

to collect the row content and the column names. I'm trying to create a generic helper function that will take any SELECT command and construct a vector<vector<string>> with the first row containing the column names (and rest containing the contents of the rows).

But this fails to capture the column names when there are no rows to show since func_ptr is only called with one or more rows to display.

How can I get the column names when SELECT returns zero rows? EDIT: I would prefer not to create a level of indirection by creating a table and then calling SELECT on that table. Thanks. EDIT: I would only like to collect what the SELECT statement contains.

Upvotes: 1

Views: 5188

Answers (1)

Mat
Mat

Reputation: 206879

You can use PRAGMA table_info(tablename); to get a result set that includes column name and type for the given table.

Call the pragma as you would a normal query (i.e. with sqlite3_exec for instance).

If you need this information for a SQL statement you didn't generate yourself that contains a column selection, you need to use a prepared statement (you should be doing that anyway). Then you can use sqlite3_column_count and sqlite3_column_name to get the information you're after.

Example usage:

sqlite3_stmt *stmt;
rc = sqlite3_prepare_v2(db, "select a,b from t where 0=1", -1, &stmt, 0);
if( rc!=SQLITE_OK ){
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
}

int cols = sqlite3_column_count(stmt);
fprintf(stdout, "%d columns\n", cols);
for (int i=0; i<cols; i++)
    fprintf(stdout, "%d. %s\n", i, sqlite3_column_name(stmt, i));

// iterate over the results with sqlite3_step
// dispose of the statement with sqlite3_finalize

You can adapt this to store the column names in a std::vector.

Upvotes: 4

Related Questions