CODEWITHSUNDEEP
mysqlc
Grace
Grace

Reputation: 21

Multiple Inserts in one sql statement in C

I want to insert 6406 recods into a table in one swoop without having to prepare and execute individual records. (fastest). In the code below, I want "i" to change for every insert

Ive tried everything

for( int i = 0; i < 6406 ; i++)
 {
  sprintf( query, "INSERT INTO table1"
                "(table_id, curr_id, cur_ref_id) "
                "VALUES (%d,%d,%d)",
                table_id,
                i,
                table_id);

  //assemble query
   DBH->prepare(query);
 }
DBH->execute();

Inset prepare failed for table1

Upvotes: 1

Views: 169

Answers (1)

nemequ
nemequ

Reputation: 17532

My memory of the MySQL C API is a bit fuzzy, but IIRC it should look something like:

MYSQL_STMT* stmt = mysql_stmt_init(MYSQL *mysql);
mysql_stmt_prepare(stmt,
  "INSERT INTO table1 (table_id, curr_id, cur_ref_id) VALUES (?,?,?)",
  len_of_previous_argument);

MYSQL_BIND params[3];
memset(params, 0, sizeof(params));
params[0].buffer_type = MYSQL_TYPE_LONG;
params[1].buffer_type = MYSQL_TYPE_LONG;
params[2].buffer_type = MYSQL_TYPE_LONG;

params[0].buffer = &table_id;
params[2].buffer = &table_id;

mysql_bind_param(stmt, params);

mysql_autocommit(conn, 0);
for ( int i = 0; i < 6406 ; i++) {
  params[1].buffer = &i;
  mysql_stmt_execute(stmt);
}
mysql_commit(conn);

mysql_stmt_close(stmt);

You'll obviously want to throw in some error handling, but this should give you the basic idea.

It might be faster to only have one parameter and encode the table_id values in the query string instead, but I'm lazy and you obviously know how to do that already (snprintf should have "(%d,?,%d)", then pass the result to mysql_stmt_prepare, then the params array will only be a single item).

Upvotes: 1

Related Questions