PolarDB:ODBC

Instal driver ODBC

1. Unduh paket driver untuk arsitektur Windows Anda. Lihat Download the driver.

2. Ekstrak paket tersebut.

3. Jalankan install.bat sebagai administrator, lalu tekan sembarang tombol untuk keluar.

   

4. (Opsional) Untuk menguninstal driver, jalankan uninstall.bat.

Hubungkan ke PolarDB dari Windows

1. Buka Control Panel dan buka Administrative Tools.

2. Buka ODBC Data Sources.

3. Pada kotak dialog ODBC Data Source Administrator, klik Add.

4. Pilih PolarDB (UNICODE) sebagai driver, lalu klik Finish.

5. Konfigurasikan parameter sumber data:

   Parameter	odbc.ini keyword	Default	Description
   Date Source	—	—	Nama sumber data
   Description	—	—	Deskripsi sumber data
   Datebase	Database	—	Nama kluster PolarDB
   SSL Mode	—	—	Apakah enkripsi SSL diaktifkan
   Server	Servername	—	Titik akhir database. Untuk menemukan titik akhir Anda, lihat View or apply for an endpoint.
   Prot	Port	1521	Port yang digunakan untuk menghubungkan ke database
   User Name	Username	—	Username akun database
   Password	Password	—	Password akun database

6. Klik Save.

Instal driver ODBC

1. Unduh paket driver untuk arsitektur Linux Anda. Lihat Download the driver.

2. Ekstrak paket tersebut:

   tar -zxvf polardb-odbc.tar.gz

   Direktori hasil ekstraksi berisi tiga subdirektori:

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

3. Atur variabel lingkungan LD_LIBRARY_PATH ke direktori lib driver:

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

4. Atur variabel lingkungan ODBCINI agar mengarah ke file odbc.ini Anda:

   export ODBCINI=<your odbc.ini file path>

Konfigurasikan sumber data

1. Edit file odbc.ini Anda:

   vim $ODBCINI

2. Tambahkan definisi sumber data berikut. Ganti nilai placeholder dengan detail koneksi aktual Anda.

   Catatan

   • Ganti /home/user dengan path aktual folder target.

   • Untuk menemukan titik akhir kluster Anda, lihat 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

Hubungkan ke PolarDB

1. Instal Libtool versi 1.5.1 atau yang lebih baru:

   yum install -y libtool

2. Instal unixODBC-devel:

   yum install -y unixODBC-devel

3. Uji koneksi:

   $isql -v POLARDB

   Koneksi yang berhasil akan menampilkan:

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

Siapkan database contoh

Buat tabel emp dan masukkan data sampel ke database PolarDB Anda sebelum menjalankan pengujian:

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

Buat dan jalankan contoh

1. Ekstrak paket driver dan masuk ke folder samples:

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

2. Kompilasi contoh:

   make

   Perintah ini menghasilkan file eksekusi Test1 dan Test2.

3. Jalankan pengujian:

   ./Test1
   ./Test2

Test1 — Operasi CRUD

Test1 menggunakan satu koneksi untuk melakukan operasi insert, update, delete, dan select terhadap tabel emp.

int main(int argc, char* argv[])
{
    /*Inisialisasi*/
    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);
    /*Operasi tambah, hapus, ubah, dan kueri*/
    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");
    /*Putus koneksi*/
    Disconnect(&hEnv,&hDBC,&hStmt);
    /*membersihkan*/
    free(hEnv);
    free(hDBC);
    free(hStmt);

    return 0;
}

Test2 — Cursor dan parameter output

Test2 menunjukkan cara memanggil prosedur tersimpan yang mengembalikan parameter output REFCURSOR.

int main(int argc, char* argv[])
{
    /*Definisi*/
    RETCODE rCode;
    SQLUSMALLINT a;
    SQLINTEGER Num1IndOrLen;
    SQLSMALLINT iTotCols = 0;

    int j;
    SDWORD cbData;
    /*Inisialisasi*/
    HENV *hEnv = (HENV*)malloc(sizeof(HENV));
    HDBC *hDBC = (HDBC*)malloc(sizeof(HDBC));
    HSTMT *hStmt = (HSTMT*)malloc(sizeof(HSTMT));
    HSTMT *hStmt1 = (HSTMT*)malloc(sizeof(HSTMT));
    /**Membangun koneksi**/
    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);
    /*mulai*/
    ExecuteSimple_Select(&hStmt1,(UCHAR*) "BEGIN;");
    /*persiapan*/
    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;
    /*eksekusi*/
    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")   ;