PolarDB:ODBC

Download the driver

• For 32-bit x86 UNIX or Linux, download PolarDB-ODBC_Linux_X86_32.tar.gz.

• For 64-bit x86 UNIX or Linux, download PolarDB-ODBC_Linux_X86_64.tar.gz.

• For 64-bit ARM UNIX or Linux, download PolarDB-ODBC_Linux_arm_64.tar.gz.

• For 64-bit x86 Windows, download PolarDB-ODBC_Windows_x86_64.7z.

• For 32-bit x86 Windows, download PolarDB-ODBC_Windows_x86_32.7z.

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.

   

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 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")   ;