PolarDB:ODBC

Install the ODBC driver

1. Download the driver package for your Windows architecture. See Download the driver.

2. Extract the package.

3. Run install.bat as an administrator, then press any key to exit.

   

4. (Optional) To uninstall the driver, run uninstall.bat.

Connect to PolarDB from Windows

1. Open Control Panel and go to Administrative Tools.

2. Open ODBC Data Sources.

3. In the ODBC Data Source Administrator dialog box, click Add.

4. Select PolarDB (UNICODE) as the driver, then click Finish.

5. Configure the data source parameters:

   Parameter	odbc.ini keyword	Default	Description
   Date Source	—	—	Name of the data source
   Description	—	—	Description of the data source
   Datebase	Database	—	Name of the PolarDB cluster
   SSL Mode	—	—	Whether to enable SSL encryption
   Server	Servername	—	Endpoint of the database. To find your endpoint, see View or apply for an endpoint.
   Prot	Port	1521	Port used to connect to the database
   User Name	Username	—	Username of the database account
   Password	Password	—	Password of the database account

6. Click Save.

Install the ODBC driver

1. Download the driver package for your Linux architecture. See Download the driver.

2. Extract the package:

   tar -zxvf polardb-odbc.tar.gz

   The extracted directory contains three subdirectories:

   ├── include
   ├── lib
   └── samples
   3 directories

3. Set the LD_LIBRARY_PATH environment variable to the driver's lib directory:

   export LD_LIBRARY_PATH=<PolarDB-ODBC/lib/path>

4. Set the ODBCINI environment variable to point to your odbc.ini file:

   export ODBCINI=<your odbc.ini file path>

Configure the data source

1. Edit your odbc.ini file:

   vim $ODBCINI

2. Add the following data source definition. Replace the placeholder values with your actual connection details.

   Note

   • Replace /home/user with the actual path of the target folder.

   • To find your cluster endpoint, see View or apply for an endpoint.

   [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

Connect to PolarDB

1. Install Libtool 1.5.1 or later:

   yum install -y libtool

2. Install unixODBC-devel:

   yum install -y unixODBC-devel

3. Test the connection:

   $isql -v POLARDB

   A successful connection shows:

   +---------------------------------------+
   | Connected!                            |
   |                                       |
   | sql-statement                         |
   | help [tablename]                      |
   | quit                                  |
   |                                       |
   +---------------------------------------+
   SQL>

Set up the sample database

Create the emp table and insert sample data in your PolarDB database before running the tests:

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

Build and run the samples

1. Extract the driver package and navigate to the samples folder:

   tar -zxvf polardb-odbc.tar.gz
   cd polardb-odbc/samples

2. Compile the samples:

   make

   This generates the Test1 and Test2 executables.

3. Run the tests:

   ./Test1
   ./Test2

Test1 — CRUD operations

Test1 uses a single connection to perform insert, update, delete, and select operations against the emp table.

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;
}

Test2 — Cursors and output parameters

Test2 demonstrates how to call a stored procedure that returns REFCURSOR output parameters.

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