I am running on Linux Mint version 20 and mysql++ (3.2.5-1build1) from the repo. I am using a few templates as:

selFmAttByRef=new Query(conn.query("select * from attachments where reference=%0q"));
selFmIndById=new Query(conn.query( "select * from index_data where id=%0q"));
selFmIndDatRef=new Query(conn.query("select * from index_data where reference = %0q"));
selIDFmIndDatRef=new Query(conn.query("select id from index_data where reference = %0q"));
upDateIndDat=new Query(conn.query("UPDATE `index_data`  SET `recdate`=%0, attachment=%1,`trecdate`=%2, `author`=%3, `tofield`=%4, `unixdate`=%5, `title`=%6 WHERE id=%7"));
xupDateIndDat=new Query(conn.query("UPDATE `index_data`  SET `recdate`=%0Q WHERE id=%0"));
xupDateIndDat->parse(); // all the above queries have parse applied.

All of the select statements have been tested and work fine. The updates, not so much. For instance the call to the xupDateIndDat looks like

mysqlpp::SimpleResult res2 = xupDateIndDat->execute( "b12345", 4);

If I change the update to recdate=%0q or recdate=%0 it still fails:

terminate called after throwing an instance of 'mysqlpp::BadQuery'
  what():  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server 
  version for the right syntax to use near 'b123' at line 1

I tried to add quotes around the parameters like '%0' or around the string like "'b12345'" and that didn't help. The structure looks like:

CREATE TABLE `index_data` (
 `id` int NOT NULL,
 `reference` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
 ...
 `recdate` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
 ...
 `title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Thanks for any advice you might have. I've used this library in the distant past and but must have forgotten something. The original code which also failed looked like this:

bool GenSql::updateIndDat(vector<string> arg) {
  cout <<"here in update"<<endl;
  try {
     mysqlpp::SimpleResult res2 = upDateIndDat->execute( arg[0].c_str(), 1, arg[1].c_str(), arg[2].c_str(),
          arg[3].c_str(), arg[4].c_str(), arg[5].c_str(), arg[6].c_str(), arg[7].c_str());

     return 1;
   } catch (const mysqlpp::BadQuery& er) {
     cerr << "Query error: " << er.what() << endl;
     return -1;
   }

}

I simplified it for testing.

Jim.

Thanks, Stupid mistake I should have seen. I'm pretty familiar with mysqlpp. I used it extensively a few years ago with great success. And I've read through the user manual an some of the reference material. I did look at the examples and other than I used a different method of furnishing the sql to the query object, I don't see the problem. The reference manual seems to support my method.

It still doesn't work. Same error message.

xupDateIndDat=new Query(conn.query("UPDATE `index_data`  SET `recdate`=%0q WHERE id=%1q"));

I stuck the q back in but that didn't help.

Query error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'b1234' at line 1

The original sql is correct. I was just being a bit lazy. The "attachment" field is an int and not null so I had to set it to something. There is not a field in the arg array for the attachment so I elected to just set it to a 1. I guess I could have let it go to default, but I wasn't thinking that way at the time.

attachment int NOT NULL DEFAULT '0',

Here's the arg array.

        vector<string> arg;
        arg.push_back(recdate);
        arg.push_back(trecdate);
        arg.push_back(author);
        arg.push_back(tofield);
        arg.push_back(str); // unixdate 
        arg.push_back(title);
        arg.push_back(str1); // id

As you can see, I didn't have the attachment element in the arg array since it's an unused field in this case. Is there a way to see what the sql generated is? I turned the logging of queries on via mysql config, but it didn't show anything.

Sorry for the insensitive second question, but I just wanted to make sure my first post made it. Jim.

WHERE id=%1q"));

Why are you quoting an int NOT NULL? Should this not be %1?

There is not a field in the arg array for the attachment so I elected to just set it to a 1

Then you're passing 9 parameters into a template query taking 8. It can't do parameter count and type checking for you because:

1. You're not using a type-safe interface. SSQLS and the SQLStream facilities would be safer to use and more likely to flag errors at compile time.

2. Much of this code is generated, creating two sub-problems:

   • Doxygen can't see it, so it isn't in the refman
   • There's an overload for every likely parameter count and ordering, so no matter what you pass, it'll compile. It just fails at runtime when the library code starts digging through the SQLQueryParams object trying to find what it needs to produce the raw query string. This then takes you back to point #1.

Is there a way to see what the sql generated is?

Call str(), passing the same parameters you passed to execute().