/****************************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM, for the purpose of
** assisting you in the development of your applications.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: dbthrds.sqc
**
** SAMPLE: How to use multiple context APIs on Windows
**
** This program uses the WIN32 threads APIs for thread creation and
** management.
**
** The program maintains a pool of contexts. A generate_work
** function is executed from main(), and creates dynamic SQL
** statements that are executed by worker threads. When a
** context becomes available, a thread is created and dispatched
** to do the specified work.
**
** The work generated consists of statements to delete entries
** from either the STAFF or EMPLOYEE tables of the SAMPLE database.
**
** Compile and link with C compiler options for multi-threaded
** applications supported by your platform.
**
** Note:
** On some environments, the output may appear garbled because
** one thread process outputs information at the same time as
** another process, thereby overwriting output strings. If this
** is a concern, you can add a locking mechanism for the output
** so only one process outputs at any one time.
**
** SQL STATEMENTS USED:
** COMMIT
** CONNECT
** EXECUTE IMMEDIATE
** ROLLBACK
**
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For information on DB2 APIs, see the Administrative API Reference.
**
** For the latest information on programming, building, and running DB2
** applications, visit the DB2 Information Center:
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/
#include <stdio.h>
#include <sqlenv.h>
#include <windows.h>
#define check_expected(condition) \
{ \
if(!(condition)) \
{ \
printf("%s: %s unexpected error: ", __FILE__, __LINE__); \
printf("%s was false\n", #condition); \
fflush(stdout); \
exit(1); \
} \
}
#define CHECKERR(context, CE_STR, pStatus) \
{ \
char buf[256]; \
sprintf(buf, "Context nb.: %i\n%s", context, CE_STR); \
if(check_error(buf, &sqlca) != 0) \
{ \
*(pStatus) = sqlca.sqlcode; \
} \
}
/* prototypes */
void initialize(int argc, char *argv[]);
void generate_work();
void dispatch(struct work *work_item);
DWORD WINAPI do_work(LPVOID args);
void clean_up(struct work *work, int connect_done, int *pStatus);
struct context *ctxlist; /* list of contexts */
HANDLE *hThreads; /* thread handles stored in an array (used to join
threads). Every thread that is created gets a
handle. */
/* user supplied arguments */
int numArgs; /* acceptable number of command line arguments */
int loops; /* amount of work for the client to create */
int contexts; /* size of context pool */
int commit; /* commit the work done */
int verbose; /* display verbose output */
char database[15];
char username[15];
char password[15];
/* for management of the context pool */
int contexts_free; /* number of contexts that are currently free */
HANDLE hEvent; /* event handle */
HANDLE hMutex; /* mutex handle */
/* The function generate_work creates the following type of struct which
is passed to each worker thread. This struct tells the thread where
to connect and what work to perform. */
struct work
{
char database[15]; /* database for thread to connect to */
char username[15];
char password[15];
int context; /* context to use for connection */
char *command; /* dynamic SQL statement to execute */
};
/* the context pool consists of an array of 'struct context' types */
struct context
{
void *ctx; /* used by the sqle* APIs */
int free; /* whether the context is free or in use */
};
/* Helper method to help check for errors. */
int check_error(char eString[], struct sqlca *caPointer);
int main(int argc, char* argv[])
{
printf("%s", "\nHow to use multiple context APIs\n\n\n");
/* set initial parameters, parse command line arguments */
initialize(argc, argv);
/* begin generating work */
generate_work();
/* wait for all threads to complete before exiting */
WaitForMultipleObjects(contexts, /* maximum number of running threads */
hThreads, /* array of all thread handles */
TRUE, /* wait for ALL threads to finish */
INFINITE); /* wait forever for threads to finish */
if(verbose)
{
printf("%s", "all workers started, exiting main\n");
fflush(stdout);
}
/* free memory we allocated earlier */
free(hThreads);
free(ctxlist);
exit(0);
} /* Main */
/* Helper function that checks for errors. */
int check_error(char eString[], struct sqlca *caPointer)
{
char eBuffer[1024];
char sBuffer[1024];
char message[1024];
char messToken[1024];
short rc, Erc;
int status = 0;
if(caPointer->sqlcode != 0 && caPointer->sqlcode != 100
&& caPointer->sqlcode != -532 && caPointer->sqlcode != -438)
{
strcpy(message, "");
sprintf(messToken, "--- error report ---\n");
strcat(message, messToken);
sprintf(messToken, "ERROR occurred : %s.\nSQLCODE : %ld\n",
eString, caPointer->sqlcode);
strcat(message, messToken);
/* get sqlstate message */
rc = sqlogstt(sBuffer, 1024, 80, caPointer->sqlstate);
/* get error message API called */
Erc = sqlaintp(eBuffer, 1024, 80, caPointer);
/* return code is the length of the eBuffer string */
if(Erc > 0)
{
sprintf(messToken, "%s", eBuffer);
strcat(message, messToken);
}
if(caPointer->sqlcode < 0)
{
if(rc == 0)
{
sprintf(messToken, "\n%s", sBuffer);
strcat(message, messToken);
}
sprintf(messToken, "--- end error report ---\n");
strcat(message, messToken);
printf("\n%s\n", message);
fflush(stdout);
return 1;
}
else
{
/* errorCode is just a warning message */
if(rc == 0)
{
sprintf(messToken, "\n%s", sBuffer);
strcat(message, messToken);
}
sprintf(messToken, "--- end error report ---\n");
strcat(message, messToken);
sprintf(messToken, "WARNING - CONTINUING PROGRAM WITH WARNINGS!\n");
strcat(message, messToken);
printf("\n%s\n", message);
fflush(stdout);
}
}
return 0;
} /* Check_error */
/* The following function initializes the program state. It creates a
context pool, marks all contexts as being free, and creates a new event
and a new mutex to control critical sections for when threads become
active. */
void initialize(int argc, char *argv[])
{
int rc, i;
struct sqlca sqlca;
char buf[256];
/* default values for command line arguments (if none are specified) */
numArgs = 8;
loops = 15; /* amount of work for the client to create */
contexts = 8; /* size of context pool */
commit = 0; /* commit the work done */
verbose = 1; /* display verbose output */
strcpy(database, "sample");
strcpy(username, "");
strcpy(password, "");
/* either all parameters were specified or no parameters were specified,
otherwise the syntax is incorrect */
if (!(argc == numArgs) && !(argc == 1))
{
printf("usage: %s\n", argv[0]);
printf("\t <Database name>\n");
printf("\t <Username>\n");
printf("\t <Password>\n");
printf("\t <Loops>\n");
printf("\t <Contexts>\n");
printf("\t <Verbose> (0-1)\n");
printf("\t <Commit> (0-1)\n");
fflush(stdout);
exit(1);
}
if (argc == numArgs)
{
strcpy (database, argv[1]);
printf("Database: %s\n", database);
fflush(stdout);
strcpy (username, argv[2]);
printf("Username: %s\n", username);
fflush(stdout);
strcpy (password, argv[3]);
printf("Password: %s\n", password);
fflush(stdout);
loops = atoi(argv[4]);
printf("Loops: %i\n", loops);
fflush(stdout);
contexts = atoi(argv[5]);
printf("Contexts: %i\n", contexts);
fflush(stdout);
verbose = atoi(argv[6]);
printf("Verbose: %i\n", verbose);
fflush(stdout);
commit = atoi(argv[7]);
printf("Commit: %i\n", commit);
fflush(stdout);
}
if (argc == 1)
{
printf("Database: %s\n", database);
printf("Username: %s\n", username);
printf("Password: %s\n", password);
printf("Loops: %i\n", loops);
printf("Contexts: %i\n", contexts);
printf("Verbose: %i\n", verbose);
printf("Commit: %i\n", commit);
}
contexts_free = contexts;
if(loops < contexts)
{
printf("You need to specify more loops than contexts.\n");
exit(1);
}
hThreads = malloc(sizeof(HANDLE) * contexts);
ctxlist = malloc(sizeof(struct context) * contexts);
check_expected(ctxlist != NULL);
sqleSetTypeCtx(SQL_CTX_MULTI_MANUAL);
if(verbose)
{
sprintf(buf, "creating context pool of size %i\n", contexts);
printf("%s", buf);
fflush(stdout);
}
for(i = 0; i < contexts; i++)
{
rc = sqleBeginCtx(&ctxlist[i].ctx, SQL_CTX_CREATE_ONLY, NULL, &sqlca);
check_expected(rc == 0 && sqlca.sqlcode == 0);
ctxlist[i].free = 1;
}
/* create a mutex */
hMutex = CreateMutex(NULL, FALSE, "cond_m");
check_expected(hMutex != 0);
/* create an event */
hEvent = CreateEvent(NULL, FALSE, FALSE, "cond");
check_expected(hEvent != 0);
return;
} /* Initialize */
/* The following function creates a new work struct and populates it with
data. It randomly generates a valid SQL statement, places this statement
into the newly created struct, and dispatches the struct to the dispatch
function so a thread can be created and start doing work. */
void generate_work()
{
int i, empno;
struct work *work_item;
char buf[256];
char *delete_str1 = "DELETE FROM STAFF WHERE ID=%i";
char *delete_str2 = "DELETE FROM EMPLOYEE WHERE EMPNO='%06i'";
/* generate work to be done in each thread */
srand(GetCurrentProcessId());
for(i = 0; i < loops; i++)
{
work_item = malloc(sizeof(struct work));
strcpy(work_item->database, database);
strcpy(work_item->username, username);
strcpy(work_item->password, password);
/* the employee numbers are in the 10-350 range and are multiples of 10 */
empno =((rand() % 1000) + 1) * 10;
sprintf(buf, i % 2 ? delete_str1 : delete_str2, empno);
work_item->command = strdup(buf);
dispatch(work_item);
}
return;
} /* Generate_work */
/* The following function creates a thread to perform work specified in
the work_item struct. Since this function can be called while multiple
threads are executing, a mutex and an event are both used to control the
critical section. As soon as at least one context becomes free, this
function claims a context and creates a thread for it. */
void dispatch(struct work *work_item)
{
int rc, ctx;
DWORD dwWorkerThreadId;
char buf[256];
OpenMutex(0, TRUE, "cond_m");
check_expected(hMutex != NULL);
while(!contexts_free)
{
OpenEvent(0, TRUE, "cond");
check_expected(hEvent != 0);
}
SetEvent(hEvent);
/* there is at least one free context at this point, find one */
for(ctx = 0; ctx < contexts; ctx++)
{
if(ctxlist[ctx].free)
{
break;
}
}
ctxlist[ctx].free = 0;
contexts_free--;
ReleaseMutex(hMutex);
check_expected(hMutex != 0);
work_item->context = ctx;
if(verbose)
{
sprintf(buf,
"creating thread on context %i for SQL statement: \n\t\"%s\"\n",
ctx,
work_item->command);
printf("%s", buf);
fflush(stdout);
}
hThreads[ctx] =
CreateThread(NULL, /* default security attributes */
0, /* default stack size */
(LPTHREAD_START_ROUTINE)do_work, /* thread "main"
procedure */
(LPVOID)work_item, /* argument: work_item */
0, /* no special flags */
&dwWorkerThreadId); /* out: thread id of worker */
check_expected(hThreads[ctx] != 0);
return;
} /* Dispatch */
/* The following function is executed by newly created threads. All of the
actual work this sample does is performed within this function. This
function attaches to a context, connects to the database, executes the
statement provided to it by the generate_work function, and finally
calls the clean_up function before returning. */
DWORD WINAPI do_work(LPVOID args)
{
EXEC SQL BEGIN DECLARE SECTION;
char dbname[15];
char user[15];
char pswd[15];
char statement[256];
EXEC SQL END DECLARE SECTION;
int rc, status = 0;
char buf[256];
struct sqlca sqlca;
struct work *work_item = (struct work *)args;
strcpy(dbname, work_item->database);
strcpy(user, work_item->username);
strcpy(pswd, work_item->password);
if(verbose)
{
sprintf(buf, "%i: sqleAttachToCtx\n", work_item->context);
printf("%s", buf);
fflush(stdout);
}
rc = sqleAttachToCtx(ctxlist[work_item->context].ctx, NULL, &sqlca);
check_expected(rc == 0 && sqlca.sqlcode == 0);
if(verbose)
{
sprintf(buf, "%i: CONNECT TO %s\n", work_item->context, dbname);
printf("%s", buf);
fflush(stdout);
}
if(strlen(user) == 0)
{
EXEC SQL CONNECT TO :dbname;
}
else
{
EXEC SQL CONNECT TO :dbname USER :user USING :pswd;
}
CHECKERR(work_item->context, "CONNECT TO DATABASE", &status);
if(sqlca.sqlcode != 0)
{
clean_up(work_item, 0, &status);
}
else
{
strcpy(statement, work_item->command);
if(verbose)
{
sprintf(buf, "%i: EXECUTE \"%s\"\n", work_item->context, statement);
printf("%s", buf);
fflush(stdout);
}
EXEC SQL EXECUTE IMMEDIATE :statement;
CHECKERR(work_item->context, "EXECUTE IMMEDIATE", &status);
clean_up(work_item, 1, &status);
}
return status;
} /* Do_work */
/* The following function performs clean up. It is called after a thread
is done doing its work. It either commits the work or performs a
rollback, depending on if commit was specified as a command line option.
The connection to the database is then terminated and the thread detaches
from the context. The context is also marked as being free so that other
threads may connect to the context. */
void clean_up(struct work *work_item, int connect_done, int *pStatus)
{
int rc;
struct sqlca sqlca;
char buf[256];
if(connect_done)
{
if(commit)
{
if(verbose)
{
sprintf(buf, "%i: COMMIT\n", work_item->context);
printf("%s", buf);
fflush(stdout);
}
EXEC SQL COMMIT;
CHECKERR(work_item->context, "COMMIT", pStatus);
}
else
{
if(verbose)
{
sprintf(buf, "%i: ROLLBACK\n", work_item->context);
printf("%s", buf);
fflush(stdout);
}
EXEC SQL ROLLBACK;
CHECKERR(work_item->context, "ROLLBACK", pStatus);
}
if(verbose)
{
sprintf(buf, "%i: CONNECT RESET\n", work_item->context);
printf("%s", buf);
fflush(stdout);
}
EXEC SQL CONNECT RESET;
CHECKERR(work_item->context, "CONNECT RESET", pStatus)}
if(verbose)
{
sprintf(buf, "%i: sqleDetachFromCtx\n", work_item->context);
printf("%s", buf);
fflush(stdout);
}
rc = sqleDetachFromCtx(ctxlist[work_item->context].ctx, NULL, &sqlca);
check_expected(rc == 0 && sqlca.sqlcode == 0);
OpenMutex(0, TRUE, "cond_m");
check_expected(hMutex != NULL);
if(verbose)
{
sprintf(buf, "%i: marking context free\n", work_item->context);
printf("%s", buf);
fflush(stdout);
}
ctxlist[work_item->context].free = 1;
contexts_free++;
SetEvent(hEvent);
check_expected(hEvent != 0);
ReleaseMutex(hMutex);
check_expected(hMutex != 0);
free(work_item->command);
free(work_item);
return;
} /* Clean_up */