testlora.c

来自「一个很好用的Linux/Unix下Oracle OCI开发接口封装库」· C语言 代码 · 共 1,757 行 · 第 1/4 页

        {
          printf("%*s ", ocol_name_lens[i] > (int) vl[i] ?
                 ocol_name_lens[i] : (int) vl[i]
                 , v[i]);
        }
      printf("\n");
      /*      printf("\n%4s%6s%19s%21s%11s\n", v[0], v[1], v[2], v[3], v[4]);*/
      ++nrows;
    }

  if (status < 0)
    {
      printf("sqlo_fetch failed: %s\n", sqlo_geterror(dbh));
    }

#ifdef CLOSE_CURSOR
  if (0 > sqlo_close(sth))
    {
      printf("sqlo_close failed: %s\n", sqlo_geterror(dbh));
      return 0;
    }
#endif
  return 1;

}
/*-------------------------------------------------------------------------
 * Select with prepare/execute/fetch.
 *-----------------------------------------------------------------------*/
int test_select2( int dbh, int thread_id )
{
  int sth = SQLO_STH_INIT;

  /* Define a structure to hold one record */
  typedef struct _array_of_struct_t {
    int thread_id;
    int nkey;
    char ckey[6];
    double nval;
    short nval_ind;
    char cval[4001];
    short cval_ind;
    char dval[11];
    short dval_ind;
    char fval[128];
    short fval_ind;
    unsigned short ckeyl;
    unsigned short cvall;
    unsigned short dvall;
    unsigned short fvall;
    unsigned short fvalrc;
  } aos_t;

  aos_t data[MAX_ARRAYSIZE];    /* A array of this structures */

  int status;
  int i;

  int rows_fetched = 0;
  int rows_fetched_total = 0;
  int rows_to_fetch;
  int done_fetching = 0;
  int ncols;
  int dtype;

  char * select_stmt =
    "SELECT THREAD_ID THID, NKEY, CKEY, NVAL, CVAL, TO_CHAR(DVAL,'DD-MM-YYYY') AS DVAL, FVAL FROM T_SQLORA_TEST WHERE THREAD_ID = :1";
  
  printf("Test select via classic methods\n");


  /* Insert test data */
  if (!test_array_insert(dbh, thread_id, 0))
    return 0;

  /* Select all and display */
  if (0>(sth = sqlo_prepare(dbh, select_stmt)))
    {
      printf("sqlo_prepare failed: %s\n", sqlo_geterror(dbh));
      return 0;
    }

  sqlo_set_prefetch_rows(sth, 2 * MAX_ARRAYSIZE);

  /* Bind input */
  if (SQLO_SUCCESS != 
      (sqlo_bind_by_name(sth, ":1", SQLOT_INT, &thread_id, sizeof(int), NULL, 0)))
    {
      printf("sqlo_bind_by_name failed: %s\n", sqlo_geterror(dbh));
      return 0;
    }

  /* Print the select list data types */
  if ( 0 > (ncols = sqlo_ncols(sth, 0))) 
    {
      printf("sqlo_ncols failed: %s\n", sqlo_geterror(dbh));
      return 0;
    }

  for ( i = 1; i <= ncols; ++i) 
    {
      if (0 > (dtype = sqlo_get_ocol_dtype(sth, i)))
        {
          printf("sqlo_get_ocol_dtype failed: %s\n", sqlo_geterror(dbh));
          return 0;
        }
      printf("Column: %d DataType: %d\n", i, dtype);
    }

  /* Define Output */
  
  if (SQLO_SUCCESS !=
      (sqlo_define_by_pos2(sth, 1, SQLOT_INT, &data[0].thread_id, sizeof(int),0, 0,
                           0, sizeof(aos_t)) ||
       sqlo_define_by_pos2(sth, 2, SQLOT_INT, &data[0].nkey, sizeof(int),0, 0, 
                          0, sizeof(aos_t)) ||
       sqlo_define_by_pos2(sth, 3, SQLOT_STR, data[0].ckey, sizeof(data[0].ckey), 0, 
                           &data[0].ckeyl, 0, sizeof(aos_t)) ||
       sqlo_define_by_pos2(sth, 4, SQLOT_FLT, &data[0].nval, sizeof(double), &data[0].nval_ind, 0, 
                           0, sizeof(aos_t)) ||
       sqlo_define_by_pos2(sth, 5, SQLOT_STR, data[0].cval, sizeof(data[0].cval), &data[0].cval_ind, 
                           0, &data[0].cvall, sizeof(aos_t)) ||
       sqlo_define_by_pos2(sth, 6, SQLOT_STR, data[0].dval, sizeof(data[0].dval), &data[0].dval_ind, 
                           0, &data[0].dvall, sizeof(aos_t)) ||
       sqlo_define_by_pos2(sth, 7, SQLOT_STR, data[0].fval, sizeof(data[0].fval), &data[0].fval_ind, 
                           &data[0].fvall, &data[0].fvalrc, sizeof(aos_t))))
    {
      printf("sqlo_define_by_pos2 failed: %s\n", sqlo_geterror(dbh));
      return 0;
    }

  rows_to_fetch = 3;
  rows_fetched = rows_to_fetch;

  while ( SQLO_STILL_EXECUTING == (status = sqlo_execute(sth, rows_to_fetch)))
    {
      /* do something. */
      printf(".");
      SQLO_USLEEP;

    }
          
  printf("\n");

  if (status < 0)
    {
      printf("sqlo_execute failed (status=%d): %s\n", status, sqlo_geterror(dbh) );
      return(0);
    }
  else if (status == SQLO_NO_DATA)
    {
      /* arrays were filled fully. Get rowcount */
      rows_fetched = sqlo_prows(sth);
      done_fetching = 1;
      printf("Execute fetched all %d rows\n", rows_fetched);

      printf("Fetched all in one go\n");

      for (i = 0; i < rows_fetched; ++i)
        {
          printf("%4d %3d %5s %19f %20s %10s\n", 
                 data[i].thread_id, data[i].nkey, data[i].ckey, data[i].nval, 
                 data[i].cval, data[i].dval);
        }
    }
  
  for (i = 0; i < rows_fetched; ++i)
    {
      if (!i)
        printf("Execute fetched %d rows\n", rows_fetched);

          printf("%4d %3d %5s %19f %20s %10s\n", 
                 data[i].thread_id, data[i].nkey, data[i].ckey, 
                 data[i].nval_ind ? 0.0 : data[i].nval,
                 data[i].cval_ind ? "<null>" : data[i].cval,
                 data[i].dval_ind ? "<null>" : data[i].dval
                 );
    }

  rows_fetched_total += rows_fetched;
  rows_to_fetch = 4;

  while(!done_fetching)
    {
      rows_fetched = rows_to_fetch;
      while (SQLO_STILL_EXECUTING == (status = sqlo_fetch(sth, rows_to_fetch)))
        {
          printf(".");
          usleep(20000);
        }

      printf("\n");

      if (status < 0)
        {
          printf("sqlo_fetch failed: %s\n", sqlo_geterror(dbh));
          return 0;
        }

      if (status ==  SQLO_NO_DATA)
        {
          rows_fetched = sqlo_prows(sth);
          
          /* The last call returns the total number of fetched rows
           * the difference to the previous total fechted rows is
           * the number of rows fetched in this last call to sqlo_execute
           */
          rows_fetched = rows_fetched - rows_fetched_total;
          done_fetching = 1;
          printf("sqlo_fetch fetched last %d rows\n", rows_fetched);
        }
      else if (status == SQLO_SUCCESS)
        {
          printf("sqlo_fetch fetched %d rows\n", rows_fetched);

        }
      else
        {
          printf("sqlo_fetch failed: %s\n", sqlo_geterror(dbh));
          return 0;
        }

      for (i = 0; i < rows_fetched; ++i)
        {
          printf("%4d %3d %5s %19f %20s %10s\n", 
                 data[i].thread_id, data[i].nkey, data[i].ckey, 
                 data[i].nval_ind ? 0.0 : data[i].nval,
                 data[i].cval_ind ? "<null>" : data[i].cval,
                 data[i].dval_ind ? "<null>" : data[i].dval
                 );
        }
      rows_fetched_total += rows_fetched;
    }
  printf("Fetched %d rows in total (should be %d)\n", rows_fetched_total, MAX_ARRAYSIZE+1);
#ifdef CLOSE_CURSOR
  if (0 > sqlo_close(sth))
    {
      printf("sqlo_close failed: %s\n", sqlo_geterror(dbh));
      return 0;
    }
#endif
  return 1;

}

/*-------------------------------------------------------------------------
 * test_reopen
 *-----------------------------------------------------------------------*/
int test_reopen( int dbh, int thread_id )
{
  int sth = SQLO_STH_INIT;
  const char **v;
  int argc;
  char sthread_id[16];
  const char *argv[1];
  int status;

  char * select_stmt =
    "SELECT NKEY, CKEY, NVAL, CVAL, TO_CHAR(DVAL,'DD-MM-YYYY') AS DVAL,FVAL FROM T_SQLORA_TEST WHERE THREAD_ID = :1";

  printf("Test reopen\n");

  sprintf(sthread_id, "%d", thread_id);
  argc = 0;
  argv[argc++] = sthread_id;

  /* Select all and display */
  while (SQLO_STILL_EXECUTING == 
      (status = sqlo_open2(&sth, dbh, select_stmt, argc, argv)))
    {
      printf(".");
      SQLO_USLEEP;
    }
  printf("\n");

  if (0>status)
    {
      printf("sqlo_open2 failed: %s\n", sqlo_geterror(dbh));
      return 0;
    }

  while (SQLO_STILL_EXECUTING  == (status = sqlo_fetch(sth, 1)))
    {
      printf(".");
      SQLO_USLEEP;
    }

  printf("\n");
  if (SQLO_SUCCESS == status)
    {
      do 
        {
          v = sqlo_values(sth, NULL, 1);
          printf("%s|%6s%19s%21s%11s%s\n", v[0], v[1], v[2], v[3], v[4], v[5]);
          
          while (SQLO_STILL_EXECUTING  == (status = sqlo_fetch(sth, 1)))
            {
              printf(".");
              SQLO_USLEEP;
            }

          printf("\n");
        } while (status == SQLO_SUCCESS);
    }

  argv[0] = sthread_id;

  while (SQLO_STILL_EXECUTING == (status = sqlo_reopen(sth, argc, argv)))
    {
      printf(".");
      SQLO_USLEEP;
    }
  printf("\n");

  if (SQLO_SUCCESS != status)
    {
      printf("sqlo_reopen failed: %s\n", sqlo_geterror(dbh));
      return 0;
    }

  printf("Fetch again\n");

  while (0 == (status = sqlo_fetch(sth,1)) || status == SQLO_STILL_EXECUTING)
    {
      if (status == SQLO_STILL_EXECUTING) {
        SQLO_USLEEP;
        continue;
      }
      v = sqlo_values(sth, NULL, 0);
      printf("%s|%6s%19s%21s%11s\n", v[0], v[1], v[2], v[3], v[4]);
    }

#ifdef CLOSE_CURSOR
  if (0 > sqlo_close(sth))
    {
      printf("sqlo_close failed: %s\n", sqlo_geterror(dbh));
      return 0;
    }
#endif
  printf("Test reopen ok\n");
  return 1;

}

/*-------------------------------------------------------------------------
 * create_packages
 *-----------------------------------------------------------------------*/
int create_packages( int dbh )
{
  int status;

  char * create_pack = 
    "CREATE OR REPLACE PACKAGE SQLORA_TEST IS\n"
    "  PROCEDURE P1(ip1 IN NUMBER, ip2 IN NUMBER, op1 OUT NUMBER, op2 OUT VARCHAR);\n"
    "END;\n";

  char * create_pack_body = 
    "CREATE OR REPLACE PACKAGE BODY SQLORA_TEST IS\n"
    "  PROCEDURE P1(ip1 IN NUMBER, ip2 IN NUMBER, op1 OUT NUMBER, op2 OUT VARCHAR)\n"
    "  IS \n"
    "  BEGIN\n"
    "     op1 := TO_NUMBER(ip1) + ip2;\n"
    "     op2 := TO_CHAR(op1);\n"
    "  END;\n"
    "END;\n";

  printf("Creating packages, dbh=%d\n", dbh);

  /* Check if the package already exists */
  if (SQLO_NO_DATA == 
      sqlo_exists(dbh, "USER_OBJECTS", "OBJECT_NAME", "SQLORA_TEST", "OBJECT_TYPE = 'PACKAGE'"))
    {
      printf("Create package SQLORA_TEST\n");
      while (SQLO_STILL_EXECUTING == (status = sqlo_exec(dbh, create_pack)))
        {
          printf(".");
          SQLO_USLEEP;
        }

      printf("\n");

      if ( 0 > status )
        {
          printf("sqlo_exec failed: %s\n%s\n",sqlo_geterror(dbh), create_pack );
          return 0;
        }
      printf("Package SQLORA_TEST created\n");
    }
  
  if (SQLO_NO_DATA == 
      sqlo_exists(dbh, "USER_OBJECTS", "OBJECT_NAME", "SQLORA_TEST", "OBJECT_TYPE = 'PACKAGE BODY'"))
    {
      printf("Create package body SQLORA_TEST\n");
      while (SQLO_STILL_EXECUTING == (status = sqlo_exec(dbh, create_pack_body)))
        {
          printf(".");
          SQLO_USLEEP;
        }
      printf("\n");

      if (0 > status)
        {
          printf("sqlo_exec failed: (status=%d) %s\n%s\n", status, sqlo_geterror(dbh), create_pack_body );
          return 0;
        }
      printf("Package body SQLORA_TEST created\n");
    }
  fflush(stdout);
  fflush(stderr);

  return 1;
}

/*-------------------------------------------------------------------------
 * test_plsql
 *-----------------------------------------------------------------------*/
int test_plsql( int dbh, int thread_id )
{
  int  ip2, op1;
  double ip1;
  char op2[40];
  int sth = SQLO_STH_INIT;
  int status;

  char * stmt = 
    "BEGIN\n"
    "    SQLORA_TEST.P1(:ip1, :ip2, :op1, :op2);\n"
    "END;\n";

  printf("test_plsql starts\n");

  if (!create_packages(dbh))
    return 0;

  ip1 = 1.123456789012345;
  ip2 = 20;
  op1 = 0;
  *op2 = 0;

  if (0 <= (sth = sqlo_prepare(dbh, stmt)))
    {