mySQL query in C++

Question

In the following code for retrieving data via a SQL query from the db, is there a way I can replace row[0] with fieldname. I am only enclosing the relevant part of the code

MYSQL_RES *resptr;
MYSQL_ROW row;

while ( ( row = mysql_fetch_row(resptr)) != NULL ) 
{
for (int i=0;i<8;i++)
string key = row[0];
 }

row[0] is the trans_date column. the code works fine as it is right now, but is there a way to assign key by using the fieldname vs. having to remember all the field numbers. thanks!

You could have an enum to represent columns names. If you do this you should have a test that compare table columns and your enum. — anno
– anno, Commented Aug 21, 2011 at 20:59
thanks anno, that's simpler...so if I wrote enum {trans_date, fname, lname}; I could just write string key=row[trans_date]; then I wouldn't need a test, would I? — itcplpl
– itcplpl, Commented Aug 21, 2011 at 21:08
By a test, I mean you should have in your test suite a test that checks that you haven't changed your columns order or name. Imagine you change trans_date place to fname, now row[trans_date] will return the wrong value. — anno
– anno, Commented Aug 21, 2011 at 21:25

dee-see · Accepted Answer · 2011-08-21 22:33:48Z

2

You can retrieve field names by doing

field = mysql_fetch_field(resptr);
std::cout << field->name;

(Put in a while loop to loop through all field)

But you can't call row[fieldName]. What you can do though is define variables to map column names and numbers

int id = 0;
int trans_date = 1;
// Code happening here
std::cout << row[id] << " " << row[trans_date];

UPDATE: You could always do something like this (using STL's map):

map<string, int> columns;
int i = 0;
while(field = mysql_fetch_field(resptr)){
  columns.insert(pair<string,int>(field->name, i++));
}

And then use row[columns["name"]];.

edited Aug 21, 2011 at 22:33

answered Aug 21, 2011 at 20:23

dee-see

24.1k6 gold badges63 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

itcplpl Over a year ago

when I have 8 columns, I am basically mapping field names to field numbers so that instead of using field numbers in my code, I use the field names - but it seems like a lot of effort to get that done. isn't there a way to do something like row[i]=field->name..

itcplpl Over a year ago

thx, that works too. I wonder whether I should go with enum or your solution ;-)

anno Over a year ago

The advantage of the map solution is that if you change your columns name, the map will return an invalid iterator. Also, changing your column order in your table won't affect you much (if it's done when the application is not running). The enum solution is faster, but that's a Micro-Optimization.

itcplpl Over a year ago

@Vache, I get an error with columns.insert to the effect of error: no matching function for call to ‘std::map<std::basic_string<char>, int>::insert(char*&, int)’....

itcplpl Over a year ago

I edited it to: columns.insert(pair<string,int>(field->name, i++)); and that fixed it :-)

Warren Young · Accepted Answer · 2011-08-22 19:14:08Z

0

MySQL++ solves this problem. You can say things like:

string key = row["key"];

or by use of its SSQLS feature, this:

MyTableStructure foo = result[i];
cout << "The key is " << foo.key << endl;

answered Aug 22, 2011 at 19:14

Warren Young

42.7k8 gold badges87 silver badges105 bronze badges

Collectives™ on Stack Overflow

mySQL query in C++

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related