This topic describes how to use the Open Database Connectivity (ODBC) driver to connect a Unix or Windows application to a PolarDB-O cluster.

Prerequisites

  • You have created an account for an ApsaraDB for PolarDB cluster. For more information about how to create an account, see Create database accounts.
  • You have added the IP address of the host that you want to connect to the ApsaraDB for PolarDB cluster to the whitelist. For more information, see Configure a whitelist for a cluster.
  • The Windows system is Windows 7 or later and the Windows 7 service pack 1 is installed.
  • For Unix or Linux systems, you must install the following tools:
    • Libtool 1.5.10 or later.
    • unixODBC-devel
    • libpq

Download drivers

Install an ODBC driver in Windows

  1. Select a driver based on the system type of your Windows operating system. You can download the package from Download drivers.
  2. Decompress the ODBC package after you download it.
  3. Run the install.bat file as Administrator, and then press any key to exit after the driver is installed.
    Install an ODBC driver in Windows
  4. Optional:To uninstall the ODBC package, run the uninstall.bat file.

Use an ODBC driver to connect to PolarDB from Windows

  1. In Control Panel, open Administrative Tools.
  2. On the Administrative Tools page, find and run ODBC Data Sources.
  3. In the ODBC Data Source Administrator dialog box, click Add.
  4. Select POLARDB (UNICODE) as the driver for the data source, click Finish.
  5. In the dialog box that appears, specify the following parameters.
    WIN ODBC登录信息
    Parameter Description
    Date Source The name of the data source.
    Description The description of the data source.
    Database The name of the target database.
    SSL Mode Specify SSL encryption.
    Server The endpoint of the target database. For more information about how to view the endpoint of a PolarDB cluster, see View endpoints.
    Prot The port number of the target database. Default value: 1521.
    User Name The database account.
    Password The password of the database account.
  6. Click Save.

Install an ODBC driver in Unix or Linux

  1. Download an ODBC driver based on the architecture and version of the operating system. For more information, see Download drivers.
  2. Install the ODBC driver
    Note PolarDB provides an ODBC driver package. You can decompress the package and use the ODBC driver without installation. Run the following command to decompress the package:
    tar -zxvf polardb_oracle_odbc.tar.gz

Use ODBC to connect to PolarDB from Unix or Linux

  1. Install Libtool on the Linux server. The version of Libtool must be 1.5.1 or later.
    yum install -y 
  2. Install unixODBC-devel on the Linux server.
    yum install -y unixODBC-devel
  3. Edit the odbcinst.ini file in the /etc directory.
    vim /etc/odbcinst.ini
  4. Add the following content to the odbcinst.ini file.
    [POLARDB]
    Description = ODBC for POLARDB
    Driver      = /root/target/lib/unix/polar-odbc.so
    Setup       = /root/target/lib/unix/libodbcpolarS.so
    Driver64    = /root/target/lib/unix/polar-odbc.so
    Setup64     = /root/target/lib/unix/libodbcpolarS.so
    Database    = <The name of the database>
    Servername  = <Endpoint of the PolarDB cluster>
    Password    = <Password>
    Port        = <Port>
    Username    = <Username>
    Trace       = yes
    TraceFile   = /tmp/odbc.log
    FileUsage   = 1
    Note
    • For more information about how to view the endpoint of a PolarDB cluster, see View endpoints.
    • In the preceding sample code, replace /root with the actual path of the target folder.
  5. Connect to PolarDB.
    $isql -v POLARDB
    +---------------------------------------+
    | Connected!                            |
    |                                       |
    | sql-statement                         |
    | help [tablename]                      |
    | quit                                  |
    |                                       |
    +---------------------------------------+
    SQL>

Examples for Unix or Linux

The following examples show how to run the Test1 and Test2 files.

  1. Open the samples folder in the ODBC driver folder.
    cd samples
  2. Compile the sample test. The following test files are generated: Test1 and Test2.
    make
  3. Run Test1 and Test2.
    . /Test1
    ## Run Test1
    
    . /Test2
    ## Run Test2
    Note
    • Test1 contains the example to perform add, delete, update, and query operations. Test2 contains the example of cursors and output parameters.
    • The following sample code is only a snippet of the source code. To view the complete source code, you can check the Test1 and Test2 files in the samples folder of the ODBC driver package.

Sample code of Test1:

... 

int main(int argc, char* argv[])
{
    /*Initialization*/
    RETCODE rCode;
    HENV *hEnv = (HENV*)malloc(sizeof(HENV));
    HDBC *hDBC = (HDBC*)malloc(sizeof(HDBC));
    HSTMT *hStmt = (HSTMT*)malloc(sizeof(HSTMT));
    Connect("POLARDB","user","",&hEnv,&hDBC);
    rCode = SQLAllocStmt(*hDBC,hStmt);
    rCode = SQLAllocHandle(SQL_HANDLE_STMT,*hDBC,hStmt);
    /*Add, delete, modify, and query operations*/
    ExecuteInsertStatement(&hStmt,(UCHAR*) "INSERT INTO EMP(EMPNO,ENAME) VALUES((SELECT COUNT(EMPNO) FROM EMP),'JACKSON')");
    ExecuteUpdate(&hStmt,(UCHAR*) "UPDATE EMP SET ENAME='ODBC Test' WHERE EMPNO < 100");
    ExecuteDeletStatement(&hStmt,(UCHAR*) "DELETE FROM EMP WHERE EMPNO<100");
    ExecuteSimple_Select(&hStmt,(UCHAR*) "SELECT EMPNO,ENAME,JOB,MGR,HIREDATE FROM EMP where empno = 7369");
    /*Disconnection*/
    Disconnect(&hEnv,&hDBC,&hStmt);
    /*clean up*/
    free(hEnv);
    free(hDBC);
    free(hStmt);

    return 0;
}

Sample code for Test2:

int main(int argc, char* argv[])
{
    /*Definition*/
    RETCODE rCode;
    SQLUSMALLINT a;
    SQLINTEGER Num1IndOrLen;
    SQLSMALLINT iTotCols = 0;

    int j;
    SDWORD cbData;
    /*Initialization*/
    HENV *hEnv = (HENV*)malloc(sizeof(HENV));
    HDBC *hDBC = (HDBC*)malloc(sizeof(HDBC));
    HSTMT *hStmt = (HSTMT*)malloc(sizeof(HSTMT));
    HSTMT *hStmt1 = (HSTMT*)malloc(sizeof(HSTMT));
    /**Establish a connection**/
    Connect("POLARDB","user","***",&hEnv,&hDBC);
    rCode = SQLAllocStmt(*hDBC,hStmt);
    rCode = SQLAllocStmt(*hDBC,hStmt1);

    rCode = SQLAllocHandle(SQL_HANDLE_STMT,*hDBC,hStmt);
    rCode = SQLAllocHandle(SQL_HANDLE_STMT,*hDBC,hStmt1);
    /*begin*/
    ExecuteSimple_Select(&hStmt1,(UCHAR*) "BEGIN;");
    /*prepare*/
    RETCODE rc = SQLPrepare((*hStmt),(SQLCHAR*)"{ call refcur_inout_callee2(?,?)}",SQL_NTS);

    rc = SQLBindParameter((*hStmt),1, SQL_PARAM_INPUT_OUTPUT, SQL_C_CHAR,SQL_REFCURSOR,0, 31,
                strName, 31, &Num1IndOrLen);
    rc = SQLBindParameter((*hStmt),2, SQL_PARAM_INPUT_OUTPUT, SQL_C_CHAR,SQL_REFCURSOR,0, 31,
                    &strName1, 31, &Num1IndOrLen);

    Num1IndOrLen=0;
    /*execute*/
    rc = SQLExecute((*hStmt));

    if(rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
    {
        printf("\nstrName _________ = %s\n",strName);
        printf("\nstrName 1_________ = %s\n",strName1);


    }
    printf("\n First Cursor as OUT Parameter \n")   ;