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

Prerequisites

  • An account is created for a PolarDB cluster. For more information, see Create database accounts.
  • The IP address of the host that you want to connect to the PolarDB cluster is added to the whitelist of the cluster. For more information, see Configure a whitelist for a cluster.
  • For Windows, make sure that the OS is Windows 7 or later and Service Pack 1 is installed.
  • For a UNIX or Linux OS, make sure that the following tools are installed:
    • Libtool 1.5.10 or later
    • unixODBC-devel

Download the driver

Install an ODBC driver in Windows

  1. Download the installation package of the driver that is suitable for the architecture and version of your OS. For more information, see Download the driver.
  2. Extract the installation package.
  3. Run the install.bat file as an administrator, and then press a random key to exit.
    Install the ODBC driver in Windows
  4. Optional:To uninstall the ODBC driver, run the uninstall.bat file.

Use the ODBC driver to connect to PolarDB from a Windows server

  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, and then click Finish.
  5. In the dialog box that appears, configure the following parameters.
    Logon information
    Parameter Description
    Date Source The name of the data source.
    Description The description of the data source.
    Datebase The name of the database to which you want to connect.
    SSL Mode Specifies whether to enable SSL encryption.
    Server The endpoint of the database. For more information about how to check the endpoint of a PolarDB cluster, see View or apply for an endpoint.
    Prot The port that is used to connect to the database. Default value: 1521.
    User Name The name of the database account.
    Password The password of the database account.
  6. Click Save.

Install the ODBC driver in Unix or Linux

  1. Run the following command to configure the LD_LIBRARY_PATH environment variable:
    export LD_LIBRARY_PATH= <PolarDB-ODBC/lib/ path>
  2. Run the following command to configure the ODBCINI environment variable:
    export export ODBCINI= </your odbc.init file patch>
  3. Download an ODBC driver based on the architecture and version of your OS. For more information, see Download the driver.
  4. Install the ODBC driver.
    Note After you decompress the installation package, you can use the ODBC driver. Run the following command to decompress the package:
    tar -zxvf polardb-odbc.tar.gz
    The following code shows the directory structure:
    ├── include
    ├── lib
    └── samples
    
    6 directories

Use the ODBC driver to connect to PolarDB from a UNIX or Linux server

  1. Install Libtool on the Linux server. The version of Libtool must be 1.5.1 or later.
    yum install -y libtool
  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/polardb-odbc.so
    Setup       = /root/target/lib/unix/libodbcpolardbS.so
    Driver64    = /root/target/lib/unix/polardb-odbc.so
    Setup64     = /root/target/lib/unix/libodbcpolardbS.so
    Database    = <Name of the database>
    Servername  = <Endpoint of the database>
    Password    = <Password>
    Port        = <Port number>
    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 or apply for an endpoint.
    • 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 example shows how to run the Test1 and Test2 files.

  1. Run the following command to decompress the ODBC driver package:
    tar -zxvf polardb-odbc.tar.gz
  2. Run the following command to open the polardb-odbc folder:
    cd polardb-odbc
  3. Open the samples folder in the ODBC driver folder.
    cd samples
  4. Compile the sample test. The following test files are generated: Test1 and Test2.
    make
  5. Run Test1 and Test2.
    ./Test1
    ## Run Test1.
    
    ./Test2
    ## Run Test2.
    Note
    • Test1 contains an example in which add, delete, update, and query operations are performed. Test2 contains an example in which cursors and output parameters are used.
    • The following sample code is only a snippet of the source code. To view the complete source code, 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")   ;