cdemodr2.c

oci程序

  /* need to initialize them to null prior to first usage in bind calls */

  for (i = 0; i < MAXBINDS; i++)
    bndhp[i] = (OCIBind *) 0;


  if (demo_insert(svchp, stmthp, bndhp, errhp))
    (void) printf("FAILED: demo_insert()\n");
  else
    (void) printf("SUCCESS: demo_insert()\n");

  if (select_locator(svchp, stmthp, bndhp, errhp))
    (void) printf("FAILED: select_locator()\n");
  else
    (void) printf("SUCCESS: select_locator()\n");

  if (demo_update(svchp, stmthp, bndhp, errhp))
    (void) printf("FAILED: demo_update()\n");
  else
    (void) printf("SUCCESS: demo_update()\n");


  if (demo_delete(svchp, stmthp, bndhp, errhp))
    (void) printf("FAILED: demo_delete()\n");
  else
    (void) printf("SUCCESS: demo_delete()\n");

  locator_free(in2, MAXITER);
  locator_free(in3, MAXITER);

  return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp,
                     stmthp, username);
}




/* ----------------------------------------------------------------- */
/* bind all the columns of TAB2 by positions.                        */
/* ----------------------------------------------------------------- */
sword bind_in_name(stmthp, bndhp, errhp)
OCIStmt *stmthp;
OCIBind *bndhp[];
OCIError *errhp;
{

  if (OCIBindByName(stmthp, &bndhp[0], errhp,
                      (text *) ":c1", (sb4) strlen((char *) ":c1"),
                      (dvoid *) &in1[0], (sb4) sizeof(in1[0]), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
   || OCIBindByName(stmthp, &bndhp[1], errhp,
                      (text *) ":c2", (sb4) strlen((char *) ":c2"),
                      (dvoid *) &in2[0], (sb4) -1, SQLT_BLOB,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
   || OCIBindByName(stmthp, &bndhp[2], errhp,
                      (text *) ":c3", (sb4) strlen((char *) ":c3"),
                      (dvoid *) &in3[0], (sb4) -1, SQLT_CLOB,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIBindByName()\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}


/* ----------------------------------------------------------------- */
/* bind all the columns of TAB2 by name.                             */
/* ----------------------------------------------------------------- */
sword bind_name(stmthp, bndhp, errhp)
OCIStmt *stmthp;
OCIBind *bndhp[];
OCIError *errhp;
{

  if (OCIBindByName(stmthp, &bndhp[10], errhp,
                      (text *) ":out1", (sb4) strlen((char *) ":out1"),
                      (dvoid *) 0, (sb4) sizeof(int), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
   || OCIBindByName(stmthp, &bndhp[11], errhp,
                      (text *) ":out2", (sb4) strlen((char *) ":out2"),
                      (dvoid *) 0, (sb4) -1, SQLT_BLOB,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
   || OCIBindByName(stmthp, &bndhp[12], errhp,
                      (text *) ":out3", (sb4) strlen((char *) ":out3"),
                      (dvoid *) 0, (sb4) -1, SQLT_CLOB,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC))
  {
    (void) printf("FAILED: OCIBindByName()\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}


/* ----------------------------------------------------------------- */
/* bind array structs for TAB2 columns.                              */
/* ----------------------------------------------------------------- */
sword bind_array(bndhp, errhp)
OCIBind *bndhp[];
OCIError *errhp;
{
  if (OCIBindArrayOfStruct(bndhp[0], errhp, s1, indsk[0], rlsk[0], rcsk[0])
   || OCIBindArrayOfStruct(bndhp[1], errhp, s2, indsk[1], rlsk[1], rcsk[1])
   || OCIBindArrayOfStruct(bndhp[2], errhp, s3, indsk[2], rlsk[2], rcsk[2]))
  {
    (void) printf("FAILED: OCIBindArrayOfStruct()\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}


/* ----------------------------------------------------------------- */
/* bind dynamic for returning TAB2 columns.                          */
/* ----------------------------------------------------------------- */
sword bind_dynamic(bndhp, errhp)
OCIBind *bndhp[];
OCIError *errhp;
{
  int  i;

  for (i = 0; i < MAXCOLS; i++)
    pos[i] = i;

  if (OCIBindDynamic(bndhp[10], errhp, (dvoid *)&pos[0], cbf_no_data,
                    (dvoid *)&pos[0], cbf_get_data)
  ||  OCIBindDynamic(bndhp[11], errhp, (dvoid *)&pos[1], cbf_no_data,
                    (dvoid *)&pos[1], cbf_get_data)
  ||  OCIBindDynamic(bndhp[12], errhp, (dvoid *)&pos[2], cbf_no_data,
                    (dvoid *)&pos[2], cbf_get_data))
  {
    (void) printf("FAILED: OCIBindDynamic()\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}



/* ----------------------------------------------------------------- */
/* bind input variables.                                             */
/* ----------------------------------------------------------------- */
sword bind_input(stmthp, bndhp, errhp)
OCIStmt *stmthp;
OCIBind *bndhp[];
OCIError *errhp;
{
  /* by the input data by names */
  if (bind_in_name(stmthp, bndhp, errhp))
    return OCI_ERROR;

  /* by input array */
  return (bind_array(bndhp, errhp));
}



/* ----------------------------------------------------------------- */
/* bind output variables.                                            */
/* ----------------------------------------------------------------- */
sword bind_output(stmthp, bndhp, errhp)
OCIStmt *stmthp;
OCIBind *bndhp[];
OCIError *errhp;
{

  /* by the returning data by names */
  if (bind_name(stmthp, bndhp, errhp))
    return OCI_ERROR;

  /* by dynamic of returning data by names */
  return (bind_dynamic(bndhp, errhp));
}



/* ----------------------------------------------------------------- */
/* bind row indicator variables.                                     */
/* ----------------------------------------------------------------- */
sword bind_low_high(stmthp, bndhp, errhp)
OCIStmt *stmthp;
OCIBind *bndhp[];
OCIError *errhp;
{
  if (OCIBindByName(stmthp, &bndhp[23], errhp,
                      (text *) ":low", (sb4) strlen((char *) ":low"),
                      (dvoid *) &lowc1[0], (sb4) sizeof(lowc1[0]), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
   || OCIBindByName(stmthp, &bndhp[24], errhp,
                      (text *) ":high", (sb4) strlen((char *) ":high"),
                      (dvoid *) &highc1[0], (sb4) sizeof(highc1[0]), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIBindByName()\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  if (OCIBindArrayOfStruct(bndhp[23], errhp, s1, indsk[0], rlsk[0], rcsk[0])
   || OCIBindArrayOfStruct(bndhp[24], errhp, s1, indsk[0], rlsk[0], rcsk[0]))
  {
    (void) printf("FAILED: OCIBindArrayOfStruct()\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}




/* ----------------------------------------------------------------- */
/* test for INSERT with RETURNING clause.                            */
/* ----------------------------------------------------------------- */
sword demo_insert(svchp, stmthp, bndhp, errhp)
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIBind *bndhp[];
OCIError *errhp;
{
  int  i, j;
  int  num[MAXITER];
  text *sqlstmt =(text *)"INSERT INTO TAB2(C1,C2,C3) VALUES \
                              (:c1, empty_blob(), empty_clob()) \
                              RETURNING C1, C2, C3 INTO \
                              :out1, :out2, :out3";

  (void) printf("\n\n========== TESTING INSERT....RETURNING \n");
  for (i = 0; i < MAXITER; i++)
    num[i] = i;

  for (i = 0; i < 1; i++)
  {
    if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
                      (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
    {
      (void) printf("FAILED: OCIStmtPrepare() insert\n");
      report_error(errhp);
      return OCI_ERROR;
    }

    if (OCIBindByName(stmthp, &bndhp[0], errhp,
                      (text *) ":c1", (sb4) strlen((char *) ":c1"),
                      (dvoid *) &num[0], (sb4) sizeof(num[0]), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT) )
    {
      (void) printf("FAILED: OCIStmtPrepare() insert\n");
      report_error(errhp);
      return OCI_ERROR;
    }

    if (OCIBindArrayOfStruct(bndhp[0], errhp, s1, indsk[0], rlsk[0], rcsk[0]))
    {
      (void) printf("FAILED: OCIStmtPrepare() insert\n");
      report_error(errhp);
      return OCI_ERROR;
    }

    if (bind_output(stmthp, bndhp, errhp))
      return OCI_ERROR;

    if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) MAXITER, (ub4) 0,
                      (CONST OCISnapshot*) 0, (OCISnapshot*) 0,
                      (ub4) OCI_DEFAULT))
    {
      (void) printf("FAILED: OCIStmtExecute() insert\n");
      report_error(errhp);
      return OCI_ERROR;
    }
  }

  (void) check_lob((int)MAXITER, svchp);

  return OCI_SUCCESS;
}



/* ----------------------------------------------------------------- */
/* test for UPDATE with RETURNING clause.                            */
/* ----------------------------------------------------------------- */
sword demo_update(svchp, stmthp, bndhp, errhp)
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIBind *bndhp[];
OCIError *errhp;
{
  int    i, j;
  int    range_size = 3;

  text *sqlstmt = (text *)
                   "UPDATE TAB2 SET C1 = C1 + :c1, C2 = :c2, C3 = :c3 \
                           WHERE C1 >= :low AND C1 <= :high \
                           RETURNING C1, C2, C3 INTO :out1, :out2, :out3";

  (void) printf("\n\n========== TESTING UPDATE....RETURNING \n");

  if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() update\n");
    report_error(errhp);
    return OCI_ERROR;
  }


  for (i = 0; i < MAXITER; i++)
  {
    in1[i] = 301 + i;

    /* in2[], in3[] have been loaded in select_locator() */

    rowsret[i] =0;
  }

  /* bind input */
  if (bind_input(stmthp, bndhp, errhp))
    return OCI_ERROR;

  /* bind output */
  if (bind_output(stmthp, bndhp, errhp))
    return OCI_ERROR;

  /* bind row indicator low, high */
  if (bind_low_high(stmthp, bndhp, errhp))
    return OCI_ERROR;

  /* update rows
         between 101 and 103;  --  expecting 3 rows returned.
         between 105 and 106;  --  expecting 2 rows returned.
         between 108 and 110;  --  expecting 3 rows returned.
  */

  lowc1[0] = 101;
  highc1[0] = 103;

  lowc1[1] = 105;
  highc1[1] = 106;

  lowc1[2] = 109;
  highc1[2] = 113;

  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) range_size, (ub4) 0,
                    (CONST OCISnapshot*) 0, (OCISnapshot*) 0,
                    (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() update\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  (void) check_lob(range_size, svchp);

  return OCI_SUCCESS;
}




/* ----------------------------------------------------------------- */
/* test for DELETE with RETURNING clause.                            */
/* ----------------------------------------------------------------- */
sword demo_delete(svchp, stmthp, bndhp, errhp)
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIBind *bndhp[];
OCIError *errhp;
{
  int    i, range_size = 3;
  sword  retval;

  text *sqlstmt = (text *)
                   "DELETE FROM TAB2 WHERE C1 >= :low AND C1 <= :high \
                            RETURNING C1, C2, C3 INTO :out1, :out2, :out3";

  (void) printf("\n\n========== TESTING DELETE....RETURNING \n");

  if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() delete\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  /* bind output */
  if (bind_output(stmthp, bndhp, errhp))
    return OCI_ERROR;

  /* bind row indicator low, high */
  if (bind_low_high(stmthp, bndhp, errhp))