Pro*C 简单实现 Oracle 数据连接池

对于连接池的工作原理，我的一编转载有介绍：http://muscle-liu.iteye.com/admin/blogs/391916

利用Java/C++很容易实现。因项目需要，要用 C 实现。项目需求，这里我是实现连接Oracle的连接池。因为我用是 Pro*C 来写的（网上的人说这个相比 OCI 简单多），所以这个连接池是与 PL/SQL 来写的。

用 Proc*C 可以创建连接名来保存 Oracle connection(具体的 Proc*C 语法这里不介绍):

EXEC SQL CREATE DATABASE LINK link_name
	CONNECT uid IDENTIFIED BY pwd USING dbname;

对于如上述的 link_name 就是一个连接名。我的连接池里主要就是保存一定数量的连接名，每次要连接时就直接调用这些连接名。

而我的简单连接池实现原理如下：

      创建一个连接名结构体（包括名称、是否空闲），然后用一个循环链表来装载连接名。每次取一个空闲连接出来时，就按循环圈来搜索，当搜索到空闲连接就取出来，同时把它标识为忙碌。同理，当一个连接用完后，就把它标识为空闲就行了。
      当全部都是忙碌时，就设定一个时间timeout，继续循环。


缺点：这种实现方法连接数不能太大，太大循环占用时间很长。

追加一个功能: 在应用一个空闲连接时，自动判断它是否已断开，如是，就自动连上。

09/06/02 update
****************************************************************
经过几天研究，DATABASE LINK 保存的数据连接并不能很好的实现，反而通过 Runtime Contexts 可以实现保存一个连接的 session. 关于 Runtime Contexts 可以参考http://www.cs.umbc.edu/help/oracle8/server.815/a68022/threads.htm. 里边有个例子如下：

/*
 * Name:        Thread_example1.pc
 *
 * Description: This program illustrates how to use threading in
 *      conjunction with precompilers. The program creates as many
 *      sessions as there are threads. Each thread executes zero or
 *      more transactions, that are specified in a transient
 *      structure called 'records'.
 * Requirements:
 *      The program requires a table 'ACCOUNTS' to be in the schema
 *      scott/tiger. The description of ACCOUNTS is:
 *  SQL> desc accounts
 *   Name                            Null?    Type
 *  ------------------------------- -------  ------
 *  ACCOUNT                                  NUMBER(36)
 *  BALANCE                                  NUMBER(36,2)
 *
 *  For proper execution, the table should be filled with the accounts
 *      10001 to 10008.
 *
 *
 */

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlca.h>

#define      _EXC_OS_        _EXC__UNIX
#define      _CMA_OS_        _CMA__UNIX

#ifdef DCE_THREADS
  #include <pthread.h>
#else
  #include <thread.h>
#endif

/* Function prototypes */
void   err_report();
#ifdef DCE_THREADS
void   do_transaction();
#else
void   *do_transaction();
#endif
void   get_transaction();
void   logon();
void   logoff();

#define CONNINFO "scott/tiger"
#define THREADS  3 

struct parameters 
{ sql_context * ctx;
  int thread_id;
};
typedef struct parameters parameters;

struct record_log
{  char action;
   unsigned int from_account;
   unsigned int to_account;
   float  amount;
};
typedef struct record_log record_log;

record_log records[]= { { 'M', 10001, 10002, 12.50 },
                        { 'M', 10001, 10003, 25.00 },
                        { 'M', 10001, 10003, 123.00 },
                        { 'M', 10001, 10003, 125.00 },
                        { 'M', 10002, 10006, 12.23 },
                        { 'M', 10007, 10008, 225.23 },
                        { 'M', 10002, 10008, 0.70 },
                        { 'M', 10001, 10003, 11.30 },
                        { 'M', 10003, 10002, 47.50 },
                        { 'M', 10002, 10006, 125.00 },
                        { 'M', 10007, 10008, 225.00 },
                        { 'M', 10002, 10008, 0.70 },
                        { 'M', 10001, 10003, 11.00 },
                        { 'M', 10003, 10002, 47.50 },
                        { 'M', 10002, 10006, 125.00 },
                        { 'M', 10007, 10008, 225.00 },
                        { 'M', 10002, 10008, 0.70 },
                        { 'M', 10001, 10003, 11.00 },
                        { 'M', 10003, 10002, 47.50 },
                        { 'M', 10008, 10001, 1034.54}};

static unsigned int trx_nr=0;
#ifdef DCE_THREADS
pthread_mutex_t mutex;
#else
mutex_t mutex;
#endif



/*********************************************************************
 *  Main
 ********************************************************************/
main()
{
  sql_context ctx[THREADS];
#ifdef DCE_THREADS
  pthread_t thread_id[THREADS];
  pthread_addr_t status;
#else
  thread_t thread_id[THREADS];
  int status;
#endif
  parameters params[THREADS];
  int i;
  
  EXEC SQL ENABLE THREADS;

  EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);

  /* Create THREADS sessions by connecting THREADS times */
  for(i=0;i<THREADS;i++)
  {
    printf("Start Session %d....",i);
    EXEC SQL CONTEXT ALLOCATE :ctx[i];
    logon(ctx[i],CONNINFO);
  }

  /*Create mutex for transaction retrieval */
#ifdef DCE_THREADS
  if (pthread_mutex_init(&mutex,pthread_mutexattr_default))
#else
  if (mutex_init(&mutex, USYNC_THREAD, NULL))
#endif
  {
     printf("Can't initialize mutex\n");
     exit(1);
  }

  /*Spawn threads*/
  for(i=0;i<THREADS;i++)
  {
    params[i].ctx=ctx[i];
    params[i].thread_id=i;

    printf("Thread %d... ",i);
#ifdef DCE_THREADS
    if (pthread_create(&thread_id[i],pthread_attr_default,
        (pthread_startroutine_t)do_transaction,
        (pthread_addr_t) &params[i]))
#else
    if (status = thr_create
    (NULL, 0, do_transaction, &params[i], 0, &thread_id[i]))
#endif
      printf("Cant create thread %d\n",i);
    else
      printf("Created\n");
  }


  /* Logoff sessions....*/
  for(i=0;i<THREADS;i++)
  {
     /*wait for thread to end */
     printf("Thread %d ....",i);
#ifdef DCE_THREADS
     if (pthread_join(thread_id[i],&status))
       printf("Error when waiting for thread % to terminate\n", i);
     else
      printf("stopped\n");

     printf("Detach thread...");
     if (pthread_detach(&thread_id[i]))
       printf("Error detaching thread! \n");
     else
       printf("Detached!\n");
#else
     if (thr_join(thread_id[i], NULL, NULL))
       printf("Error waiting for thread to terminate\n");
#endif
     printf("Stop Session %d....",i);
     logoff(ctx[i]);
     EXEC SQL CONTEXT FREE :ctx[i];
  }


  /*Destroys mutex*/
#ifdef DCE_THREADS
  if (pthread_mutex_destroy(&mutex))
#else
  if (mutex_destroy(&mutex))
#endif
  {
    printf("Can't destroy mutex\n");
    exit(1);
  }
}

/*********************************************************************
 * Function: do_transaction
 *
 * Description:  This functions executes one transaction out of the 
 *               records array. The records array is 'managed' by
 *               the get_transaction function.
 *
 *
 ********************************************************************/
#ifdef DCE_THREADS
void do_transaction(params)
#else
void *do_transaction(params)
#endif
parameters *params;
{
  struct sqlca sqlca;
  record_log *trx;
  sql_context ctx=params->ctx;

  /* Done all transactions ? */
  while (trx_nr < (sizeof(records)/sizeof(record_log)))
  {
    get_transaction(&trx);

    EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);
    EXEC SQL CONTEXT USE :ctx;

    printf("Thread %d executing transaction\n",params->thread_id);
    switch(trx->action)
    {
      case 'M':  EXEC SQL UPDATE ACCOUNTS
                          SET    BALANCE=BALANCE+:trx->amount
                          WHERE  ACCOUNT=:trx->to_account;
                 EXEC SQL UPDATE ACCOUNTS
                          SET    BALANCE=BALANCE-:trx->amount
                          WHERE  ACCOUNT=:trx->from_account;
                 break;
       default:  break;
    }
    EXEC SQL COMMIT;
  }
}


/*****************************************************************
 * Function: err_report
 *
 * Description: This routine prints out the most recent error
 *
 ****************************************************************/
void      err_report(sqlca)
struct sqlca sqlca;
{
  if (sqlca.sqlcode < 0)
   printf("\n%.*s\n\n",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc);
  exit(1);
}

/*****************************************************************
 * Function: logon
 *
 * Description: Logs on to the database as USERNAME/PASSWORD
 *
 *****************************************************************/
void      logon(ctx,connect_info)
sql_context ctx;
char * connect_info;
{
    EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);
    EXEC SQL CONTEXT USE :ctx;
    EXEC SQL CONNECT :connect_info;
    printf("Connected!\n");

}

/******************************************************************
 * Function: logoff
 *
 * Description: This routine logs off the database
 *
 ******************************************************************/
void      logoff(ctx)
sql_context ctx;
{
    EXEC SQL WHENEVER SQLERROR DO err_report(sqlca);
    EXEC SQL CONTEXT USE :ctx;
    EXEC SQL COMMIT WORK RELEASE;
    printf("Logged off!\n");
}


/******************************************************************
 * Function: get_transaction
 *
 * Description: This routine returns the next transaction to process
 *
 ******************************************************************/
void get_transaction(trx)
record_log ** trx;
{
#ifdef DCE_THREADS
  if (pthread_mutex_lock(&mutex))
#else
  if (mutex_lock(&mutex))
#endif
    printf("Can't lock mutex\n");

  *trx=&records[trx_nr];

  trx_nr++;

#ifdef DCE_THREADS
  if (pthread_mutex_unlock(&mutex))
#else
  if (mutex_unlock(&mutex))
#endif
    printf("Can't unlock mutex\n");
}