using variable in ORDER BY

[dc-price]

I am trying to avoid duplicating code merely to have a different ORDER BY clause . This compiles and runs but doesn't sort.

string sortorder = "familyName, givennames";                <<<<< This would be changed via user input

rowset<row> rs = (sql.prepare << "SELECT	personID, \
											active, \
											familyName, \
											givenNames, \
											yearBorn, \
											yearDied \
									FROM	individuals \
									ORDER BY :sortorder ",
									use(sortorder));	
									
	for (rowset<row>::const_iterator it = rs.begin(); it != rs.end(); ++it)
	{
		row const& row = *it;
	
		output_file << setw(7)  << left  << row.get<int>(0);
		output_file << setw(8)  << left  << row.get<string>(1);
		output_file << setw(15) << left  << row.get<string>(2);
		output_file << setw(25) << left  << row.get<string>(3);
		output_file << setw(7)  << right << row.get<int>(4);
		output_file << setw(7)  << right << row.get<int>(5) << endl;				
	}	

Thanks for any consideration.

David

[zann1x]

Placeholders in prepared statements are meant to be used for values, not column names. You are limited by the database capabilities here.

I would have expected a runtime exception thrown by the database, though.

[dc-price]

Thanks for the explanation. I managed to do a workaround by concatenating the SELECT string, as follows:

string sql1 = "SELECT personID, active, familyName, givenNames, yearBorn, YearDied FROM individuals ";
string sql2 = "ORDER BY personID";
string sql3 = "ORDER BY familyName, givenNames";

string sql4;
if (choice == 1) { sql4 = sql1 + sql2; }               // choice is the user's input
if (choice == 2) { sql4 = sql1 + sql3; }
	
rowset<row> rs = (sql.prepare << sql4);          // this works