cppdb: show tables like query arguments not bound

[dreaming-augustin]

The following:

#include <iostream>
#include <cppdb/frontend.h>

int main() {
        std::string dbcs ="mysql:database=mydb;user=myuser;password=mypassword;@pool_size=10;set_charset_name=utf8";
        auto sql = cppdb::session(dbcs);
        cppdb::result res;
        std::string table("my_table");
        res = sql << "SHOW TABLES LIKE ?" << table;
        res.next();
}

produces:

terminate called after throwing an instance of 'cppdb::mysql_backend::cppdb_myerror' what(): cppdb::mysql::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 '?' at line 1 Aborted

The same code but with a different query (e.g. a SELECT query) can properly bind the arguments, but for some reason, the 'show tables' query above does not work with bound arguments.

Also, could cppdb_myerror output the full query? I checked the cppdb source code and I couldn't see any obvious way to have a more detailed error message being thrown. However, when developing an application, it is sometimes hard to debug code and queries because the exact query which causes to throw is not displayed. Above is a good example of that: the error thrown tell about "the right syntax to use near '?' ", but it does not show the actually query "SHOW TABLES LIKE ?".
How hard would it be for the full query to be thrown together with the error?

[artyom-beilis]

Try to use unprepared statements: http://cppcms.com/sql/cppdb/classcppdb_1_1session.html#a0f366b9d4708fb7674f0637c133fc825

It looks like mysql does not support prepared statement with show tables like

[dreaming-augustin]

Thanks. The code below works.

1) Would you like me to document this? Actually, I am thinking that maybe the error thrown should be more explicit. If MySQL does not support prepared statements on queries like this one, doesn't MySQL report an error when trying to bind an argument? As it is, the error thrown misleadingly mentions an error in the SQL syntax, which is not the case.

2) What about my second point, regarding showing the query when cppdb throws? Not having the query displayed as part of the error message makes it very hard to develop and debug an application.

#include <iostream>
#include <cppdb/frontend.h>

int main() {
        std::string dbcs ="mysql:database=mydb;user=myuser;password=mypassword;@pool_size=10;set_charset_name=utf8";
        auto sql = cppdb::session(dbcs);
        cppdb::result res;
        std::string table("mytable");
        cppdb::statement statement = sql.create_statement("SHOW TABLES LIKE ?");
        statement << table;
        res = statement.query();
        res.next();
        if (res.empty()) {
                std::cout << "The table '" << table << "' does not exist." << std::endl;
        }
        else {
                std::cout << "The table '" << table << "' exists." << std::endl;
        }
}

[artyom-beilis]

Would you like me to document this? It is something that is MySQL specific behavior regarding specific statement. I don't think it needs special treatment.

Actually, I am thinking that maybe the error thrown should be more explicit. If MySQL does not support prepared statements on queries like this one, doesn't MySQL report an error when trying to bind an argument? As it is, the error thrown misleadingly mentions an error in the SQL syntax, which is not the case.

It looks like it fails during preparation (before bind) and the error I get from MySQL the error I return, I don't really look into statement content unless I have to (driver that does not support ? as place holder).

What about my second point, regarding showing the query when cppdb throws? Not having the query displayed as part of the error message makes it very hard to develop and debug an application.

This is good point and actually was discussed before. In general I don't always have the original SQL statement (not this particular case). You should also be careful not to write too much in error. Consider error message like error is SQL select id from users where password='mycoolpass' that goes to some alert.

I suggest open an enhancement request (turned on by connection string parameter to include the original SQL in the thrown error.