Azure Database for MySQL:使用 Connector/C++ 连接和查询数据Azure Database for MySQL: Use Connector/C++ to connect and query data

备注

将要查看的是 Azure Database for MySQL 的新服务。You are viewing the new service of Azure Database for MySQL. 若要查看经典 MySQL Database for Azure 的文档,请访问此页To view the documentation for classic MySQL Database for Azure, please visit this page.

本快速入门演示如何使用 C++ 应用程序连接到 Azure Database for MySQL。This quickstart demonstrates how to connect to an Azure Database for MySQL by using a C++ application. 同时还介绍了如何使用 SQL 语句在数据库中查询、插入、更新和删除数据。It shows how to use SQL statements to query, insert, update, and delete data in the database. 本主题假设你熟悉如何使用 C++ 进行开发,但不太熟悉 Azure Database for MySQL 的用法。This topic assumes you're familiar with developing using C++ and you're new to working with Azure Database for MySQL.

先决条件Prerequisites

此快速入门使用以下任意指南中创建的资源作为起点:This quickstart uses the resources created in either of the following guides as a starting point:

还需要:You also need to:

重要

确保已使用 Azure 门户Azure CLI 将服务器的防火墙规则添加到连接的 IP 地址Ensure the IP address you're connecting from has been added the server's firewall rules using the Azure portal or Azure CLI

安装 Visual Studio 和 .NETInstall Visual Studio and .NET

本部分的步骤假定你熟悉如何使用 .NET 进行开发。The steps in this section assume that you're familiar with developing using .NET.

WindowsWindows

  • 安装 Visual Studio 2019 Community。Install Visual Studio 2019 Community. Visual Studio 2019 Community 是一个功能齐全、可扩展的免费 IDE。Visual Studio 2019 Community is a full featured, extensible, free IDE. 使用此 IDE,你可以为 Android、iOS、Windows、Web 和数据库应用程序以及云服务创建新式应用程序。With this IDE, you can create modern applications for Android, iOS, Windows, web and database applications, and cloud services. 可安装完整的 .NET Framework 或仅 .NET Core:快速入门中的代码片段二者都可以使用。You can install either the full .NET Framework or just .NET Core: the code snippets in the Quickstart work with either. 如果已在计算机上安装了 Visual Studio,请跳过下面两个步骤。If you already have Visual Studio installed on your computer, skip the next two steps.
    1. 下载 Visual Studio 2019 安装程序Download the Visual Studio 2019 installer.
    2. 运行该安装程序,并遵照安装提示完成安装。Run the installer and follow the installation prompts to complete the installation.

配置 Visual StudioConfigure Visual Studio

  1. 从 Visual Studio 的“项目”->“属性”->“链接器”->“常规”>“其他库目录”中,添加 C++ 连接器的“lib\opt”目录(例如:C:\Program Files (x86)\MySQL\MySQL Connector C++ 1.1.9\lib\opt)。From Visual Studio, Project -> Properties -> Linker -> General > Additional Library Directories, add the "\lib\opt" directory (for example: C:\Program Files (x86)\MySQL\MySQL Connector C++ 1.1.9\lib\opt) of the C++ connector.
  2. 在 Visual Studio 的“项目”->“属性”->“C/C++”->“常规”->“其他 Include 目录”中:From Visual Studio, Project -> Properties -> C/C++ -> General -> Additional Include Directories:
    • 添加 c++ 连接器的“\include”目录(例如:C:\Program Files (x86)\MySQL\MySQL Connector C++ 1.1.9\include))。Add the "\include" directory of c++ connector (for example: C:\Program Files (x86)\MySQL\MySQL Connector C++ 1.1.9\include).
    • 添加 Boost 库的根目录(例如:C:\boost_1_64_0))。Add the Boost library's root directory (for example: C:\boost_1_64_0).
  3. 在 Visual Studio 的“项目”->“属性”->“链接器”->“输入”->“其他依赖项”中,将 mysqlcppconn.lib 添加到文本字段。From Visual Studio, Project -> Properties -> Linker -> Input > Additional Dependencies, add mysqlcppconn.lib into the text field.
  4. mysqlcppconn.dll 从步骤 3 中的 C++ 连接器库文件夹复制到应用程序可执行文件所在的目录,或者将其添加到环境变量,方便应用程序找到它。Either copy mysqlcppconn.dll from the C++ connector library folder in step 3 to the same directory as the application executable or add it to the environment variable so your application can find it.

获取连接信息Get connection information

获取连接到 Azure Database for MySQL 所需的连接信息。Get the connection information needed to connect to the Azure Database for MySQL. 需要完全限定的服务器名称和登录凭据。You need the fully qualified server name and login credentials.

  1. 登录到 Azure 门户Sign in to the Azure portal.
  2. 在 Azure 门户的左侧菜单中,单击“所有资源”,然后搜索已创建的服务器(例如 mydemoserver)。From the left-hand menu in Azure portal, click All resources, and then search for the server you have created (such as mydemoserver).
  3. 单击服务器名称。Click the server name.
  4. 从服务器的“概览”面板中记下“服务器名称”和“服务器管理员登录名”。 From the server's Overview panel, make a note of the Server name and Server admin login name. 如果忘记了密码,也可通过此面板来重置密码。If you forget your password, you can also reset the password from this panel. Azure Database for MySQL 服务器名称Azure Database for MySQL server name

进行连接,创建表,然后插入数据Connect, create table, and insert data

通过以下代码进行连接,然后使用 CREATE TABLE 和 INSERT INTO SQL 语句加载数据。Use the following code to connect and load the data by using CREATE TABLE and INSERT INTO SQL statements. 代码使用 sql::Driver 类,通过 connect() 方法建立到 MySQL 的连接。The code uses sql::Driver class with the connect() method to establish a connection to MySQL. 然后,代码使用 createStatement() 和 execute() 方法运行数据库命令。Then the code uses method createStatement() and execute() to run the database commands.

替换 Host、DBName、User 和 Password 参数。Replace the Host, DBName, User, and Password parameters. 可以将这些参数替换为你在创建服务器和数据库时指定的值。You can replace the parameters with the values that you specified when you created the server and database.

#include <stdlib.h>
#include <iostream>
#include "stdafx.h"

#include "mysql_connection.h"
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/prepared_statement.h>
using namespace std;

//for demonstration only. never save your password in the code!
const string server = "tcp://yourservername.mysql.database.chinacloudapi.cn:3306";
const string username = "username@servername";
const string password = "yourpassword";

int main()
{
    sql::Driver *driver;
    sql::Connection *con;
    sql::Statement *stmt;
    sql::PreparedStatement *pstmt;

    try
    {
        driver = get_driver_instance();
        con = driver->connect(server, username, password);
    }
    catch (sql::SQLException e)
    {
        cout << "Could not connect to server. Error message: " << e.what() << endl;
        system("pause");
        exit(1);
    }

    //please create database "quickstartdb" ahead of time
    con->setSchema("quickstartdb");

    stmt = con->createStatement();
    stmt->execute("DROP TABLE IF EXISTS inventory");
    cout << "Finished dropping table (if existed)" << endl;
    stmt->execute("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);");
    cout << "Finished creating table" << endl;
    delete stmt;

    pstmt = con->prepareStatement("INSERT INTO inventory(name, quantity) VALUES(?,?)");
    pstmt->setString(1, "banana");
    pstmt->setInt(2, 150);
    pstmt->execute();
    cout << "One row inserted." << endl;

    pstmt->setString(1, "orange");
    pstmt->setInt(2, 154);
    pstmt->execute();
    cout << "One row inserted." << endl;

    pstmt->setString(1, "apple");
    pstmt->setInt(2, 100);
    pstmt->execute();
    cout << "One row inserted." << endl;

    delete pstmt;
    delete con;
    system("pause");
    return 0;
}

读取数据Read data

使用以下代码进行连接,并使用 SELECT SQL 语句读取数据。Use the following code to connect and read the data by using a SELECT SQL statement. 代码使用 sql::Driver 类,通过 connect() 方法建立到 MySQL 的连接。The code uses sql::Driver class with the connect() method to establish a connection to MySQL. 然后,代码使用 prepareStatement() 和 executeQuery() 方法运行 select 命令。Then the code uses method prepareStatement() and executeQuery() to run the select commands. 然后,代码使用 next() 转到结果中的记录。Next, the code uses next() to advance to the records in the results. 最后,代码使用 getInt() 和 getString() 分析记录中的值。Finally, the code uses getInt() and getString() to parse the values in the record.

替换 Host、DBName、User 和 Password 参数。Replace the Host, DBName, User, and Password parameters. 可以将这些参数替换为你在创建服务器和数据库时指定的值。You can replace the parameters with the values that you specified when you created the server and database.

#include <stdlib.h>
#include <iostream>
#include "stdafx.h"

#include "mysql_connection.h"
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/prepared_statement.h>
using namespace std;

//for demonstration only. never save your password in the code!
const string server = "tcp://yourservername.mysql.database.chinacloudapi.cn:3306";
const string username = "username@servername";
const string password = "yourpassword";

int main()
{
    sql::Driver *driver;
    sql::Connection *con;
    sql::PreparedStatement *pstmt;
    sql::ResultSet *result;

    try
    {
        driver = get_driver_instance();
        //for demonstration only. never save password in the code!
        con = driver->connect(server, username, password);
    }
    catch (sql::SQLException e)
    {
        cout << "Could not connect to server. Error message: " << e.what() << endl;
        system("pause");
        exit(1);
    }

    con->setSchema("quickstartdb");

    //select  
    pstmt = con->prepareStatement("SELECT * FROM inventory;");
    result = pstmt->executeQuery();

    while (result->next())
        printf("Reading from table=(%d, %s, %d)\n", result->getInt(1), result->getString(2).c_str(), result->getInt(3));

    delete result;
    delete pstmt;
    delete con;
    system("pause");
    return 0;
}

更新数据Update data

使用以下代码进行连接,并使用 UPDATE SQL 语句读取数据。Use the following code to connect and read the data by using an UPDATE SQL statement. 代码使用 sql::Driver 类,通过 connect() 方法建立到 MySQL 的连接。The code uses sql::Driver class with the connect() method to establish a connection to MySQL. 然后,代码使用 prepareStatement() 和 executeQuery() 方法运行 update 命令。Then the code uses method prepareStatement() and executeQuery() to run the update commands.

替换 Host、DBName、User 和 Password 参数。Replace the Host, DBName, User, and Password parameters. 可以将这些参数替换为你在创建服务器和数据库时指定的值。You can replace the parameters with the values that you specified when you created the server and database.

#include <stdlib.h>
#include <iostream>
#include "stdafx.h"

#include "mysql_connection.h"
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/prepared_statement.h>
using namespace std;

//for demonstration only. never save your password in the code!
const string server = "tcp://yourservername.mysql.database.chinacloudapi.cn:3306";
const string username = "username@servername";
const string password = "yourpassword";

int main()
{
    sql::Driver *driver;
    sql::Connection *con;
    sql::PreparedStatement *pstmt;

    try
    {
        driver = get_driver_instance();
        //for demonstration only. never save password in the code!
        con = driver->connect(server, username, password);
    }
    catch (sql::SQLException e)
    {
        cout << "Could not connect to server. Error message: " << e.what() << endl;
        system("pause");
        exit(1);
    }
    
    con->setSchema("quickstartdb");

    //update
    pstmt = con->prepareStatement("UPDATE inventory SET quantity = ? WHERE name = ?");
    pstmt->setInt(1, 200);
    pstmt->setString(2, "banana");
    pstmt->executeQuery();
    printf("Row updated\n");

    delete con;
    delete pstmt;
    system("pause");
    return 0;
}

删除数据Delete data

使用以下代码进行连接,并使用 DELETE SQL 语句读取数据。Use the following code to connect and read the data by using a DELETE SQL statement. 代码使用 sql::Driver 类,通过 connect() 方法建立到 MySQL 的连接。The code uses sql::Driver class with the connect() method to establish a connection to MySQL. 然后,代码使用 prepareStatement() 和 executeQuery() 方法运行 delete 命令。Then the code uses method prepareStatement() and executeQuery() to run the delete commands.

替换 Host、DBName、User 和 Password 参数。Replace the Host, DBName, User, and Password parameters. 可以将这些参数替换为你在创建服务器和数据库时指定的值。You can replace the parameters with the values that you specified when you created the server and database.

#include <stdlib.h>
#include <iostream>
#include "stdafx.h"

#include "mysql_connection.h"
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/prepared_statement.h>
using namespace std;

//for demonstration only. never save your password in the code!
const string server = "tcp://yourservername.mysql.database.chinacloudapi.cn:3306";
const string username = "username@servername";
const string password = "yourpassword";

int main()
{
    sql::Driver *driver;
    sql::Connection *con;
    sql::PreparedStatement *pstmt;
    sql::ResultSet *result;

    try
    {
        driver = get_driver_instance();
        //for demonstration only. never save password in the code!
        con = driver->connect(server, username, password);
    }
    catch (sql::SQLException e)
    {
        cout << "Could not connect to server. Error message: " << e.what() << endl;
        system("pause");
        exit(1);
    }
    
    con->setSchema("quickstartdb");
        
    //delete
    pstmt = con->prepareStatement("DELETE FROM inventory WHERE name = ?");
    pstmt->setString(1, "orange");
    result = pstmt->executeQuery();
    printf("Row deleted\n");    
    
    delete pstmt;
    delete con;
    delete result;
    system("pause");
    return 0;
}

后续步骤Next steps