Use applications to connect to an ApsaraDB RDS for PostgreSQL instance with Babelfish enabled - ApsaraDB RDS

This topic describes how to use a C#, Java, Python, or C application to connect to an ApsaraDB RDS for PostgreSQL instance for which Babelfish is enabled over the Tabular Data Stream (TDS) port.

Prerequisites

An ApsaraDB RDS for PostgreSQL instance for which Babelfish is enabled is created. For more information, see Create an ApsaraDB RDS for PostgreSQL instance for which Babelfish is enabled.
A Babelfish account is created. For more information, see Manage Babelfish accounts.
A whitelist is configured to allow the server on which your client resides to access the RDS instance. For more information, see Configure an IP address whitelist.
The endpoints and TDS port of the RDS instance are obtained. For more information, see View the endpoints and TDS port.

Preparations

Connect to the RDS instance. For more information, see Use clients to establish connections.
Create a test database.
```
create database sqlserverdb;
```
Note
If you use the Single-DB migration mode, you can create only one database. If you have created a database, you cannot create another database. For more information about migration modes, see View the status of Babelfish.

Create a test table.

USE sqlserverdb
GO

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

Use a C# application to connect to the RDS instance

In this example, the C# application is configured in a Windows Server system. For more information about how to configure the C# application in other operating systems, see the tutorial in official .NETdocumentation.

Environment preparations

.NET 6 SDK (64-bit) is installed. For more information, see Download .NET.

Procedure

In the Windows Server desktop, press Win+Q to open the search box, enter cmd, and then press Enter to open Command Prompt. Then, run the following command to create a project:
```
dotnet new console -o <The name of the project> -f net6.0
```
Go to the directory in which the project resides and edit the Program.cs file.
You can obtain the directory from the command output in Step 1. In this example, the C:\Users\Administrator\MyApp\ directory is used.

Copy the following sample code and paste the code into the Program.cs file.

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;

            //Configure the endpoint, the TDS port, the username and password of the Babelfish account, and the name of the database that are used to connect to the RDS instance. 
            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();
        }

    }
}

Go to the directory in which the project resides and add the following dependencies to the MyApp.csproj file:
```
<ItemGroup>
    <PackageReference Include="System.Data.SqlClient" Version="4.8.6" />
</ItemGroup>
```
Open Command Prompt and switch to the directory in which the project resides. Then, run the following command to run the C# application:
```
cd MyAPP
dotnet run Program.cs
```
Command output:

Use a Java application to connect to the RDS instance

In this example, your Java project is set up by using Maven.

Environment preparations

Java 1.8 or later is installed.

Procedure

Add the following dependencies to the pom.xml file of your Maven project:

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

Connect to the RDS instance over Java Database Connectivity (JDBC).

public class BabelfishDemo {
    public static Connection getRdsPgConnection(){
        //The endpoint that is used to connect to the RDS instance.
        String rdsPgConnStr = "pgm-****.pg.rds.aliyuncs.com";
        //The TDS port.
        String rdsPgPort = "1433";
        //The name of the database.
        String databaseName = "sqlserverdb";
        //The username of the Babelfish account.
        String userName = "babelfish_user";
        //The password of the Babelfish account.
        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.....");
    }
}

Command output:

Use a Python application to connect to the RDS instance

In this example, the Python application is configured in CentOS 7.9.

Environment preparations

The required dependencies are installed.

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

Procedure

Run the vim command to create a file. For example, you can run the following command to create a file named main01.py.
```
vim main01.py
```

Enter i to enter the insert mode. Copy the following sample code and paste the code into the main01.py file.

import sys
import os
import pyodbc


# Configure the endpoint, the TDS port, the username and password of the Babelfish account, and the name of the database that are used to connect to the RDS instance.
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")

Press Esc to exit the insert mode and enter :wq to save and close the file.
Run the following command to run the Python application:
```
python3 main01.py
```
Command output:

Use a C application to connect to the RDS instance

In this example, the C application is configured in CentOS 7.9.

Environment preparations

The required dependencies are installed.

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

Procedure

Run the vim command to create a file. For example, you can run the following command to create a file named main01.c:
```
vim main01.c
```

Enter i to enter the insert mode. Copy the following sample code and paste the code into the main01.c file.

#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"     // The name of the database.
#define  UID       "babelfish_user"  // The username of the Babelfish account.
#define  PWD       "babelfish_pwd"   // The password of the Babelfish account.
#define  DBSERVER  "pgm-*****.pg.rds.aliyuncs.com"   // The endpoint that is used to connect to the RDS instance.
#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;
}

Press Esc to exit the insert mode and enter :wq to save and close the file.
Run the following command to run the C application:
```
gcc main01.c -lsybdb  -o main01
./main01
```
Command output: