c mysql query 错误,C ++ executeQuery（）错误显示表中的MySQL数据

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

#include

#include

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

解决方案

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).