having problem inserting multiple value into mysql db table using c++

how d开发者_开发问答o i insert multiple values from a vector into a mysql database table using c++ I.e mysql c++ connector is not installed.

my code

#include cstdlib
#include iostream
#include mysql.h
using namespace std;

MYSQL *connection, mysql;
MYSQL_RES *result;
MYSQL_ROW row;
int query_state;

I can insert row manually like this:

query_state=mysql_query(connection,"insert into test values('boy','girl')");

But I need to read in values from a file or vector

Thanks

I'd use mysqls prepared statement API to do this. You can see a complete example here.

You'd just loop over the vector rebinding to the new values and reexecuting the statement. This will probably be more efficient and less error prone (harder to suffer from SQL injection type attacks) than manually constructing and rerunning the query.

The other option (Not as good IMO, but simpler and can suffer from sql injection issues if you're not careful) is to loop over the vector constructing the query in a stringstream and then using the stringstream buffer.

The string stream method looks a bit like this (untested and probably buggy):

vector<pair<string,int> > values = get_my_values();
stringstream query;
query<<"insert into test values";
for( vector<pair<string,int> >::iterator it = values.begin() ;
     it != values.end();
     ++it )
{
    query<<"(\""<<mysql_real_escape(connection, it->first.c_str())<<"\","<<it->second<<")";
    if( it+1 != values.end() ) { query<<","; }
}
query_state=mysql_query(connection, query.str().c_str() );