PolarDB:OCI

Prasyarat

Sebelum memulai, pastikan Anda telah memiliki:

• Akun database untuk kluster PolarDB Anda. Untuk informasi selengkapnya, lihat Create database accounts.

• Alamat IP host Anda telah ditambahkan ke daftar putih kluster. Untuk informasi selengkapnya, lihat Configure a whitelist for a cluster.

• Sistem operasi Linux atau Windows 64-bit.

• Oracle OCI driver development kit telah diinstal.

Unduh driver PolarDB OCI

Unduh paket driver: polardb-oci.tar.gz

Instal driver PolarDB OCI

Ekstrak paket driver, salin file pustaka ke sistem Anda, dan atur variabel lingkungan agar kompilator dapat menemukannya.

Parameter koneksi

Sebelum menulis kode, kumpulkan informasi berikut mengenai kluster PolarDB Anda:

Format string koneksi adalah //host:port/database. Pada demo ini, nilai default-nya adalah //localhost:5432/postgres.

Tulis kode

Contoh polardb_demo dalam paket driver menunjukkan alur kerja lengkap: menghubungkan ke kluster, membuat tabel, memasukkan data, mengkueri set hasil, memanggil prosedur tersimpan dengan parameter IN OUT dan OUT, serta membersihkan sumber daya.

Semua contoh menggunakan urutan inisialisasi handle OCI standar: environment handle → error handle → service context handle → session handle → server handle.

/* ============================================================================
 * Copyright (c) 2004-2019 POLARDB Corporation. All Rights Reserved.
 * ===========================================================================
 */
#include <stdio.h>
#include <stdlib.h>

#include <string.h>
#include <oci.h>

#ifdef WIN32
#include <time.h>
#else
#include <sys/time.h>
#endif

/* Definisikan makro untuk menangani error */
#define HANDLE_ERROR(x,y) check_oci_error(x,y)

#define DATE_FMT "DAY, MONTH DD, YYYY"
#define DATE_LANG "American"

sword ConvertStringToDATE( char *datep, char *formatp, dvoid *datepp );
/* Rutinitas Kustom untuk menangani error,     */

/* ini menunjukkan Penanganan Error/Exception di OCI */
void check_oci_error (dvoid * errhp, sword status);

/*
 * <<<<<<<<<<<<<<<<<<< PROTOTIPE FUNGSI<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
 */

/* Inisialisasi & Alokasikan semua handle */
void
initHandles (OCISvcCtx **, OCIServer **, OCISession **, OCIError **,
         OCIEnv **);

/* login ke database dan mulai sesi pengguna */
void
logon (OCISvcCtx **, OCIServer **, OCISession **, OCIError **,
       OCIEnv **, text *, text *, text *);

/* Buat tabel yang diperlukan  */
void create_table (OCISvcCtx *, OCIError *, OCIEnv *);

/* siapkan data untuk contoh kita */
void prepare_data (OCISvcCtx *, OCIError *, OCIEnv *);

/* buat prosedur/fungsi untuk didemonstrasikan dalam contoh */
void create_stored_procs (OCISvcCtx *, OCIError *, OCIEnv *);

/* pilih dan cetak data dengan iterasi melalui resultSet */
void select_print_data (OCISvcCtx *, OCIError *, OCIEnv *);

/* demonstrasikan pemanggilan prosedur tersimpan dan pengambilan nilai */

/* proc1 mendemonstrasikan IN OUT */
void call_stored_proc1 (OCISvcCtx *, OCIError *, OCIEnv *);

/* proc2 mendemonstrasikan OUT */
void call_stored_proc2 (OCISvcCtx *, OCIError *, OCIEnv *);

/* hapus tabel yang diperlukan */
void drop_table (OCISvcCtx *, OCIError *, OCIEnv *);

/* hapus prosedur dan fungsi tersimpan */
void drop_stored_procs (OCISvcCtx *, OCIError *, OCIEnv *);

/* bersihkan handle utama sebelum keluar */
void
cleanup (OCISvcCtx **, OCIServer **, OCISession **, OCIError **, OCIEnv **);

/*
 * <<<<<<<<<<<<<<<<<<<<<<<<< AKHIR PROTOTIPE FUNGSI<<<<<<<<<<<<<<<<<<<<<<<<<<
 */


/* <<<<<<<<<< Variabel Global */
ub4 init_mode = OCI_DEFAULT;
ub4 auth_mode = OCI_CRED_RDBMS;

/* <<<<<<<<<< Akhir Variabel Global */

int
main (void)
{

  /*
   * Deklarasi Handle, program OCI tipikal memerlukan setidaknya
   * handle berikut: Environment Handle, Error Handle, Service Context
   * Handle, Server Handle, User Session (Authentication Handle)
   */

  /* Environment */
  OCIEnv *envhp;

  /* Error */
  OCIError *errhp;

  /* Service Context */
  OCISvcCtx *svchp;

  /* Server */
  OCIServer *srvhp;

  /* Session (otentikasi) */
  OCISession *authp;


  /*
   * Akhir deklarasi Handle
   */

  /*
   * Deklarasi variabel lokal,
   */
  text *username = (text *) "parallels";
  text *passwd = (text *) "";

  /*
   * String Koneksi Oracle Instant Client
   */
  text *server = (text *) "//localhost:5432/postgres";

  /*
   * Inisialisasi dan Alokasikan handle
   */
  initHandles (&svchp, &srvhp, &authp, &errhp, &envhp);

  /*
   * login ke database
   */
  logon (&svchp, &srvhp, &authp, &errhp, &envhp, username, passwd, server);

  /*
   * Buat tabel yang diperlukan untuk contoh ini
   */
  create_table (svchp, errhp, envhp);

  /*
   * masukkan data ke tabel
   */
  prepare_data (svchp, errhp, envhp);

  /*
   * buat prosedur tersimpan & fungsi
   */
  create_stored_procs (svchp, errhp, envhp);

  /*
   * pilih dan cetak data dengan iterasi melalui resultSet sederhana
   */
  select_print_data (svchp, errhp, envhp);

  /*
   * demonstrasikan pemanggilan prosedur tersimpan dan pengambilan nilai
   */
  call_stored_proc1 (svchp, errhp, envhp);

  /*
   * demonstrasikan parameter OUT
   */
  call_stored_proc2 (svchp, errhp, envhp);

  /*
   * Hapus tabel yang digunakan dalam contoh ini
   */
  drop_table (svchp, errhp, envhp);

  /*
   * Hapus prosedur tersimpan & fungsi yang digunakan dalam contoh ini
   */
  drop_stored_procs (svchp, errhp, envhp);

  /*
   * bersihkan sumber daya
   */
  cleanup (&svchp, &srvhp, &authp, &errhp, &envhp);

  return 0;
}

/* Rutinitas Kustom untuk menangani error,     */

/* ini menunjukkan Penanganan Error/Exception di OCI */

void
check_oci_error (dvoid * errhp, sword status)
{
  text errbuf[512];
  sb4 errcode;

  if (status == OCI_SUCCESS)
    {
      return;
    }
  switch (status)
    {
    case OCI_SUCCESS_WITH_INFO:
      printf ("OCI_SUCCESS_WITH_INFO:\n");
      OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,
           errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);
      printf ("%s", errbuf);
      break;
    case OCI_NEED_DATA:
      printf ("Error - OCI_NEED_DATA\n");
      break;
    case OCI_NO_DATA:
      printf ("Error - OCI_NO_DATA\n");
      break;
    case OCI_ERROR:
      printf ("Error - OCI_ERROR:\n");
      OCIErrorGet (errhp, (ub4) 1, (text *) 0, &errcode,
           errbuf, (ub4) sizeof (errbuf), OCI_HTYPE_ERROR);
      printf ("%s", errbuf);
      break;
    case OCI_INVALID_HANDLE:
      printf ("Error - OCI_INVALID_HANDLE\n");
      break;
    case OCI_STILL_EXECUTING:
      printf ("Error - OCI_STILL_EXECUTING\n");
      break;
    case OCI_CONTINUE:
      printf ("Error - OCI_CONTINUE\n");
      break;
    default:
      break;
    }

  /*
   * keluar aplikasi
   */
  exit((int)status);
}

/* Inisialisasi & Alokasikan handle yang diperlukan */
void
initHandles (OCISvcCtx ** svchp, OCIServer ** srvhp, OCISession ** authp,
         OCIError ** errhp, OCIEnv ** envhp)
{

  /*
   * Sekarang dimulai bagian tempat kita harus menginisialisasi & Mengalokasikan
   * handle dasar. Ini adalah pengaturan wajib atau inisialisasi yang
   * diperlukan sebelum kita dapat melanjutkan login dan bekerja dengan
   * database. Inisialisasi dan persiapan ini akan mencakup langkah-langkah berikut:
   *
   * 1. Inisialisasi OCI (OCIInitialize()) 2. Inisialisasi
   * Environment (OCIEnvInit()) 3. Inisialisasi & Alokasikan Error Handle
   * 4. Inisialisasi & Alokasikan Service Context Handle 5. Inisialisasi &
   * Alokasikan Session Handle 6. Inisialisasi & Alokasikan Server Handle
   *
   * Sesuai versi OCI terbaru, alih-alih menggunakan OCIInitialize()
   * dan OCIEnvInit(), kita dapat melakukannya dengan satu panggilan API bernama
   * OCIEnvCreate().
   */

  /*
   * Inisialisasi OCI
   */
  if (OCIInitialize (init_mode, (dvoid *) 0,
             (dvoid * (*)(dvoid *, size_t)) 0,
             (dvoid * (*)(dvoid *, dvoid *, size_t)) 0,
             (void (*)(dvoid *, dvoid *)) 0) != OCI_SUCCESS)
    {
      printf ("ERROR: gagal menginisialisasi OCI\n");
      exit (1);
    }
  /*
   * Inisialisasi Environment.
   */
  HANDLE_ERROR (*envhp,
        OCIEnvInit (&(*envhp), OCI_DEFAULT, (size_t) 0,
                (dvoid **) 0));

  /*
   * Inisialisasi & Alokasikan Error Handle
   */
  HANDLE_ERROR (*envhp,
        OCIHandleAlloc (*envhp, (dvoid **) & (*errhp),
                OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0));

  /*
   * Inisialisasi & Alokasikan Service Context Handle
   */
  HANDLE_ERROR (*errhp,
        OCIHandleAlloc (*envhp, (dvoid **) & (*svchp),
                OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0));

  /*
   * Inisialisasi & Alokasikan Session Handle
   */
  HANDLE_ERROR (*errhp,
        OCIHandleAlloc (*envhp, (dvoid **) & (*authp),
                OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0));

  /*
   * Inisialisasi & Alokasikan Server Handle
   */
  HANDLE_ERROR (*errhp,
        OCIHandleAlloc (*envhp, (dvoid **) & (*srvhp),
                OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0));

}

void
logon (OCISvcCtx ** svchp, OCIServer ** srvhp, OCISession ** authp,
       OCIError ** errhp, OCIEnv ** envhp, text * username, text * passwd,
       text * server)
{

  /*
   * Sekarang dimulai proses login ke Server Database yang mencakup dua
   * langkah:
   *
   * 1. Menyambungkan ke Server 2. Memulai atau Memulai Sesi
   *
   * Ini adalah login kompleks. Cara mudah untuk login adalah menghindari
   * penyambungan server dan memulai sesi, lalu cukup gunakan OCILogon() atau
   * OCILogon2() dan logout menggunakan OCILogoff()
   */

  /*
   * Menyambungkan ke server
   */

  HANDLE_ERROR (*errhp,
        OCIServerAttach (*srvhp, *errhp, server,
                 (ub4) strlen ((char *) server),
                 OCI_DEFAULT));

  /*
   * Kode berikut akan memulai sesi, tetapi sebelum memulai sesi kita harus:
   * 1. Mengatur Server Handle yang sekarang telah tersambung ke Service Context Handle
   * 2. Mengatur Username dan password ke Session Handle
   */

  /*
   * Mengatur Server Handle ke Service Context Handle
   */

  HANDLE_ERROR (*errhp,
        OCIAttrSet (*svchp, OCI_HTYPE_SVCCTX,
                (dvoid *) (*srvhp), (ub4) 0, OCI_ATTR_SERVER,
                *errhp));

  /*
   * Mengatur username dan password ke session handle
   */

  HANDLE_ERROR (*errhp,
        OCIAttrSet (*authp, OCI_HTYPE_SESSION,
                (dvoid *) username,
                (ub4) strlen ((char *) username),
                OCI_ATTR_USERNAME, *errhp));
  HANDLE_ERROR (*errhp,
        OCIAttrSet (*authp, OCI_HTYPE_SESSION, (dvoid *) passwd,
                (ub4) strlen ((char *) passwd), OCI_ATTR_PASSWORD,
                *errhp));

  /*
   * Sekarang AKHIRNYA Mulai sesi kita
   */

  HANDLE_ERROR ((*errhp),
        OCISessionBegin (*svchp, *errhp,
                 *authp, auth_mode, OCI_DEFAULT));

  printf ("********************************************\n");
  printf ("Milestone  : Logged on as --> '%s'\n", username);
  printf ("********************************************\n");

  /*
   * Setelah kita memulai sesi, kita harus mengatur Session
   */

  /*
   * (otentikasi) handle ke Service Context Handle
   */

  HANDLE_ERROR (*errhp,
        OCIAttrSet (*svchp, OCI_HTYPE_SVCCTX,
                (dvoid *) (*authp), (ub4) 0,
                OCI_ATTR_SESSION, *errhp));
}

/* Buat tabel yang diperlukan untuk contoh ini */
void
create_table (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{
  OCIStmt *stmhp;
  text *create_statement =
    (text *)"CREATE TABLE OCISPEC \n (ENAME VARCHAR2(20)\n, MGR NUMBER\n, HIREDATE DATE)";
  ub4 status = OCI_SUCCESS;

  /*
   * Inisialisasi & Alokasikan Statement Handle
   */
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));


  /*
   * Siapkan pernyataan Create
   */

  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp, errhp,
                create_statement,
                strlen ((const char *) create_statement),
                OCI_NTV_SYNTAX, OCI_DEFAULT));


  /*
   * Eksekusi Pernyataan Create
   */
  if ((status = OCIStmtExecute (svchp, stmhp, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN CREATING TABLE(S)\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Table(s) Successfully created\n");
      printf ("********************************************\n");
    }
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp, OCI_HTYPE_STMT));
}

/* siapkan data untuk contoh kita */
void
prepare_data (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{
  OCIStmt *stmhp;
  text *insstmt =
    (text *)
    "INSERT INTO OCISPEC (ename,mgr, hiredate) VALUES (:ENAME,:MGR, CAST(:HIREDATE AS timestamp))";
  OCIBind *bnd1p = (OCIBind *) 0;    /* handle bind pertama   */
  OCIBind *bnd2p = (OCIBind *) 0;    /* handle bind kedua */
  OCIBind *bnd3p = (OCIBind *) 0;    /* handle bind ketiga   */
  ub4 status = OCI_SUCCESS;
  int i = 0;

  char *ename[3] = { "SMITH", "ALLEN", "KING" };

  sword mgr[] = { 7886, 7110, 7221 };

  char *date_buffer[3] = { "02-AUG-07", "02-APR-07", "02-MAR-07" };

  /*
   * Inisialisasi & Alokasikan Statement Handle
   */
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  /*
   * Siapkan pernyataan insert
   */
  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp, errhp, insstmt,
                (ub4) strlen ((char *) insstmt),
                (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));

  /*
   * Dalam loop ini kita akan mengikat data dari array untuk memasukkan beberapa
   * baris ke database. Cara yang lebih elegan dan lebih baik untuk melakukan ini adalah
   * menggunakan Array Binding (Batch Inserts). Pustaka Pengganti OCI POLARDB
   * AKAN mendukung Array Bindings meskipun tidak digunakan di sini saat ini
   */
  for (i = 0; i < 3; i++)
    {
      /*
       * Bind Variable untuk ENAME
       */
      HANDLE_ERROR (errhp,
            OCIBindByName (stmhp, &bnd1p, errhp, (text *) ":ENAME",
                   -1, (dvoid *) ename[i],
                   (sb4) strlen (ename[i]) + 1, SQLT_STR,
                   (dvoid *) 0, 0, (ub2 *) 0, (ub4) 0,
                   (ub4 *) 0, OCI_DEFAULT));

      /*
       * Bind Variable untuk MGR
       */
      HANDLE_ERROR (errhp,
            OCIBindByName (stmhp, &bnd2p, errhp, (text *) ":MGR",
                   -1, (dvoid *) & mgr[i], sizeof (mgr[i]),
                   SQLT_INT, (dvoid *) 0, 0, (ub2 *) 0,
                   (ub4) 0, (ub4 *) 0, OCI_DEFAULT));

      /*
       * Bind Variable untuk HIREDATE
       */
      HANDLE_ERROR (errhp,
            OCIBindByName (stmhp, &bnd3p, errhp, (text *) ":HIREDATE",
                   -1, (dvoid *)  date_buffer[i],
                   strlen(date_buffer[i])+1, SQLT_STR, (dvoid *) 0, 0,
                   (ub2 *) 0, (ub4) 0, (ub4 *) 0,
                   OCI_DEFAULT));

      /*
       * Eksekusi pernyataan dan masukkan data
       */
      if ((status = OCIStmtExecute (svchp, stmhp, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN INSERTING DATA\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
    }

  OCITransCommit (svchp, errhp, (ub4) 0);
  printf ("********************************************\n");
  printf
    ("MileStone : Data Sucessfully inserted \n & Committed via Transaction\n");
  printf ("********************************************\n");
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp, OCI_HTYPE_STMT));

}

/* Buat prosedur dan fungsi tersimpan yang akan digunakan dalam contoh ini */
void
create_stored_procs (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{
  /*
   * Fungsi ini membuat 2 prosedur tersimpan dan satu fungsi tersimpan:
   * 1. StoredProcedureSample1 - untuk menunjukkan eksekusi prosedur dan
   * menerima nilai dari parameter IN OUT
   * 2. StoredProcedureSample2 - untuk menunjukkan eksekusi prosedur dan
   * menerima nilai dari parameter OUT
   * 3. StoredProcedureSample3 - untuk menunjukkan eksekusi fungsi dan
   * menerima nilai yang dikembalikan oleh fungsi dengan cara Callable Statement
   */
  OCIStmt *stmhp;
  OCIStmt *stmhp2;
  OCIStmt *stmhp3;

  text *create_statement =
    (text *)"CREATE OR REPLACE PROCEDURE StoredProcedureSample1\n (mgr1 int, ename1 IN OUT varchar2)\n   is\nbegin\ninsert into ocispec (mgr, ename) values (7990,'STOR1');\nename1 := 'Successful';\n end;\n";

  text *create_statement2 =
    (text *)"CREATE OR REPLACE PROCEDURE StoredProcedureSample2\n(mgr1 int, ename1 varchar2,eout1 OUT varchar2)\nis\nbegin\ninsert into ocispec(mgr,ename) values (7991, 'STOR2');\neout1 := 'Successful';\n    end;";

  text *create_statement3 =
    (text *)"CREATE OR REPLACE FUNCTION f1\nRETURN VARCHAR2\nis\nv_Sysdate DATE;\nv_charSysdate VARCHAR2(20);\nbegin\nSELECT TO_CHAR(SYSDATE, 'dd-mon-yyyy') into v_charSysdate FROM DUAL;\n    return(v_charSysdate);\nend;";



  ub4 status = OCI_SUCCESS;

  /*
   * Inisialisasi & Alokasikan Statement Handles
   */
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp2,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp3,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  /*
   * Siapkan pernyataan Create
   */

  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp, errhp,
                create_statement,
                strlen ((const char *) create_statement),
                OCI_NTV_SYNTAX, OCI_DEFAULT));
  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp2, errhp, create_statement2,
                strlen ((const char *) create_statement2),
                OCI_NTV_SYNTAX, OCI_DEFAULT));
  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp3, errhp, create_statement3,
                strlen ((const char *) create_statement3),
                OCI_NTV_SYNTAX, OCI_DEFAULT));

  /*
   * Eksekusi Pernyataan Create SampleProcedure1
   */
  if ((status = OCIStmtExecute (svchp, stmhp, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN CREATING PROCEDURE 1\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Sample Procedure 1 Successfully created\n");
      printf ("********************************************\n");

    }

  /*
   * Eksekusi Pernyataan Create Sample Procedure2
   */
  if ((status = OCIStmtExecute (svchp, stmhp2, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN CREATING PROCEDURE 2\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Sample Procedure 2 Successfully created\n");
      printf ("********************************************\n");
    }

  /*
   * Eksekusi Pernyataan Create Sample Procedure3
   */
  if ((status = OCIStmtExecute (svchp, stmhp3, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN CREATING PROCEDURE 3\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Sample Procedure 3 Successfully created\n");
      printf ("********************************************\n");
    }


  HANDLE_ERROR (errhp, OCIHandleFree (stmhp, OCI_HTYPE_STMT));
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp2, OCI_HTYPE_STMT));
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp3, OCI_HTYPE_STMT));
}

/* pilih dan cetak data dengan iterasi melalui resultSet */
void
select_print_data (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{

  /* Statement */
  OCIStmt *stmhp;

  /* Define */
  OCIDefine *define;

  /* Buffer untuk Nama karyawan */
  char ename_buffer[10] ;

  /* Buffer untuk mgr */
  sword mgr_buffer;

  /*Buffer untuk hiredate */
  char hire_date[20];

  /*
   * pernyataan select sederhana
   */
  text * sql_statement =
      (text *) "select ename,mgr,hiredate from ocispec";

  /*
   * variabel lokal tambahan
   */

  ub4 rows = 1;
  ub4 fetched = 1;
  ub4 status = OCI_SUCCESS;

  sb2 null_ind_ename = 0;

  /* indikator null untuk ename */
  sb2 null_ind_mgr = 0;

  /* indikator null untuk mgr */
  sb2 null_ind_hiredate = 0;

  /* indikator null untuk hiredate */

  /*
   * Sekarang kita akan memulai Milestone dari Kueri Sederhana ke
   * database dan loop melalui resultSet. Ini mencakup langkah-langkah berikut:
   *
   * 1. Inisialisasi dan Alokasikan Statement Handle
   * 2. Siapkan Pernyataan
   * 3. Definisikan variabel Output untuk menerima output dari
   * pernyataan select
   * 4. Eksekusi pernyataan
   * 5. Ambil resultset dan Cetak nilai
   *
   */
memset( ename_buffer, 0, sizeof(ename_buffer) );
memset( hire_date, 0, sizeof(hire_date) );
  /*
   * Inisialisasi & Alokasikan Statement Handle
   */

  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  /*
   * Siapkan pernyataan
   */

  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp, errhp,
                sql_statement,
                strlen ((const char *) sql_statement),
                OCI_NTV_SYNTAX, OCI_DEFAULT));


  /*
   * Ikat variabel String (OCIString) pada posisi 1. Tipe data yang digunakan
   * SQLT_VST
   */
  HANDLE_ERROR (errhp,
        OCIDefineByPos (stmhp, &define, errhp,
                (ub4) 1, ename_buffer, 10,
                (ub2) SQLT_STR, &null_ind_ename, 0, 0,
                OCI_DEFAULT));

  /*
   * Ikat variabel Number (OCINumber) pada posisi 2. Tipe data yang digunakan
   * SQLT_VNU
   */
  HANDLE_ERROR (errhp,
        OCIDefineByPos (stmhp, &define, errhp,
                (ub4) 2, &mgr_buffer, sizeof (sword),
                (ub2) SQLT_INT, &null_ind_mgr, 0, 0,
                OCI_DEFAULT));

  /*
   * Ikat variabel Date (OCIDate) pada posisi 3. Tipe data yang digunakan
   * SQLT_ODT
   */
  HANDLE_ERROR (errhp,
        OCIDefineByPos (stmhp, &define, errhp,
                (ub4) 3, hire_date, 20,
                (ub2) SQLT_STR, &null_ind_hiredate, 0, 0,
                OCI_DEFAULT));



  /*
   * Eksekusi Pernyataan SQL sederhana
   */
  status = OCIStmtExecute (svchp, stmhp, errhp,
               rows, (ub4) 0, NULL, NULL, OCI_DEFAULT);


  /*
   * Cetak Resultset
   */
  if (status == OCI_NO_DATA)
    {
      /*
       * menunjukkan tidak mengambil apa pun (karena kita tidak melakukan array
       * fetching)
       */
      fetched = 0;
    }
  else
    {
      HANDLE_ERROR (errhp, status);
    }

  if (fetched)
    {
      /*
       * cetak string
       */
      if (null_ind_ename == -1)
    printf ("name -> [NULL]\t");
      else
    printf ("name -> [%s]\t",  ename_buffer);


      /*
       * cetak angka dengan mengonversinya menjadi int
       */
      if (null_ind_mgr == -1)
    printf ("mgr -> [NULL]\n");
      else
    {
      printf ("mgr -> [%d]\n", mgr_buffer);
    }

      if (null_ind_hiredate == -1)
    printf ("hiredate -> [NULL]\n");
      else
    {
      printf ("hiredate -> [%s]\n",hire_date );
    }

      /*
       * loop melalui resultset satu per satu melalui
       * OCIStmtFetch()
       */

      /*
       * hingga tidak menemukan apa pun
       */
      while (1)
    {
      status = OCIStmtFetch (stmhp, errhp,
                 rows, OCI_FETCH_NEXT, OCI_DEFAULT);
      if (status == OCI_NO_DATA)
        {
          /*
           * menunjukkan tidak dapat mengambil apa pun
           */
          break;
        }
      else
        {
          HANDLE_ERROR (errhp, status);
        }

      /*
       * cetak string
       */
      if (null_ind_ename == -1)
        printf ("name -> [NULL]\t");
      else
        printf ("name -> [%s]\t", ename_buffer);

      /*
       * cetak angka dengan mengonversinya menjadi int
       */
      if (null_ind_mgr == -1)
        printf ("mgr -> [NULL]\n");
      else
        {
        printf ("mgr -> [%d]\n", mgr_buffer);
        }

      /*
       * cetak tanggal setelah mengonversi ke teks
       */
      if (null_ind_hiredate == -1)
        printf ("hiredate -> [NULL]\n");
      else
        {

          printf ("hiredate -> [%s]\n", hire_date);
        }
    }
    }
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp, OCI_HTYPE_STMT));

}

void
call_stored_proc1 (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{
  OCIStmt *p_sql;
  OCIBind *p_Bind1 = (OCIBind *) 0;
  OCIBind *p_Bind2 = (OCIBind *) 0;

  char field2[20];


  /*
   * char field3[20];
   */
  sword field1 = 3;
  text *mySql = (text *) "Begin StoredProcedureSample1(:MGR, :ENAME); END";

  memset( field2, 0, sizeof(field2) );
  strcpy( field2, "Entry 3" );

  printf ("*************************************************\n");
  printf ("Example 1 - Using an IN OUT Parameter\n");
  printf ("*************************************************\n");


  /*
   * Inisialisasi & Alokasikan Statement Handle
   */

  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & p_sql,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  HANDLE_ERROR (errhp,
        OCIStmtPrepare (p_sql, errhp, mySql,
                (ub4) strlen ((char *)mySql), OCI_NTV_SYNTAX,
                OCI_DEFAULT));

  HANDLE_ERROR (errhp,
        OCIBindByPos (p_sql, &p_Bind1, errhp, 1,
                  (dvoid *) & field1, sizeof (sword),
                  SQLT_INT, 0, 0, 0, 0, 0, OCI_DEFAULT));

  HANDLE_ERROR (errhp,
        OCIBindByPos (p_sql, &p_Bind2, errhp, 2,
                  field2, (sizeof (field2)),
                  SQLT_STR, 0, 0, 0, 0, 0, OCI_DEFAULT));

  printf (" Field2 Before:\n");
  printf (" size ---> %d\n", sizeof (field2));
  printf (" length ---> %d\n", strlen (field2));
  printf (" value ---> %s\n", field2);

  HANDLE_ERROR (errhp,
        OCIStmtExecute (svchp, p_sql, errhp, (ub4) 1, (ub4) 0,
                (OCISnapshot *) NULL, (OCISnapshot *) NULL,
                (ub4) OCI_COMMIT_ON_SUCCESS));

  printf (" Field2 After:\n");
  printf (" size ---> %d\n", sizeof (field2));
  printf (" length ---> %d\n", strlen (field2));
  printf (" value ---> %s\n", field2);

  HANDLE_ERROR (errhp, OCIHandleFree (p_sql, OCI_HTYPE_STMT));
}

void
call_stored_proc2 (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{
  OCIStmt *p_sql;
  OCIBind *p_Bind1 = (OCIBind *) 0;
  OCIBind *p_Bind2 = (OCIBind *) 0;
  OCIBind *p_Bind3 = (OCIBind *) 0;

  char field2[20] = "Entry 3";
  char field3[20];
  sword field1 = 3;
  text *mySql =
    (text *) "Begin StoredProcedureSample2(:MGR, :ENAME, :EOUT); END";


  memset( field2, 0, sizeof(field2) );
  strcpy( field2, "Entry 3" );

  memset( field3, 0, sizeof(field3) );


  printf ("*************************************************\n");
  printf ("Example 2 - Using an OUT Parameter\n");
  printf ("*************************************************\n");

  /*
   * Inisialisasi & Alokasikan Statement Handle
   */

  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & p_sql,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  HANDLE_ERROR (errhp,
        OCIStmtPrepare (p_sql, errhp, mySql,
                (ub4) strlen ((char *)mySql), OCI_NTV_SYNTAX,
                OCI_DEFAULT));

  HANDLE_ERROR (errhp,
        OCIBindByPos (p_sql, &p_Bind1, errhp, 1,
                  (dvoid *) & field1, sizeof (sword),
                  SQLT_INT, 0, 0, 0, 0, 0, OCI_DEFAULT));

  HANDLE_ERROR (errhp,
        OCIBindByPos (p_sql, &p_Bind2, errhp, 2,
                  field2, strlen (field2) + 1,
                  SQLT_STR, 0, 0, 0, 0, 0, OCI_DEFAULT));

  HANDLE_ERROR (errhp,
        OCIBindByPos (p_sql, &p_Bind3, errhp, 3,
                  field3, 20,
                  SQLT_STR, 0, 0, 0, 0, 0, OCI_DEFAULT));

  printf (" Field3 Before:\n");
  printf (" size ---> %d\n", sizeof (field3));
  printf (" length ---> %d\n", strlen (field3));
  printf (" value ---> %s\n", field3);

  HANDLE_ERROR (errhp,
        OCIStmtExecute (svchp, p_sql, errhp, (ub4) 1, (ub4) 0,
                (OCISnapshot *) NULL, (OCISnapshot *) NULL,
                (ub4) OCI_COMMIT_ON_SUCCESS));


  printf (" Field3 After:\n");
  printf (" size ---> %d\n", sizeof (field3));
  printf (" length ---> %d\n", strlen (field3));
  printf (" value ---> %s\n", field3);

  HANDLE_ERROR (errhp, OCIHandleFree (p_sql, OCI_HTYPE_STMT));
}

/* hapus tabel yang diperlukan untuk contoh ini */
void
drop_table (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{
  OCIStmt *stmhp;
  text *statement = (text *)"DROP TABLE OCISPEC";
  ub4 status = OCI_SUCCESS;

  /*
   * Inisialisasi & Alokasikan Statement Handle
   */
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  /*
   * Siapkan pernyataan drop
   */
  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp, errhp,
                statement, strlen ((const char *) statement),
                OCI_NTV_SYNTAX, OCI_DEFAULT));

  /*
   * Eksekusi Pernyataan drop
   */
  if ((status = OCIStmtExecute (svchp, stmhp, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN DROPING TABLE(S)\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Table(s) Successfully Dropped\n");
      printf ("********************************************\n");
    }
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp, OCI_HTYPE_STMT));
}

void
drop_stored_procs (OCISvcCtx * svchp, OCIError * errhp, OCIEnv * envhp)
{
  OCIStmt *stmhp;
  OCIStmt *stmhp2;
  OCIStmt *stmhp3;

  text *create_statement = (text *)"DROP PROCEDURE StoredProcedureSample1";
  text *create_statement2 = (text *)"DROP PROCEDURE StoredProcedureSample2";
  text *create_statement3 = (text *)"DROP FUNCTION  f1";


  ub4 status = OCI_SUCCESS;
  OCITransCommit( svchp, errhp, OCI_DEFAULT );
  /*
   * Inisialisasi & Alokasikan Statement Handles
   */
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp2,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
  HANDLE_ERROR (errhp,
        OCIHandleAlloc (envhp, (dvoid **) & stmhp3,
                OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  /*
   * Siapkan pernyataan Create
   */

  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp, errhp,
                create_statement,
                strlen ((const char *) create_statement),
                OCI_NTV_SYNTAX, OCI_DEFAULT));
  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp2, errhp, create_statement2,
                strlen ((const char *) create_statement2),
                OCI_NTV_SYNTAX, OCI_DEFAULT));
  HANDLE_ERROR (errhp,
        OCIStmtPrepare (stmhp3, errhp, create_statement3,
                strlen ((const char *) create_statement3),
                OCI_NTV_SYNTAX, OCI_DEFAULT));

  /*
   * Eksekusi Pernyataan Create SampleProcedure1
   */
  if ((status = OCIStmtExecute (svchp, stmhp, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN DROPPING PROCEDURE 1\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Sample Procedure 1 Successfully dropped\n");
      printf ("********************************************\n");
    }

  /*
   * Eksekusi Pernyataan Create Sample Procedure2
   */
  if ((status = OCIStmtExecute (svchp, stmhp2, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN DROPPING PROCEDURE 2\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Sample Procedure 2 Successfully dropped\n");
      printf ("********************************************\n");
    }

  /*
   * Eksekusi Pernyataan Create Sample Procedure3
   */
  if ((status = OCIStmtExecute (svchp, stmhp3, errhp,
                   (ub4) 1, (ub4) 0, NULL, NULL, OCI_DEFAULT)) < OCI_SUCCESS)
    {
      printf ("FAILURE IN DROPPING PROCEDURE 3\n");
      HANDLE_ERROR (errhp, status);
      return;
    }
  else
    {
      printf ("********************************************\n");
      printf ("MileStone : Sample Procedure 3 Successfully dropped\n");
      printf ("********************************************\n");
    }


  HANDLE_ERROR (errhp, OCIHandleFree (stmhp, OCI_HTYPE_STMT));
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp2, OCI_HTYPE_STMT));
  HANDLE_ERROR (errhp, OCIHandleFree (stmhp3, OCI_HTYPE_STMT));

}

/* Bersihkan jejak Anda */
void
cleanup (OCISvcCtx ** svchp, OCIServer ** srvhp, OCISession ** authp,
     OCIError ** errhp, OCIEnv ** envhp)
{
  /*
   * logout
   */
  HANDLE_ERROR (*errhp, OCISessionEnd (*svchp, *errhp, *authp, OCI_DEFAULT));
  printf ("logged off\n");

  /*
   * putuskan koneksi dari server
   */
  HANDLE_ERROR (*errhp, OCIServerDetach (*srvhp, *errhp, OCI_DEFAULT));
  printf ("detached form server\n");

  /*
   * bebaskan handle
   */
  HANDLE_ERROR (*errhp, OCIHandleFree (*authp, OCI_HTYPE_SESSION));
  /* bebaskan session handle */
  *authp = 0;
  HANDLE_ERROR (*errhp, OCIHandleFree (*srvhp, OCI_HTYPE_SERVER));
  /* bebaskan server handle */
  *srvhp = 0;
  HANDLE_ERROR (*errhp, OCIHandleFree (*svchp, OCI_HTYPE_SVCCTX));
  /* bebaskan service context */
  *svchp = 0;
  HANDLE_ERROR (*errhp, OCIHandleFree (*errhp, OCI_HTYPE_ERROR));
  /* bebaskan error handle */
  *errhp = 0;
  OCIHandleFree (*envhp, OCI_HTYPE_ENV);
  /* bebaskan environment handle */
  *envhp = 0;
  printf ("free'd all handles\n");
}

Ganti parameter koneksi dalam fungsi main() dengan informasi kluster Anda (lihat bagian Parameter koneksi di atas).

Untuk informasi selengkapnya tentang driver OCI native Oracle, lihat OCI: Introduction.

Kompilasi kode

# ============================================================================
# Copyright (c) 2004-2012 PolarDB Corporation. All Rights Reserved.
# ===========================================================================

# Makefile untuk membangun test case C untuk OCILib
#

CC=gcc
CFLAGS=-Wall -g -I$(ORACLE_HOME)/ -L $(POLARDBOCI_LIB) -lpolaroci -lpq -liconv

SAMPLES = polardb_demo

all: $(SAMPLES)

%:%.o
    $(CC) $(CFLAGS) -o $@
clean:
    rm -rf $(SAMPLES)

make

Verifikasi koneksi

Setelah mengompilasi dan menjalankan polardb_demo, output yang diharapkan adalah:

********************************************
Milestone  : Logged on as --> 'parallels'
********************************************
********************************************
MileStone : Table(s) Successfully created
********************************************
********************************************
MileStone : Data Sucessfully inserted
 & Committed via Transaction
********************************************
********************************************
MileStone : Sample Procedure 1 Successfully created
********************************************
********************************************
MileStone : Sample Procedure 2 Successfully created
********************************************
********************************************
MileStone : Sample Procedure 3 Successfully created
********************************************
name -> [SMITH]    mgr -> [7886]
hiredate -> [2007-08-02 00:00:00]
name -> [ALLEN]    mgr -> [7110]
hiredate -> [2007-04-02 00:00:00]
name -> [KING]    mgr -> [7221]
hiredate -> [2007-03-02 00:00:00]
*************************************************
Example 1 - Using an IN OUT Parameter
*************************************************
 Field2 Before:
 size ---> 20
 length ---> 7
 value ---> Entry 3
 Field2 After:
 size ---> 20
 length ---> 10
 value ---> Successful
*************************************************
Example 2 - Using an OUT Parameter
*************************************************
 Field3 Before:
 size ---> 20
 length ---> 0
 value --->
 Field3 After:
 size ---> 20
 length ---> 10
 value ---> Successful
********************************************
MileStone : Table(s) Successfully Dropped
********************************************
********************************************
MileStone : Sample Procedure 1 Successfully dropped
********************************************
********************************************
MileStone : Sample Procedure 2 Successfully dropped
********************************************
********************************************
MileStone : Sample Procedure 3 Successfully dropped
********************************************
logged off
detached form server
free'd all handles

Jika output-nya sesuai, driver PolarDB OCI telah terinstal dan terhubung dengan benar.

Langkah selanjutnya

Setelah terhubung dengan driver OCI, Anda dapat membangun antarmuka tingkat lebih tinggi di atasnya:

• PolarDB JDBC — aplikasi Java

• PolarDB ODBC — aplikasi yang menggunakan driver kompatibel ODBC

• PolarDB .NET — aplikasi .NET