PolarDB:ODBC

Download the driver

Install ODBC on Windows

1. Download the installation package for your Windows architecture. For download links, see Download the driver.

2. After the download is complete, decompress the ODBC package.

3. Run the install.bat file as an administrator. After the installation is complete, press any key to exit.

4. Optional: To uninstall the ODBC package, run the uninstall.bat file.

Connect to PolarDB on Windows

1. In Control Panel, open Tools.

2. On the Tools page, find and open ODBC Data Sources.

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

4. Select POLARDB (UNICODE) and click Complete.

5. In the dialog box, enter the following information.

   Parameter	Description
   Data Source	The name of the data source.
   Description	A description for the data source.
   Database	The name of the destination database.
   SSL Mode	Select an SSL encryption method.
   Server	The endpoint of the target database. To view the endpoint of a PolarDB cluster, see View or apply for an endpoint.
   Port	The port of the destination database. The default value is 1521.
   User Name	The username for the database account.
   Password	The password for the database account.

6. Click Save.

Install ODBC on Unix or Linux

1. Run the following command to set the LD_LIBRARY_PATH environment variable:

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

2. Run the following command to set the ODBCINI environment variable:

   export ODBCINI=<your odbc.init file path>

3. Download the ODBC driver that corresponds to the architecture and version of your operating system. For download links, see Download the driver.

4. Install the ODBC driver.

   Note

   The PolarDB ODBC driver package is portable. You can use it immediately after decompression.

   tar -zxvf polardb-odbc.tar.gz

   The directory structure is as follows:

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

Connect to PolarDB on Unix or Linux

1. Install Libtool on your Linux server. The Libtool version must be 1.5.10 or later.

   yum install -y libtool

2. Install unixODBC-devel on your Linux server.

   yum install -y unixODBC-devel

3. Edit your odbc.ini file.

   vim $ODBCINI 

4. Add the following information 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    = <Your database name>
   Servername  = <Your database endpoint>
   Password    = <Your password>
   Port        = <Your port>
   Username    = <Your username>
   Trace       = yes
   TraceFile   = /tmp/odbc.log
   FileUsage   = 1

   Note

   • To view the endpoint of a PolarDB cluster, see View or apply for an endpoint.

   • In the example, replace /home/user with the actual path that contains the target folder.

5. Connect to PolarDB.

   $isql -v POLARDB
   +---------------------------------------+
   | Connected!                            |
   |                                       |
   | sql-statement                         |
   | help [tablename]                      |
   | quit                                  |
   |                                       |
   +---------------------------------------+
   SQL>

Examples for Unix or Linux

This section shows how to run the Test1 and Test2 test 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 within the ODBC driver folder.

   cd samples

4. Compile the test samples. After the compilation is complete, two test files, Test1 and Test2, are generated.

   make

5. Run Test1 and Test2.

   ./Test1
   ## Run Test1
   ./Test2
   ## Run Test2

   Note

   • Test1 demonstrates create, read, update, and delete (CRUD) operations. Test2 demonstrates how to use a cursor and an output parameter.

   • The following code snippets are for demonstration purposes only. For the complete code, see the Test1 and Test2 files in the samples folder of the driver package.

The following code is an example from 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);
    /* CRUD 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");
    /* Close the connection */
    Disconnect(&hEnv,&hDBC,&hStmt);
    /* Clean up */
    free(hEnv);
    free(hDBC);
    free(hStmt);
    return 0;
}

The following code is an example from Test2:

int main(int argc, char* argv[])
{
    /* Definitions */
    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")   ;