應用程式串連Babelfish for RDSPostgreSQL執行個體的樣本 - ApsaraDB 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
```
執行結果如下：