testlora.c

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

/* $Id: testlora.c,v 1.32 2004/05/28 03:04:15 kpoitschke Exp $
 *
 * Copyright (c) 1991-2004 Kai Poitschke ([e-mail])
 *  
 * 
 * This file is part of the libsqlora8 package which can be found
 * at http://www.poitschke.de/libsqlora8/
 *
 *
 *  Permission to use, copy, modify, and distribute this software for
 *  any purpose with or without fee is hereby granted, provided that
 *  the above copyright notice and this permission notice appear in all
 *  copies.
 *
 *  THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
 *  WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
 *  MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
 *  IN NO EVENT SHALL THE AUTHORS AND COPYRIGHT HOLDERS AND THEIR
 *  CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 *  SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 *  LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
 *  USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 *  ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
 *  OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
 *  OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
 *  SUCH DAMAGE.
 *
 * testlora.c 
 * Test programm for libsqlora8.a (Kai Poitschke)
 *-----------------------------------------------------------------------*/
#ifdef HAVE_CONFIG_H
#include "config.h"             /* read config to get ENABLE_PTHREADS */
#endif

#include <stdio.h>
#include <unistd.h>
#include <signal.h>
#include <string.h>
#include <malloc.h>
#if (defined(ENABLE_PTHREADS) && defined(HAVE_PTHREAD_H))
#  include <pthread.h>
#endif

#if defined(ENABLE_ORATHREADS)
#define ENABLE_PTHREADS
#endif

#include "sqlora.h"


#define MAX_ITERS 100

#define MAX_LOOPS 5           /* number of runs/threads */

#define CLOSE_CURSOR 1

#define MAX_ARRAYSIZE 100


static int g_dbh[MAX_LOOPS];
static char * g_cstr = NULL;
static int g_nonblocking_mode = 0;

#if defined(ENABLE_PTHREADS)  && defined(HAVE_PTHREAD_H)
static pthread_t g_thr[MAX_LOOPS];
static pthread_mutex_t g_create_tables_lock;
#endif


/* If we have usleep we wait this amount of microseconds in a 
 * OCI_STILL_EXECUTING loop
 */
#ifdef HAVE_USLEEP
#  define SQLO_USLEEP usleep(20000)
#else
#  define SQLO_USLEEP
#endif

#undef __P
#if (defined(PROTOTYPES) || defined(__STDC__) || defined(__cplusplus) )
# define __P(protos) protos
#else
# define __P(protos) ()
#endif

int test_array_insert __P(( int dbh, int thread_id, int delete_flag ));
void sig_handler __P((int signal));
int create_table __P((int dbh));
int create_table_long __P((int dbh));
int delete_table __P((int dbh, char *table_name, int thread_id));
int insert_into_long_table __P((int dbh, int thread_id));
int select_from_long_table __P((int, int thread_id));
int test_long __P((int dbh, int thread_id));
int do_select __P((int dbh, int thread_id));
int test_select2 __P((int dbh, int thread_id));
int test_reopen __P((int dbh, int thread_id));
int create_packages __P((int dbh));
int test_plsql __P((int dbh, int thread_id));
int test_insert __P(( int dbh, int thread_id, int delete_flag));
int test_array_insert __P((int dbh, int thread_id, int delete_flag));
int test_array_insert2 __P(( int dbh, int thread_id, int delete_flag ));
int test_exists __P((int dbh, int thread_id));
int test_count __P((int dbh, int thread_id));
int test_exec __P((int dbh, int thread_id));
int create_tables __P((int dbh));
int cleanup __P((int dbh));
int delete_table __P(( int dbh, char * table_name, int thread_id ));

#if defined(ENABLE_PTHREADS)  && defined(HAVE_PTHREAD_H)
void * run_test __P((void * arg));
#else
int run_test __P((int arg));
#endif

static int start_test __P((void));
static int join_threads __P((void));

void sig_handler(int signal)
{
  abort();
}

/*-------------------------------------------------------------------------
 * create our test table
 *-----------------------------------------------------------------------*/
int create_table( int dbh )
{
  int status;

  char * create_table = 
    "CREATE TABLE T_SQLORA_TEST (\n"
    "THREAD_ID NUMBER     NOT NULL,\n"
    "NKEY NUMBER(8)       NOT NULL,\n"
    "CKEY VARCHAR2(5)     NOT NULL,\n"
    "NVAL NUMBER(16,4)    NULL,\n"
    "CVAL VARCHAR2(20)    NULL,\n"
    "FVAL FLOAT(126)    NULL,\n"
    "DVAL DATE)";

  printf("Create table T_SQLORA_TEST\n");

  /* Check if the table already exists */
  if (SQLO_NO_DATA == 
      sqlo_exists(dbh, "USER_TABLES", "TABLE_NAME", "T_SQLORA_TEST", NULL))
    {
      /* No, create it */
      while (SQLO_STILL_EXECUTING == (status = sqlo_exec(dbh, create_table)))
        {
          printf(".");
          SQLO_USLEEP;
        }
      printf("\n");

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

  return 1;
}

/*-------------------------------------------------------------------------
 * create  a test table with a long field
 *-----------------------------------------------------------------------*/
int create_table_long( int dbh )
{
  int status;

  char * create_table = 
    "CREATE TABLE T_SQLORA_TEST_LONG (\n"
    "THREAD_ID NUMBER     NOT NULL,\n"
    "DATA LONG)";

  printf("Create Table T_SQLORA_TEST_LONG\n");
  /* Check if the table already exists */
  if (SQLO_NO_DATA == 
      sqlo_exists(dbh, "USER_TABLES", "TABLE_NAME", "T_SQLORA_TEST_LONG", NULL))
    {
      /* No, create it */
      while (SQLO_STILL_EXECUTING == (status = sqlo_exec(dbh, create_table)))
        {
          printf(".");
          SQLO_USLEEP;
        }
      printf("\n");

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

  return 1;
}

/*-------------------------------------------------------------------------
 * delete the test table
 *-----------------------------------------------------------------------*/
int delete_table( int dbh, char * table_name, int thread_id )
{
  char sqlcmd[512];
  int status;

  sprintf(sqlcmd, "DELETE %s WHERE THREAD_ID=%d", table_name, thread_id);

  while ( SQLO_STILL_EXECUTING == (status = sqlo_exec(dbh, sqlcmd)))
    {
      printf(".");
      SQLO_USLEEP;
    }
  printf("\n");

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

  return 1;
}

/*-------------------------------------------------------------------------
 * Insert data into T_SQLORA_TEST_LONG 
 *-----------------------------------------------------------------------*/
int insert_into_long_table( int dbh, int thread_id )
{
#define MAX_DATA 65535
  char * stmt = 
    "INSERT INTO T_SQLORA_TEST_LONG (THREAD_ID, DATA) VALUES (:b1, :b2)";
  int i;
  int argc;
  char const * argv[3];
  char data[MAX_DATA+1];
  char sthread_id[16];

  printf("Insert data into long table\n");

  sprintf(sthread_id, "%d", thread_id);
  /* fill the data */
  for (i = 0; i < MAX_DATA; ++i)
    {
      data[i] = 'A' + i % 26;
    }

  data[i] = '\0';
  argc = 0;
  argv[argc++] = sthread_id;
  argv[argc++] = data;

  if ( 0 > sqlo_run(dbh, stmt, argc, argv))
    {
      printf("sqlo_run failed: %s\n", sqlo_geterror(0));
      return (0);
    }

  return (1);

}

/*-------------------------------------------------------------------------
 * Select data from T_SQLORA_TEST_LONG 
 *-----------------------------------------------------------------------*/
int select_from_long_table( int dbh, int thread_id )
{
  char * stmt = 
    "SELECT THREAD_ID, DATA FROM T_SQLORA_TEST_LONG";
  int status;
  int sth = SQLO_STH_INIT;
  char const **v;

  printf("Select data from long table\n");

  while ( SQLO_STILL_EXECUTING == (status= sqlo_open2(&sth, dbh, stmt, 0, NULL)))
    {
      SQLO_USLEEP;
    }

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

  while (SQLO_SUCCESS == (status = sqlo_fetch(sth, 1)) || status == SQLO_STILL_EXECUTING)
    {
      if (status == SQLO_STILL_EXECUTING) 
        {
          SQLO_USLEEP;
          continue;
        }

      v = sqlo_values(sth, NULL, 0);
      printf("ThreadID: %s, Data: %.60s\n", v[0], v[1]);
    }

  if (status < 0) 
    {
      printf("ERROR during fetch: %s", sqlo_geterror(dbh));
      return 0;
    }
  sqlo_close(sth);

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

  return (1);

}


/*-------------------------------------------------------------------------
 * test_long
 *-----------------------------------------------------------------------*/
int test_long ( int dbh, int thread_id )
{
  printf ("Test long\n");
  if (!create_table_long(dbh))
    return (0);

  if (!insert_into_long_table(dbh, thread_id))
    return (0);

  if (!select_from_long_table(dbh, thread_id))
    return (0);

  printf ("Test long ok\n");
  return (1);
}



/*-------------------------------------------------------------------------
 * Query the test table
 *-----------------------------------------------------------------------*/
int do_select( int dbh, int thread_id )
{
  int sth = SQLO_STH_INIT;
  const char **v;
  int argc;
  const char *argv[1];
  const char **ocol_names;
  const int * ocol_name_lens;
  const unsigned short * vl;
  int nrows;
  char sthread_id[16];

  int n_ocols;
  int i;
  int status;

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

  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 at line %d: %s\n%s\n", __LINE__, 
             sqlo_geterror(dbh),
             select_stmt);
      return 0;
    }

  if (! (ocol_names = sqlo_ocol_names(sth, &n_ocols)))
    {
      printf("sqlo_ocol_names failed: %s\n", sqlo_geterror(sth));
      return 0;
    }


  if (! (ocol_name_lens = sqlo_ocol_name_lens(sth, NULL)))
    {
      printf("sqlo_ocol_name_lens failed: %s\n", sqlo_geterror(sth));
      return 0;
    }

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

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


  
  nrows = 0;
  while (0 == (status = sqlo_fetch(sth, 1)) || status == SQLO_STILL_EXECUTING)
    {
      if (status == SQLO_STILL_EXECUTING) {
        SQLO_USLEEP;
        continue;
      }

      v = sqlo_values(sth, NULL, 1);
      vl = sqlo_value_lens(sth, NULL);

      if ( 0 == nrows % 24 ) 
        {
          for (i = 0; i < n_ocols; ++i) 
            {
              printf("%-*s ", ocol_name_lens[i] > (int)vl[i] ?
                     ocol_name_lens[i] : (int)vl[i]
                     , ocol_names[i]);
            }
          printf("\n");
        }

      for (i = 0; i < n_ocols; ++i)
        {
          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;
    char cval[21];
    char dval[11];
    char fval[128];
    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),0, 0, 
                           0, sizeof(aos_t)) ||
       sqlo_define_by_pos2(sth, 5, SQLOT_STR, data[0].cval, sizeof(data[0].cval), 0, 
                           0, &data[0].cvall, sizeof(aos_t)) ||
       sqlo_define_by_pos2(sth, 6, SQLOT_STR, data[0].dval, sizeof(data[0].dval), 0, 
                           0, &data[0].dvall, sizeof(aos_t)) ||
       sqlo_define_by_pos2(sth, 7, SQLOT_STR, data[0].fval, sizeof(data[0].fval), 0, 
                           &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");