应用程序连接Babelfish for RDSPostgreSQL实例的示例 - 云数据库 RDS

您可以通过C#、Java、Python或C语言应用程序连接Babelfish for RDS PostgreSQL实例的TDS端口，实现业务目标。本文介绍这些应用程序的连接示例。

前提条件

已创建Babelfish for RDS PostgreSQL实例。更多信息，请参见创建Babelfish for RDS PostgreSQL实例。
已创建Babelfish账号。更多信息，请参见管理Babelfish账号。
已设置白名单，允许客户端所在服务器访问Babelfish for RDS PostgreSQL实例。更多信息，请参见设置白名单。
获取Babelfish for RDS PostgreSQL实例连接地址及TDS端口。更多信息，请参见查看连接地址及TDS端口号。

测试数据准备

连接Babelfish for RDS PostgreSQL实例，更多信息，请参见客户端连接示例。
创建测试数据库。
```
create database sqlserverdb;
```
说明
迁移模式为single-db时，只支持创建一个数据库，如果您已创建了一个数据库，则无需再次创建。如何查看迁移模式，请参见查看Babelfish状态。

创建测试表。

USE sqlserverdb
GO

CREATE TABLE dbo.tb_test(
    id int not null IDENTITY(1,1) PRIMARY KEY,
    name varchar(50))
GO

C#应用程序连接示例

本文以Windows Server为例配置C#应用程序，其他环境准备请参见.NET教程官方文档。

环境准备

已安装.NET 6 SDK (64-bit)，下载链接请参见Download .NET。

操作步骤

在Windows Server桌面窗口中，使用Win + Q打开搜索，输入cmd，按Enter键，打开命令行终端，输入如下命令创建项目。
```
dotnet new console -o <项目名称> -f net6.0
```
进入项目所在目录，编辑Program.cs文件。
项目所在目录可通过步骤1的命令行返回报文获取，本示例为C:\Users\Administrator\MyApp\。

复制如下示例代码，粘贴到Program.cs文件中。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace sample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Setting up MSSQL Credentials
            SqlConnection con;

            //分别配置Babelfish for RDS PostgreSQL的连接地址、TDS端口号、用户名、密码以及数据库名称。
            string conString = "Server=" + @"pgm-****.pg.rds.aliyuncs.com,1433" + ";" +
                               "User id=" + "babelfish_user" + ";" +
                               "Password=" + "babelfish_pwd" + ";" +
                               "Database=" + "sqlserverdb" + ";" +
                               "MultipleActiveResultSets=true;";

            con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand();

            // Creating MSSQL Connection
            try
            {
                con.Open();
                Console.WriteLine("Connection established\n") ;
            }
            catch
            {
                Console.WriteLine("Can not connect to database!\nPlease check credentials!");
                Environment.Exit(1);
            }

            string sqlQuery = "";

            // Select values example
            select_all(con);

            // Transaction example
            // Insert values into sample table
            cmd = con.CreateCommand();
            SqlTransaction transaction = con.BeginTransaction("SampleTransaction");

            try
            {
                sqlQuery = "insert into dbo.tb_test(name) values(@name)";
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = sqlQuery;
                cmd.Transaction = transaction;

                cmd.Parameters.AddWithValue("@name", "A");

                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@name", "B");

                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@name", "C");

                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@name", "D");

                cmd.ExecuteNonQuery();
                transaction.Commit();

                Console.WriteLine("\nInsert successful!\n");
            }
            catch
            {
                transaction.Rollback();
                Console.WriteLine("\nInsert failed!\n");
            }

            select_all(con);

            // Removing inserted values
            sqlQuery = "delete from dbo.tb_test";
            cmd = con.CreateCommand();
            cmd.CommandText = sqlQuery;

            int row_count = cmd.ExecuteNonQuery();

            // Select metadata
            // Select row count from delete
            Console.WriteLine("\nDeleted rows: " + row_count + "\n");

            // Select column names from table
            sqlQuery = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'dbo.tb_test'";
            cmd = con.CreateCommand();
            cmd.CommandText = sqlQuery;

            SqlDataReader reader = cmd.ExecuteReader();

            string value = "";

            while (reader.Read())
            {
                value += reader.GetValue(0) + " ";
            }

            Console.WriteLine(value);
            reader.Close();

            // Closing connection
            con.Close();
            Console.WriteLine("\nConnection closed!");
        }

        private static void select_all(SqlConnection con)
        {
            string sqlQuery = "select id,name from dbo.tb_test order by id";
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = sqlQuery;
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                string value = "";
                for (int i = 0; i != reader.FieldCount; i++)
                {
                    value += reader.GetValue(i) + " ";
                }
                Console.WriteLine(value);
            }

            reader.Close();
        }

    }
}

进入项目所在目录，编辑MyApp.csproj文件，添加如下依赖。

<ItemGroup>
    <PackageReference Include="System.Data.SqlClient" Version="4.8.6" />
</ItemGroup>

打开命令行终端，切换至项目目录，执行如下命令运行程序。
```
cd MyAPP
dotnet run Program.cs
```
执行结果如下：

Java应用程序连接示例

本文以Maven构建Java项目为例。

环境准备

已安装Java 1.8以上开发环境。

操作步骤

在Maven项目的pom.xml文件中添加依赖。

<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
  <version>9.4.0.jre8</version>
</dependency>

JDBC连接示例。

public class BabelfishDemo {
    public static Connection getRdsPgConnection(){
        //Babelfish for RDS PostgreSQL实例连接地址
        String rdsPgConnStr = "pgm-****.pg.rds.aliyuncs.com";
        //babelfish TDS 端口
        String rdsPgPort = "1433";
        //数据库名称
        String databaseName = "sqlserverdb";
        //Babelfish用户名
        String userName = "babelfish_user";
        //Babelfish用户名的密码
        String password = "babelfish_pwd";

        String connectionUrl = String.format("jdbc:sqlserver://%s:%s;databaseName=%s;user=%s;password=%s;connectTimeout=600;socketTimeout=600", rdsPgConnStr, rdsPgPort, databaseName, userName, password);
        Connection connection = null;
        try{
            connection = DriverManager.getConnection(connectionUrl);
        }
        catch (Exception exception) {
            exception.printStackTrace();
        }
        return connection;
    }

    public static void insertRecord(String name, Connection dbConnObj){
        try{
            PreparedStatement stmt = dbConnObj.prepareStatement("delete from dbo.tb_test;insert into dbo.tb_test(name) values(?)");
            stmt.setString(1, name);
            stmt.execute();
        } catch (Exception exception){
            exception.printStackTrace();
        }
    }

    public static void queryDataRecords(Connection dbConnObj){
        try (Statement stmt = dbConnObj.createStatement()) {
            String SQL = "select * from dbo.tb_test order by id;";
            ResultSet rs = stmt.executeQuery(SQL);

            // Iterate through the data in the result set and display it.
            while (rs.next()) {
                System.out.println(rs.getString("id") + " " + rs.getString("name"));
            }
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        System.out.println("Babelfish Demo startd.....");

        // get connection
        Connection dbConnObj = getRdsPgConnection();

        // write record to db
        insertRecord("B", dbConnObj);

        // query data
        queryDataRecords(dbConnObj);

        System.out.println("Babelfish Demo Touchdown.....");
    }
}

执行结果如下： Java执行结果

Python应用程序连接示例

本文以CentOS 7.9为例配置Python应用程序。

环境准备

已安装运行所需依赖。

sudo yum install gcc gcc-c++ -y

sudo wget https://packages.microsoft.com/config/centos/7/packages-microsoft-prod.rpm

sudo rpm -ivh packages-microsoft-prod.rpm

sudo yum install msodbcsql17.x86_64 -y

sudo yum install unixODBC-devel

sudo pip3 install pyodbc

操作步骤

使用vim命令创建文件并编辑，以创建main01.py文件为例。
```
vim main01.py
```

输入i，进入编辑状态，复制如下示例代码，粘贴到main01.py文件中。

import sys
import os
import pyodbc


# 设置Babelfish for RDS PostgreSQL实例连接地址、TDS端口号、数据库名称、用户名、密码
server = 'pgm-*****.pg.rds.aliyuncs.com,1433'
database = 'sqlserverdb'
username = 'babelfish_user'
password = 'babelfish_pwd'


# Trying to establish connection
connection, cursor = None, None
try:
    connection = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
    cursor = connection.cursor()
    print("Connection established for select examples!\n")
except pyodbc.ProgrammingError:
    print("Cannot connect to the database!\nPlease check credentials!")
    exit(1)


sql = "insert into dbo.tb_test(name) values('A'),('B'),('C'),('D')"
cursor.execute(sql)
# Select values
cursor.execute("select id,name from dbo.tb_test order by id")
for row in cursor.fetchall():
    print(row)

sql = "delete from dbo.tb_test"
cursor.execute(sql)

cursor.close()
connection.close()
print("\nsuccess!\n")

按Esc键退出编辑状态，然后输入:wq保存并退出。
使用如下命令，运行程序。
```
python3 main01.py
```
执行结果如下：

C语言程序连接示例

本文以CentOS 7.9为例配置C语言应用程序。

环境准备

已安装运行所需依赖。

sudo yum install freetds freetds-devel unixODBC-devel -y

操作步骤

使用vim命令创建文件并编辑，以创建main01.c文件为例。
```
vim main01.c
```

输入i，进入编辑状态，复制如下示例代码，粘贴到main01.c文件中。

#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <sys/param.h>
#include <sybfront.h>
#include <sybdb.h>
#include <syberror.h>


#define  DBNAME    "sqlserverdb"     // 数据库名称
#define  UID       "babelfish_user"  // Babelfish用户名
#define  PWD       "babelfish_pwd"   // Babelfish用户名的密码
#define  DBSERVER  "pgm-*****.pg.rds.aliyuncs.com"   //Babelfish for RDS PostgreSQL实例的连接地址
#define  TDSPORT   1433


/* handler from messages from the server */
static int
msg_handler(DBPROCESS* dbproc, DBINT msgno, int msgstate, int severity,
    char *msgtext, char *srvname, char *procname, int line)
{
    /* regular errors are handled by the error handler */
    if     (severity < 11)
            fprintf(stderr, "Server message (severity %d): %s\n", severity, msgtext);

    return 0;
}

/* error handler */
static int err_handler(DBPROCESS* dbproc, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr)
{
    fprintf(stderr, "Server error %d: %s\n", dberr, dberrstr);
    if     (oserr != 0)
            fprintf(stderr, "Caused by system error %d: %s\n", oserr, oserrstr);

    return INT_CANCEL;
}

int main(void)
{
      LOGINREC     *login;
      DBPROCESS     *dbconn;
      char         hostname[MAXHOSTNAMELEN];
      int         max_len = MAXHOSTNAMELEN;
      DBCHAR         accession[10];
      DBCHAR         examdesc[10];
      DBCHAR         examcode[255];
    char         portstr[20];
    int         rc;

    char        sql[65535];


      if (dbinit() == FAIL)
    {
            fprintf(stderr, "Could not init db.\n");
            return 1;
      }

      /* Allocate a login params structure */
    if     ((login = dblogin()) == FAIL)
    {
        fprintf(stderr, "Could not initialize dblogin() structure.\n");
            return 2;
      }

    /* Initialize the login params in the structure */
    DBSETLUSER(login, UID);
    DBSETLPWD(login, PWD);
    if     (gethostname(hostname, max_len) == 0)
    {
            DBSETLHOST(login, hostname);
        fprintf(stderr, "setting login hostname: %s\n", hostname);
    }

    /* the port can only be set via environment variable */
    rc = snprintf(portstr, 20, "TDSPORT=%d", TDSPORT);
    if     (rc < 0 || rc >= 20)
    {
            fprintf(stderr, "error composing string for environment variable TDSPORT\n");
            return 0;
    }

    if     (putenv(portstr) != 0)
    {
            fprintf(stderr, "error setting TDSPORT environment variable\n");
            return 0;
    }

    /* install error handler */
    dberrhandle(err_handler);
    dbmsghandle(msg_handler);

    /* Now connect to the DB Server */
    if     ((dbconn = dbopen(login, DBSERVER)) == NULL)
    {
        fprintf(stderr, "Could not connect to DB Server: %s\n", DBSERVER);
            return 3;
    }

    /* Use database which you want to operate */
    if  (dbuse(dbconn, DBNAME) == FAIL)
    {
        fprintf(stderr, "Could not use database: %s\n", DBNAME);
            return 4;
    }

    /* Prepare sql */
    snprintf(sql, 65535, "insert into dbo.tb_test(name) values('A'),('B'),('C'),('D')");
    if  (dbcmd(dbconn, sql) == FAIL)
    {
        fprintf(stderr, "Could not prepare sql: %s\n", sql);
            return 5;
    }

    /* Execute sql */
    if  (dbsqlexec(dbconn) == FAIL)
    {
           fprintf(stderr, "Could not execute sql: %s\n", sql);
            return 6;
    }

    /* Judge sql execute result */
    if  (dbresults(dbconn) != SUCCEED)
    {
           fprintf(stderr, "Could not execute sql: %s\n", sql);
            return 7;
    }

    /* Prepare sql */
    snprintf(sql, 65535, "select id,name from dbo.tb_test order by id");
    if  (dbcmd(dbconn, sql) == FAIL)
    {
        fprintf(stderr, "Could not prepare sql: %s\n", sql);
            return 8;
    }

    /* Execute sql */
    if  (dbsqlexec(dbconn) == FAIL)
    {
           fprintf(stderr, "Could not execute sql: %s\n", sql);
            return 9;
    }

    /* Fetch sql execute result */
    int             retcode;
    char            id[65535];
    char            name[65535];

    if  ((retcode = dbresults(dbconn)) != NO_MORE_RESULTS && retcode == SUCCEED)
    {
        dbbind(dbconn, 1, CHARBIND, (DBCHAR)0, (BYTE*)id);
        dbbind(dbconn, 2, CHARBIND, (DBCHAR)0, (BYTE*)name);
        while (dbnextrow(dbconn) != NO_MORE_ROWS)
        {
            printf("id: %s, name: %s\n", id, name);
        }
    }  else
    {
        fprintf(stderr, "Could not fetch result for sql: %s\n", sql);
            return 10;
    }

    /* Prepare sql */
    snprintf(sql, 65535, "delete from dbo.tb_test");
    if  (dbcmd(dbconn, sql) == FAIL)
    {
        fprintf(stderr, "Could not prepare sql: %s\n", sql);
            return 11;
    }

    /* Execute sql */
    if  (dbsqlexec(dbconn) == FAIL)
    {
           fprintf(stderr, "Could not execute sql: %s\n", sql);
            return 12;
    }

    /* Judge sql execute result */
    if  (dbresults(dbconn) != SUCCEED)
    {
           fprintf(stderr, "Could not execute sql: %s\n", sql);
            return 13;
    }

    /* Close the connection */
    dbclose(dbconn);

    printf("success\n");
    return 0;
}

按Esc键退出编辑状态，然后输入:wq保存并退出。
使用如下命令，编译并运行程序。
```
gcc main01.c -lsybdb  -o main01
./main01
```
执行结果如下：