MySQL/C++ and Prepared Statements: setInt always 0

Question

I'm using the MySQL Connector/C++ library to insert values into a database table. I'm following the examples at

http://dev.mysql.com/tech-resources/articles/mysql-connector-cpp.html

almost exactly. However, I can't seem to get prepared statements to work with value placeholders.

  sql::mysql::MySQL_Driver* driver = sql::mysql::MySQL_Driver::Instance();

  boost::shared_ptr<sql::Connection> conn(driver->connect("localhost", "", ""));
  conn->setSchema("TESTDB");

  boost::shared_ptr<sql::Statement> stmt(conn->createStatement());
  stmt->execute("DROP TABLE IF EXISTS TESTTBL");
  stmt->execute("CREATE TABLE TESTTBL (m_id INT)");

  boost::shared_ptr<sql::PreparedStatement> pstmt(conn->prepareStatement("INSERT INTO TESTTBL VALUES(?)"));
  for (int i = 0; i != 10; ++i) {
  pstmt->setInt(1, i);
  pstmt->executeUpdate();     // Always inserts 0.
  }

Any ideas on why I can't bind to the prepared statement? The other set* functions have the same result (e.g. if I use setString I get a string '0' in the resulting row).

Answer 1

I haven't done SQL in a while, but I think you're forgetting to put the column name there. The syntax should be:

INSERT INTO TESTTBL (column_name) VALUES (?)

If you wanted to insert into multiple columns, you'd use comma delimiters:

INSERT INTO TESTTBL (col1, col2, col3) VALUES (?,?,?)

Answer 2

Recompiling the C++ connector from source fixed this problem.

Probably a compiler setting in their pre-built binary that didn't agree with my project. I will talk to MySQL about this.

Thanks for the assistance.