This topic describes how to use the Open Database Connectivity (ODBC) driver to connect a UNIX or Windows application to a PolarDB for Oracle cluster.
Prerequisites
- An account is created for your 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.
- PolarTools. For more information about the download and installation, see Overview.
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
Use the ODBC driver to connect to PolarDB from a Windows server
Install the ODBC driver in Unix or Linux
Use the ODBC driver to connect to PolarDB from a UNIX or Linux server
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 );
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") ;