文档

应用程序连接示例

更新时间:

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

前提条件

测试数据准备

  1. 连接Babelfish for RDS PostgreSQL实例,更多信息,请参见客户端连接示例

  2. 创建测试数据库。

    create database sqlserverdb;
    说明

    迁移模式为single-db时,只支持创建一个数据库,如果您已创建了一个数据库,则无需再次创建。如何查看迁移模式,请参见查看Babelfish状态

  3. 创建测试表。

    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

操作步骤

  1. 在Windows Server桌面窗口中,使用Win + Q打开搜索,输入cmd,按Enter键,打开命令行终端,输入如下命令创建项目。

    dotnet new console -o <项目名称> -f net6.0

    创建项目

  2. 进入项目所在目录,编辑Program.cs文件。

    项目所在目录可通过步骤1的命令行返回报文获取,本示例为C:\Users\Administrator\MyApp\修改Program.cs文件

  3. 复制如下示例代码,粘贴到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();
            }
    
        }
    }
  4. 进入项目所在目录,编辑MyApp.csproj文件,添加如下依赖。

    <ItemGroup>
        <PackageReference Include="System.Data.SqlClient" Version="4.8.6" />
    </ItemGroup>
  5. 打开命令行终端,切换至项目目录,执行如下命令运行程序。

    cd MyAPP
    dotnet run Program.cs

    执行结果如下:运行结果

Java应用程序连接示例

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

环境准备

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

操作步骤

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

    <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
      <version>9.4.0.jre8</version>
    </dependency>
  2. 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

操作步骤

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

    vim main01.py
  2. 输入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")
  3. Esc键退出编辑状态,然后输入:wq保存并退出。

  4. 使用如下命令,运行程序。

    python3 main01.py

    执行结果如下:Python示例

C语言程序连接示例

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

环境准备

已安装运行所需依赖。

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

操作步骤

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

    vim main01.c
  2. 输入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;
    }
  3. Esc键退出编辑状态,然后输入:wq保存并退出。

  4. 使用如下命令,编译并运行程序。

    gcc main01.c -lsybdb  -o main01
    ./main01

    执行结果如下:C语言示例