运行任意SQL命令MySQL c++ (X DevAPI)?

Question

我已经将我的c++项目连接到MySQL并成功创建了一个会话。我可以创建一个Schema。我的问题是，当我试图运行简单的任意查询，如USE testSchema SHOW tables;使用MySQL/c++ api，我遇到SQL语法错误。当我直接在MySQL shell中运行该函数时，查询运行得非常好。

这是完整的代码

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();
}

下面是控制台输出:

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

错误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是MySQL错误，这意味着我在查询中有语法错误，但是当我仔细查看查询时，我看到它没有任何问题。

我已经复制并粘贴代码直接从cpp文件到mysql shell，它运行完美。这告诉我，我如何在sql()函数中输入查询的格式出现了问题。但是sql()函数的文档非常简洁，

下面是对sql()函数的引用:https://dev.mysql.com/doc/dev/connector-cpp/8.0/class_session.html#a2e625b5223acd2a3cbc5c02d653a1426

有没有人能告诉我我哪里错了?这里是更多上下文的完整cpp代码:https://pastebin.com/3kQY8THC

窗口10Visual Studio 2019MySQL 8.0 with Connect/c++ X DevAPI

Answer 1

您可以分两步完成:

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;
}

也可以使用Schema::getTables():

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

请记住，Schema::getTables()不显示Schema::createCollection()创建的集合。还有一个Schema::getCollections():

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