以下示例程序 thread_safe 显示如何跨线程使用游标。此程序的示例输出遵循此源
列表。
源列表
主线程开始名为 con1 的临界，
并在表 t 上声明一个游标。
然后打开游标并使 con1
连接休眠。主线程然后启动六个线程（threads_all() 函数的六个实例），调用 pthread_join()
DCE 等待线程完成它们的工作。
每个线程使用连接 con1，打开的游标来执行获取操作。在获取操作之后，程序使连
接休眠。线程以顺序方式使用连接 con1，因为一次只能有一个线程可以使用函数，每个线
程从 t 表读取下一个记录。
/* **************************************************************
* Program Name: thread_safe()
*
* purpose : If a server connection is initiated with the WITH
* CONCURRENT TRANSACTION clause, an ongoing transaction
* can be shared across threads that subsequently
* connect to that server.
* In addition, if an open cursor is associated with such
* connection, the cursor will remain open when the
* connection
* is made dormant. Therefore, multiple threads can share a
* cursor.
*
* Methods : - Create database db_con221 and table t1.
* - Insert 6 rows into table t1, i.e. values 1 through 6.
* - Connect to db_con221 as con1 with CONCURRENT
* TRANSACTION.
* The CONCURRENT TRANSACTION option is required
since
* all
* threads use the cursor throughout the same
* connection.
* - Declare c1 cursor for "select a from t1 order by a".
* - Open the cursor.
* - Start 6 threads. Use DCE pthread_join() to determine if
GBase 8s ESQL/C 编程指南
南大通用数据技术股份有限公司
- 400 -
* all threads complete & all threads do same thing as
* follows.
* For thread_1, thread_2, ..., thread_6:
* o SET CONNECTION con1
* o FETCH a record and display it.
* o SET CONNECTION con1 DORMANT
* - Disconnect all connections.
**************************************************************
*/
#include
#include
/* global definitions */
#define num_threads 6
/* Function definitions */
static void thread_all();
static long dr_dbs();
static int checksql(char *str, long expected_err, char *con_name);
static void dce_err();
/* Host variables declaration */
EXEC SQL BEGIN DECLARE SECTION;
char con1[] = "con1";
EXEC SQL END DECLARE SECTION;
/* ****************************************************
* Main Thread
******************************************************/
main()
{
/* create database */
EXEC SQL create database db_con221 with log;
if (! checksql("create database", 0, EMPTYSTR))
{
printf("MAIN:: create database returned status {%d}\n", SQLCODE);
exit(1);
}
EXEC SQL create table t1( sales int);
if (! checksql( "create_table", 0, EMPTYSTR))
{
dr_dbs("db_con221");
printf("MAIN:: create table returned status {%d}\n", SQLCODE);
exit(1);
}
GBase 8s ESQL/C 编程指南
南大通用数据技术股份有限公司
- 401 -
if ( populate_tab() != FUNCSUCC)
{
dr_dbs("db_con221");
printf("MAIN:: returned status {%d}\n", SQLCODE);
exit(1);
}
EXEC SQL close database;
checksql("[main] ", 0, EMPTYSTR);
/* Establish connection 'con1' */
EXEC SQL connect to 'db_con221' as 'con1' WITH CONCURRENT
TRANSACTION;
if (! checksql("MAIN:: ", 0, EMPTYSTR))
{
dr_dbs("db_con221");
exit(1);
}
/* Declare cursor c1 associated with the connection con1 */
EXEC SQL prepare tabid from "select sales from t1 order by sales";
checksql("MAIN:: ", 0, EMPTYSTR);
EXEC SQL declare c1 cursor for tabid;
checksql("MAIN:: ", 0, EMPTYSTR);
/* Open cursor c1 and make the connection dormant */
EXEC SQL open c1;
checksql("MAIN:: ", 0, EMPTYSTR);
EXEC SQL set connection :con1 dormant;
checksql("MAIN:: ", 0, EMPTYSTR);
/* Start threads */
start_threads();
/* Close cursor and drop database */
EXEC SQL set connection :con1;
checksql("MAIN:: set connection", 0, EMPTYSTR);
EXEC SQL close c1;
checksql("MAIN:: ", 0, EMPTYSTR);
EXEC SQL free c1;
checksql("MAIN:: ", 0, EMPTYSTR);
EXEC SQL disconnect all;
checksql("MAIN:: disconnect all", 0, EMPTYSTR);
dr_dbs("db_con221");
} /* end of Main Thread */
/**********************************************************************
GBase 8s ESQL/C 编程指南
南大通用数据技术股份有限公司
- 402 -
* Function: thread_all()
* Purpose : Uses connection con1 and fetches a row from table t1 using *
cursor c1.
* Returns : Nothing
**********************************************************************/
static void thread_all(thread_num)
int *thread_num;
{
EXEC SQL BEGIN DECLARE SECTION;
int val;
EXEC SQL END DECLARE SECTION;
/* Wait for the connection to become available */
do {
EXEC SQL set connection :con1;
} while (SQLCODE == -1802);
checksql("thread_all: set connection", 0, con1);
/* Fetch a row */
EXEC SQL fetch c1 into :val;
checksql("thread_all: fetch c1 into :val", 0, con1);
/* Free connection con1 */
EXEC SQL set connection :con1 dormant;
checksql("thread_all: set connection con1 dormant", 0, EMPTYSTR);
printf("Thread id %d fetched value %d from t1\n", *thread_num, val);
} /* thread_all() */
/**********************************************************************
* Function: start_threads()
* purpose : Create num_threads and passes a thread id number to each
* thread
**********************************************************************/
start_threads()
{
int thread_num[num_threads];
pthread_t thread_id[num_threads];
int i, ret, return_value;
for(i=0; i< num_threads; i++)
{
thread_num[i] = i;
if ((pthread_create(&thread_id[i], pthread_attr_default
(pthread_startroutine_t ) thread_all, &thread_num[i])) == -1)
{
dce_err(__FILE__, "pthread_create failed", (unsigned long)-1);
GBase 8s ESQL/C 编程指南
南大通用数据技术股份有限公司
- 403 -
dr_dbs("db_con221");
exit(1);
}
}
/* Wait for all the threads to complete their work */
for(i=0; i< num_threads; i++)
{
ret = pthread_join(thread_id[i], (pthread_addr_t *) &return_value);
if(ret == -1)
{
dce_err(__FILE__, "pthread_join", (unsigned long)-1);
dr_dbs("db_con221");
exit(1);
}
}
} /* start_threads() */
/**********************************************************************
* Function: populate_tab()
* Purpose : insert values in table t1.
* Returns : FUNCSUCC on success and FUNCFAIL when it fails.
**********************************************************************/
static int
populate_tab()
{
EXEC SQL BEGIN DECLARE SECTION;
int i;
EXEC SQL END DECLARE SECTION;
EXEC SQL begin work;
if (!checksql("begin work", 0,EMPTYSTR))
return FUNCFAIL;
for (i=1; i<=num_threads; i++)
{
EXEC SQL insert into t1 values (:i);
if(!checksql("insert", 0,EMPTYSTR))
return FUNCFAIL;
}
EXEC SQL commit work;
if (!checksql("commit work", 0,EMPTYSTR))
return FUNCFAIL;
return FUNCSUCC;
} /* populate_tab() */
/**********************************************************************
* Function: dr_dbs()
* Purpose : drops the database.
GBase 8s ESQL/C 编程指南
南大通用数据技术股份有限公司
- 404 -
**********************************************************************/
long dr_dbs(db_name)
EXEC SQL BEGIN DECLARE SECTION;
char *db_name;
EXEC SQL END DECLARE SECTION;
{
EXEC SQL connect to DEFAULT;
checksql("dr_dbs: connect", 0, "DEFAULT");
EXEC SQL drop database :db_name;
checksql("dr_dbs: drop database", 0, EMPTYSTR);
EXEC SQL disconnect all;
checksql("dr_dbs: disconnect all", 0, EMPTYSTR);
} /*dr_dbs() */
/**********************************************************************
* Function: checksql()
* Purpose : To check the SQLCODE against the expected error
* (or the expected SQLCODE) and issue a proper message.
* Returns : FUNCSUCC on success & FUNCFAIL on FAILURE.
**********************************************************************/
int checksql(str, expected_err, con_name)
char *str;
long expected_err;
char *con_name;
{
if (SQLCODE != expected_err)
{
printf( "%s %s Returned {%d}, Expected {%d}\n", str, con_name,
SQLCODE,
expected_err);
return(FUNCFAIL);
}
return (FUNCSUCC);
} /* checksql() */
/**********************************************************************
* Function: dce_err()
* purpose : prints error from dce lib routines
* return : nothing
**********************************************************************/
void dce_err(program, routine, status)
char *program, *routine;
unsigned long status;
{
int dce_err_status;
GBase 8s ESQL/C 编程指南
南大通用数据技术股份有限公司
- 405 -
char dce_err_string[dce_c_error_string_len+1];
if(status == (unsigned long)-1)
{
dce_err_status = 0;
sprintf(dce_err_string, "returned FAILURE (errno is %d)", errno);
}
else
dce_error_inq_text(status, (unsigned char *)dce_err_string,
&dce_err_status);
if(!dce_err_status)
{
fprintf(stderr, "%s: error in %s:\n ==> %s (%lu) <==\n",
program, routine, dce_err_string, status);
}
else
fprintf(stderr, "%s: error in %s: %lu\n", program, routine, status);
} /* dce_err() */
输出
每次执行示例程序时，示例输出可能会出现不同，因为它取决于线程的执行顺序。
Thread id 0 fetched value 1 from t1
Thread id 2 fetched value 2 from t1
Thread id 3 fetched value 3 from t1
Thread id 4 fetched value 4 from t1
Thread id 5 fetched value 5 from t1
Thread id 1 fetched value 6 from t1
在此输出中，线程 1 获取表中最后一条记录。