Running arbitrary SQL commands MySQL C++ (X DevAPI)?

Question

I've connected my C++ project to MySQL and successfully created a session. I was able to create a Schema. My issue is that when I try to run simple arbitrary queries like USE testSchema SHOW tables; using the MySQL/C++ api, I run into SQL syntax errors. When I run the function directly in the MySQL shell, the query runs perfectly fine.

Here is the full code

const char* url = (argc > 1 ? argv[1] : "mysqlx://pct@127.0.0.1");
cout << "Creating session on " << url << " ..." << endl;

Session sess(url);

{
    cout << "Connected!" << endl;

    // Create the Schema "testSchema"; This code creates a schema without issue
    cout << "Creating Schema..." << endl;
    sess.dropSchema("testSchema");
    Schema mySchema = sess.createSchema("testSchema");
    cout << "Schema Created!" << endl;


    // Create the Table "testTable"; This code runs like normal, but the schema doesn't show
    cout << "Creating Table with..." << endl;
    SqlStatement sqlcomm = sess.sql("USE testSchema SHOW tables;");
    sqlcomm.execute();
}

Here is the console output:

Creating session on mysqlx://pct@127.0.0.1 ...
Connected!
Creating Schema...
Schema Created!
Creating Table with...
MYSQL ERROR: CDK 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 'SHOW tables' at line 1

The 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 'SHOW tables' at line 1 is a MySQL error that means I have a syntax error in the query, but when I take a closer look at a query, I see there is nothing wrong with it.

I've copied and pasted the code directly from the cpp file into the mysql shell and it runs perfectly. This tells me that something is up with the formatting of how I'm entering the query in the sql() function. But the documentation for the sql() function is really terse and.

Here is the reference to the sql() function: https://dev.mysql.com/doc/dev/connector-cpp/8.0/class_session.html#a2e625b5223acd2a3cbc5c02d653a1426

Can someone please give me some insight on where I'm going wrong? Also here here is the full cpp code for more context:https://pastebin.com/3kQY8THC

Windows 10 Visual Studio 2019 MySQL 8.0 with Connect/C++ X DevAPI

Answer 1

You can do it in two steps:

sess.sql("USE testSchema").execute();

SqlStatement sqlcomm = sess.sql("SHOW tables");
SqlResult res = sqlcomm.execute();
for(auto row : res)
{
   std::cout << row.get(0).get<std::string>() << std::endl;
}

Also, you can use the Schema::getTables():

for(auto table : mySchema.getTables())
{
  std::cout << table.getName() << std::endl;
}

Keep in mind that the Schema::getTables() doesn't show the Collections created by Schema::createCollection(). There is also a Schema::getCollections():

for(auto collection : mySchema.getCollections())
{
  std::cout << collection.getName() << std::endl;
}