CODEWITHSUNDEEP
c++mysqlsqlwinapi
James Diaz
James Diaz

Reputation: 329

Inserting a variable into a mysql table

I know how to insert non variable values into a MySql table (using C++):

Ex:

//Table person:
mysql_query(conn, "INSERT INTO MyTable VALUES (  '1', 'John', 'Kennedy')");
mysql_query(conn, "INSERT INTO MyTable VALUES (  '2', 'Dave', 'Chappelle')");
mysql_query(conn, "INSERT INTO MyTable VALUES (  '3', 'Arnold', 'Schwarzenegger')");
//Up to 100 rows of data...

What I would like to do is be able to insert a variable into a table with C++:

Ex:

i=0;
for(i=0; i < 100; i++)
{
mysql_query(conn, "INSERT INTO MyTable VALUES ( i, 'FirstName', 'LastName')"); 
}

Rather than having to manually type in 100 rows of data, I would like to be able to use a for loop, and increment a variable to create the rows for me.

Does anyone know how to insert a variable into a mysql database using C++?

Upvotes: 6

Views: 17759

Answers (5)

user1642018
user1642018

Reputation:

this worked for me.

#include <stdio.h>
#include <stdlib.h>
#include <iostream>

#include <mysql_connection.h>

#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>

sql::Driver *driver;
sql::Connection *con;
sql::ResultSet *res;
sql::Statement *stmt;

/* Create a connection */
driver = get_driver_instance();
con = driver->connect("tcp://127.0.0.1:3306", "admin", "password");

// db name
con->setSchema("db_name");

std::string lookup = "bob" ;

stmt = con->createStatement();
res = stmt->executeQuery("SELECT last_name FROM names_table WHERE first_name = '" + lookup + "'");
while (res->next()) {
  std::cout << res->getString("last_name") << std::endl;
}

delete res;
delete stmt;
delete con;

Upvotes: 0

The Beast
The Beast

Reputation: 1669

You can do it by 

   int var = 10;
   string str = to_string(var);
   string requete="INSERT INTO stat(temps) VALUES (\"";
   requete += str;
   requete += "\")";
   mysql_query(&mysql,requete.c_str());

just specify in mySql that the field has a type of int , double , float etc.

Upvotes: 3

Karthik T
Karthik T

Reputation: 31972

I am not too familiar with the syntax to get it to work, but the generic version of what you want is Prepared Statements. You make a statement which has variables as placeholders. These are later provided values and used.

Edit: You can find more information about prepared statement use in c++ at the MySQL website

// ...
sql::Connection *con;
sql::PreparedStatement  *prep_stmt
// ...

prep_stmt = con->prepareStatement("INSERT INTO test(id, label) VALUES (?, ?)");

prep_stmt->setInt(1, 1);
prep_stmt->setString(2, "a");
prep_stmt->execute();

prep_stmt->setInt(1, 2);
prep_stmt->setString(2, "b");
prep_stmt->execute();

delete prep_stmt;
delete con;

Upvotes: 5

Bunny Pigtails
Bunny Pigtails

Reputation: 112

You need to make the query string first before performing a query. Replace the below printf with a string class instance to make one. You may want to put it in a vector of strings also.

char queryFmt[]="INSERT INTO myTable VALUES('%d','%s',%s)\n";
char color[16];
char fruit[32];
for(int i=0;i<2;i++)
{       
    //scanf_s("%s",color);
    //scanf_s("%s",fruit);      
    std::cin>>color;
    std::cin>>fruit;
    printf_s(queryFmt,i,color,fruit);

}

Please refer to many a thread of the related issues using scanf to read in a character or string inside a loop if you don't know yet and are interested in any.

Upvotes: 0

Shamis Shukoor
Shamis Shukoor

Reputation: 2515

i=0;
for(i=0; i < 100; i++)
{
mysql_query(conn, "INSERT INTO MyTable VALUES ( '"+i+"', 'blue', 'pumpkin')"); 
}

This should work

Upvotes: -1

Related Questions