C++ executeQuery() error displaying MySQL data fro

Question

I need some help. I had this code (below), to add data to a MySQL table and then return that same table. The code is doing fine, when I run it it adds the column to the MySQL table BUT it stops, with the error:

SQL error. Error message: 

Literally blank. If I use a SELECT statement rather than an INCLUDE one in executeQuery(), it runs with no problem and no error message, just displays my table (or parts of it). What am I missing?

I am using Visual Studios 2015, and MySQL Server.

The end-goal is to connect an API with an SQL table using C++, to record data according to a specific timespan. This is one of the first steps, just to make sure that I can link MySQL with C++ properly.

Sorry if this post is poorly written, first time here and definitely not an experienced programmer... Also, I looked into other threads, but as this is so specific I could not find them helpful.

// Standard C++ includes
#include <iostream>
#include <cstdlib>
#include <string>
using namespace std;

// Include the Connector/C++ headers
#include "cppconn/driver.h"
#include "cppconn/exception.h"
#include "cppconn/resultset.h"
#include "cppconn/statement.h"

// Link to the Connector/C++ library
#pragma comment(lib, "mysqlcppconn.lib")

// Specify our connection target and credentials
const string server = "127.0.0.1:3306";
const string username = "root";
const string password = "root";
const string database = "dbex"; 
const string table = "tbex";

int main()
{
    sql::Driver     *driver; // Create a pointer to a MySQL driver object
    sql::Connection *dbConn; // Create a pointer to a database connection object
    sql::Statement  *stmt;   // Create a pointer to a Statement object to hold our SQL commands
    sql::ResultSet  *res;    // Create a pointer to a ResultSet object to hold the results of any queries we run

                             // Try to get a driver to use to connect to our DBMS
    try
    {
        driver = get_driver_instance();
    }
    catch (sql::SQLException e)
    {
        cout << "Could not get a database driver. Error message: " << e.what() << endl;
        system("pause");
        exit(1);
    }

    // Try to connect to the DBMS server
    try
    {
        dbConn = driver->connect(server, username, password);
        dbConn->setSchema(database);
    }
    catch (sql::SQLException e)
    {
        cout << "Could not connect to database. Error message: " << e.what() << endl;
        system("pause");
        exit(1);
    }
    stmt = dbConn->createStatement();

    // Try to query the database
    try
    {
        //stmt->execute("USE " + database);              // Select which database to use. Notice that we use "execute" to perform a command.
        res = stmt->executeQuery("INSERT INTO "+ table +"(Brand, Model, Power, `Last Used`,`# Times Used`) VALUES('Ferrari','Modena','500','Never',0)"); 
        res = stmt->executeQuery("SELECT * FROM cars"); // Perform a query and get the results. Notice that we use "executeQuery" to get results back

    }
    catch (sql::SQLException e)
    {
        cout << "SQL error. Error message: " << e.what() << endl;
        system("pause");
        exit(1);
    }

// While there are still results (i.e. rows/records) in our result set...
    while (res->next())
    {
        cout << res->getString(1) << endl;
    }

    delete res;
    delete stmt;
    delete dbConn;

    system("pause");
    return 0;
}

Thanks in advance

Answer 1

An INSERT is not a query. Try using executeUpdate() instead of executeQuery().

Replace this line

res = stmt->executeQuery("INSERT INTO "+ table +"(Brand, Model, Power, `Last Used`,`# Times Used`) VALUES('Ferrari','Modena','500','Never',0)"); 

with the following lines (you may need an additional .h file):

sql::PreparedStatement *pstmt;

pstmt = con->prepareStatement("INSERT INTO "+ table +"(Brand, Model, Power, `Last Used`,`# Times Used`) VALUES('Ferrari','Modena','500','Never',0)");
res = pstmt->executeUpdate();
delete pstmt;

Look at the official MySQL example here for an example of the concept.

You may also try using execute(), as shown in this Stackoverflow question. The function execute() is used for generic SQL commands, but may not be as verbose in its return value as more specified functions (it returns a boolean).

Answer 2

Check this:

in line:

res = stmt->executeQuery("INSERT INTO "+ table +"(Brand, Model, Power, `Last Used`,`# Times Used`) VALUES('Ferrari','Modena','500','Never',0)");

You are making a wrong string concatenation, that + (plus) operator don't work that way, that code don't concatenate strings, instead is adding pointers.

Just simply replace this way and try again:

#define TABLE "tbex"// put this in top of cpp file
......
res = stmt->executeQuery("INSERT INTO " TABLE "(Brand, Model, Power, `Last Used`
,`# Times Used`) VALUES('Ferrari','Modena','500','Never',0)");