All Products
Search
Document Center

PolarDB:ODBC

Last Updated:Jul 25, 2023

This topic describes how to use the Open Database Connectivity (ODBC) driver to connect a UNIX or Windows application to a PolarDB for PostgreSQL(Compatible with Oracle) cluster.

Prerequisites

  • An account is created for your PolarDB for MySQL cluster. For more information, see Create database accounts.
  • The IP address of the host that you want to connect to the PolarDB for MySQL 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.

    • PolarTools. For more information about the download and installation, see Overview.

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.

    Parameter

    Description

    Date Source

    The name of the data source.

    Description

    The description of the data source.

    Datebase

    The name of the cluster.

    SSL Mode

    Specifies whether to enable SSL-based 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 username 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 ODBCINI=</your odbc.init file path>
  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. Modify the odbc.ini file that you configure.

    vim $ODBCINI 
  4. Add the following content to the odbc.ini file:

    [POLARDB]
    Description = ODBC for POLARDB
    Driver      = /home/user/target/lib/unix/polardb-odbc.so
    Driver64    = /home/user/target/lib/unix/polardb-odbc.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.

    • Replace /home/user in the preceding example 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.

Create the following table schema in a PolarDB database in advance and insert the following data:

create table emp(empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number (4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2));

insert into emp values(7369,'smith','clerk',7902 ,to_date('17-12-1980','dd-mm-yyyy'),800,null, 20 );
insert into emp values(7499,'allen','salesman',7698 ,to_date('20-2-1981','dd-mm-yyyy'),1600, 300 , 30 );
insert into emp values(7521,'ward' ,'salesman' ,7698 ,to_date(' 22-2-1981' , 'dd-mm-yyyy' ), 1250 , 500 , 30 );
insert into emp values(7566,'jones','manager' , 7839 ,to_date(' 2-4-1981' , 'dd-mm-yyyy' ), 2975 , null , 20 );
insert into emp values(7654,'martin','salesman',7698 ,to_date(' 28-9-1981' , 'dd-mm-yyyy' ), 1250 , 1400 , 30 );
insert into emp values(7698,'blake','manager', 7839 ,to_date(' 1-5-1981' , 'dd-mm-yyyy' ), 2850 , null , 30 );
insert into emp values(7782,'clark','manager', 7839 ,to_date(' 9-6-1981' , 'dd-mm-yyyy' ), 2450 , null , 10 );
insert into emp values(7788,'scott','analyst', 7566 ,to_date(' 13-7-1987' , 'dd-mm-rr ' ) - 85 , 3000 , null , 20 );
insert into emp values(7839,'king', 'president ' , null ,to_date('17-11-1981 ' , 'dd-mm-yyyy' ), 5000 , null , 10 );
insert into emp values(7844,'turner','salesman ' , 7698 ,to_date('8-9-1981 ' , 'dd-mm-yyyy' ), 1500 , 0 , 30 );
insert into emp values(7876,'adams', 'clerk' , 7788 ,to_date('13-7-1987' , 'dd-mm-rr ' ) - 51 , 1100 , null , 20 );
insert into emp values(7900,'james', 'clerk' , 7698 ,to_date('3-12-1981' , 'dd-mm-yyyy' ), 950 , null , 30 );
insert into emp values(7902,'ford', 'analyst' , 7566 ,to_date('3-12-1981' , 'dd-mm-yyyy' ), 3000 , null , 20 );
insert into emp values(7934,'miller','clerk ' , 7782 ,to_date('23-1-1982' , 'dd-mm-yyyy' ), 1300 , null , 10 );
  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")   ;