testlora.c

Linux下的操作oracle数据库的连接库

  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, data[i].cval, 
                 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, data[i].cval, 
                 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)))
    {
      if (SQLO_SUCCESS != 
          (sqlo_bind_by_name(sth, ":ip1", SQLOT_FLT, &ip1, sizeof(ip1),0,0) ||
           sqlo_bind_by_name(sth, ":ip2", SQLOT_INT, &ip2, sizeof(ip2),0,0) ||
           sqlo_bind_by_name(sth, ":op1", SQLOT_INT, &op1, sizeof(op1),0,0) ||
           sqlo_bind_by_name(sth, ":op2", SQLOT_STR, op2, sizeof(op2),0,0)
           ))
        {
          printf("sqlo_bind_by_name failed failed: %s\n", sqlo_geterror(dbh) );
          return 0;
        }
      else
        {
          while (SQLO_STILL_EXECUTING == (status = sqlo_execute(sth, 1)))
            {
              /* do something */
              printf(".");
              SQLO_USLEEP;
            }
          printf("\n");

          if (SQLO_SUCCESS != status) 
            {
              printf("sqlo_execute failed: %s\n", sqlo_geterror(dbh) );
              return 0;
            }
        }
#ifdef CLOSE_CURSOR
      if (SQLO_SUCCESS != sqlo_close(sth))
        {
          printf("sqlo_close failed: %s\n", sqlo_geterror(dbh) );
          return 0;
        }
#endif
      printf ("ip1: %.16f, ip2: %d, op1: %d, op2: %s\n", ip1, ip2, op1, op2);
    }
  else
    {
      printf("sqlo_prepare failed: Status: %d, %s\n", sth, sqlo_geterror(dbh) );
      return 0;
    }
  printf("test_plsql finished\n");
  return 1;
}

/*-------------------------------------------------------------------------
 * test_insert with bind by pos
 *-----------------------------------------------------------------------*/
int test_insert( int dbh, int thread_id, int delete_flag )
{
  int nkey;
  char ckey[6];
  double nval;
  char cval[21];
  char dval[12];
  int status;
  int sth = SQLO_STH_INIT;
  char fval[255];

  char * insert_stmt = 
    "INSERT INTO T_SQLORA_TEST (THREAD_ID, NKEY, CKEY, NVAL, CVAL, DVAL, FVAL) VALUES (:THR_ID, :NKEY, :CKEY, :NVAL, :CVAL, TO_DATE(:DVAL,'DD-MM-YYYY'), :FVAL)";



  printf("Testing Insert (bind by pos)\n");

  if (!create_table(dbh))
    return 0;


  nkey = 100;
  strcpy(ckey, "ckey");
  nval = 1234567890.001;
  strcpy(fval," 98765432109876543210987654321098765432109876543210.01");
  strcpy(cval,"aaaaaaaaaaaaaaaaaaaa");
  strcpy(dval,"01-JUL-2000");

  if (0 <= (sth = sqlo_prepare(dbh, insert_stmt)))
    {
      if (SQLO_SUCCESS != 
          (sqlo_bind_by_pos(sth, 1, SQLOT_INT, &thread_id, sizeof(int),0,0) ||
           sqlo_bind_by_pos(sth, 2, SQLOT_INT, &nkey, sizeof(int),0,0) ||
           sqlo_bind_by_pos(sth, 3, SQLOT_STR, ckey, sizeof(ckey),0,0) ||
           sqlo_bind_by_pos(sth, 4, SQLOT_FLT, &nval, sizeof(double),0,0) ||
           sqlo_bind_by_pos(sth, 5, SQLOT_STR, cval, strlen(cval)+1,0,0) ||
           sqlo_bind_by_pos(sth, 6, SQLOT_STR, dval, strlen(dval)+1,0,0) ||
           sqlo_bind_by_pos(sth, 7, SQLOT_STR, fval, strlen(fval)+1,0,0)
           ))
        {
          printf("sqlo_bind_by_pos failed at line %d: %s\n", __LINE__, sqlo_geterror(dbh) );
          return 0;
        }
      else
        {
          printf("Inserting the values\n");
          while (SQLO_STILL_EXECUTING == (status = sqlo_execute(sth, 1)))
            {
              /* do something */
              printf(".");
              SQLO_USLEEP;
            }
          printf("\n");
          if (SQLO_SUCCESS != status)
            {
              printf("sqlo_execute failed at line %d: %s\n", 
                     __LINE__, sqlo_geterror(dbh) );
              return 0;
            }
        }
#ifdef CLOSE_CURSOR
      if (SQLO_SUCCESS != sqlo_close(sth))
        {
          printf("sqlo_close failed: %s\n", sqlo_geterror(dbh) );
          return 0;
        }
#endif

      if (delete_flag)
        if (!do_select(dbh, thread_id))
          return 0;
    }
  else
    {
      printf("sqlo_prepare failed: Status: %d, %s\n", sth, sqlo_geterror(dbh) );
      return 0;
    }

  if (delete_flag)
    if (!delete_table(dbh, "T_SQLORA_TEST", thread_id))
      return(0);

  printf("finished test_insert\n");
  return 1;
}

/*-------------------------------------------------------------------------
 * test_array_insert
 *-----------------------------------------------------------------------*/
int test_array_insert( int dbh, int thread_id, int delete_flag )
{
  int nkey[MAX_ITERS];
  char ckey[MAX_ITERS][6];
  double nval[MAX_ITERS];
  char cval[MAX_ITERS][21];
  char dval[MAX_ITERS][16];
  char fval[MAX_ITERS][255];

  short nind[MAX_ITERS];
  short cind[MAX_ITERS];
  short dind[MAX_ITERS];
  short find[MAX_ITERS];
  int thread_ids[MAX_ITERS];

  int sth, i, j;
  int status;

  char * insert_stmt = 
    "INSERT INTO T_SQLORA_TEST (THREAD_ID, NKEY, CKEY, NVAL, CVAL, DVAL, FVAL) VALUES (:THR_ID, :NKEY, :CKEY, :NVAL, :CVAL, TO_DATE(:DVAL,'DD-MM-YYYY'), :FVAL)";

  printf("Testing Array Insert (bind by name)\n");


  if (!create_table(dbh))
    return 0;

  /* setup bind arrays */
  for ( i = 0 ; i < MAX_ITERS; i++)
    {
      nkey[i] = i+1;
      sprintf(ckey[i], "%c", 'A' + i % 26 );
      nval[i] = 1234567890.0 + i / 1000.0;
      strcpy(fval[i],"123456789012345678901234567890123456789012345678901234567890");
      for (j = 0; j < 20; j++)
        cval[i][j] = 'a' + i % 26;
      cval[i][20] = '\0';
      sprintf(dval[i], "%02d-JUL-2000", (i % 30 ) + 1);
      
      nind[i] = 0;
      cind[i] = 0;
      dind[i] = 0;
      find[i] = 0;
      thread_ids[i] = thread_id;
    }

  if (0 <= (sth = sqlo_prepare(dbh, insert_stmt)))
    {
      if (SQLO_SUCCESS != 
          (sqlo_bind_by_name(sth, ":NKEY", SQLOT_INT, &nkey[0], sizeof(int), NULL,1) ||
           sqlo_bind_by_name(sth, ":CKEY", SQLOT_STR, &ckey[0], 6, NULL,1) ||
           sqlo_bind_by_name(sth, ":NVAL", SQLOT_FLT, &nval[0], sizeof(double), nind,1) ||
           sqlo_bind_by_name(sth, ":CVAL", SQLOT_STR, &cval[0], 21, cind,1) ||
           sqlo_bind_by_name(sth, ":DVAL", SQLOT_STR, &dval[0], 16, dind,1) ||
           sqlo_bind_by_name(sth, ":THR_ID", SQLOT_INT, &thread_ids[0], sizeof(int), NULL,1) ||
           sqlo_bind_by_name(sth, ":FVAL", SQLOT_STR, &fval[0], 255, find,1)
           ))
        {
          printf("sqlo_bind_by_name failed failed: %s\n", sqlo_geterror(dbh) );
          return 0;
        }
      else
        {
          while (SQLO_STILL_EXECUTING == (status = sqlo_execute(sth, MAX_ITERS)))
            {
              /* do something */
              printf(".");
              SQLO_USLEEP;
            }

          printf("\n");

          if (SQLO_SUCCESS != status)
            {
              printf("sqlo_execute failed: %s\n", sqlo_geterror(dbh) );
              return 0;
            }
        }
#ifdef CLOSE_CURSOR
      if (SQLO_SUCCESS != sqlo_close(sth))
        {
          printf("sqlo_close failed: %s\n", sqlo_geterror(dbh) );
          return 0;
        }
#endif
      if (delete_flag)
        if (!do_select(dbh, thread_id))
          return 0;
    }
  else
    {
      printf("sqlo_prepare failed: Status: %d, %s\n", sth, sqlo_geterror(dbh) );
      return 0;
    }

  if (delete_flag)
    if (!delete_table(dbh, "T_SQLORA_TEST", thread_id))
      return(0);

  return 1;
}


/*-------------------------------------------------------------------------
 * test_array_insert2 (by pos)
 *-----------------------------------------------------------------------*/
int test_array_insert2( int dbh, int thread_id, int delete_flag )
{
  int nkey[MAX_ITERS];
  char ckey[MAX_ITERS][6];
  double nval[MAX_ITERS];
  char cval[MAX_ITERS][21];
  char dval[MAX_ITERS][16];
  double fval[MAX_ITERS];

  short nind[MAX_ITERS];
  short cind[MAX_ITERS];
  short dind[MAX_ITERS];
  short find[MAX_ITERS];

  int thread_ids[MAX_ITERS];

  int i, j;
  int status;
  int sth;

  char * insert_stmt = 
    "INSERT INTO T_SQLORA_TEST (THREAD_ID, NKEY, CKEY, NVAL, CVAL, DVAL, FVAL) VALUES (:THR_ID, :NKEY, :CKEY, :NVAL, :CVAL, TO_DATE(:DVAL,'DD-MM-YYYY'), :FVAL)";

  printf("Testing Array Insert ( bind by pos)\n");


  if (!create_table(dbh))
    return (0);

  /* setup bind arrays */
  for ( i = 0 ; i < MAX_ITERS; i++)
    {
      nkey[i] = i+1;
      sprintf(ckey[i], "%c", 'A' + i % 26 );
      nval[i] = 1234567890.0 + i / 1000.0;
      fval[i] = nval[i];
      for (j = 0; j < 20; j++)
        cval[i][j] = 'a' + i % 26;
      cval[i][20] = '\0';
      sprintf(dval[i], "%02d-JUL-2000", (i % 30) + 1);
      
      nind[i] = 0;
      cind[i] = 0;
      dind[i] = 0;