Developing multithreaded Applications with PRO*C

Developing multithreaded Applications with PRO*C
(Note: This article is from pro*c/c++ precompiler Programmer ' s Guide release 8.1.5)
//
If your operating system does not support threads, this article is not for you. This article contains the following sections:
//
What is multithreading.
//
Runtime context in Pro*c
//
Usage patterns for runtime contexts
//
User interface for Multithreaded applications
//
Multithreading examples
//
A What is multithreading.
//
In a multithreaded application, the thread runs in the shared address space. Threads are "lightweight" child processes that execute within a process, sharing code snippets and data segments, but with their own program counters, registers, and stacks. Global variables and static variables are shared between threads, so it is often necessary to use some kind of mutex in the program to manage the thread's access to these variables, which is the synchronization device used to ensure data integrity mutexes.
//
For more discussion of mutexes, see the article on multithreading.
//
The Proc*c compiler supports the development of multithreaded Oracle Applications (on the platform of supporting threads) in the following ways:
//
Generate thread-Safe code with a command-line compilation option
//
Support multithreading with Embedded SQL statements and directives
//
Thread-safe Lib libraries and other client Lib libraries
//
Note: Perhaps your platform supports a particular thread pack, but you still need to look at Oracle's platform documentation to see if Oracle supports it.
//
Two Runtime context in Pro*c
//
To create a loosely binding between thread and database connections, PRO*C introduces a concept runtime_context, which we call the Run-time context. A Run-time context contains the following resources and information:
//
Connection to the database server
//
Cursors used on the current connection
//
Some of the options embedded, such as Mode,hold_cursor,release_cursor and Select_error
//
Rather than simply a loose combination of support threads and connections, the PRO*C compiler allows developers to create a loose combination of thread and run-time contexts, Pro*c allows a handle to be defined in a program for the Run-time context through which the runtime context can switch between threads.
//
For example, an interactive application creates a thread T1, executes a query, returns the first 10 records, and then T1 terminates. After the user has entered the necessary data, the program creates a thread T2 and passes the run-time context used by T1 to T2 so T2 can get the next 10 records on the same cursor.
//
Three Usage patterns for runtime contexts
//
The following are two possible patterns for using the run-time context in a multithreaded Pro*c program:
//
Multithreading share a single run-time context
//
Multithreading uses an independent run-time context
//
Regardless of the pattern, multiple threads cannot share the same run-time context at the same time. If two or more threads try to use the same run-time context at the same time, the following error occurs: Sql-02131:runtime iuse.
//
1. Multithreading share a single run-time context
//
Figure (i) shows an application running in a multithreaded environment, where multiple threads share a run-time context to execute SQL statements, and runtime contexts cannot be used by multiple threads at the same time, and the mutex in the diagram shows how to prevent this parallel use.
//
Figure (i)
//
2. Multithreading uses an independent run-time context
//
Figure (ii) shows a multithreaded application that uses multiple run-time contexts, in which case the program does not need to use a mutex mutex because each thread has a separate run-time context.
//
Figure (ii)
//
Four User interface for Multithreaded applications
//
The Pro*c compiler provides the following interfaces to support multithreading:
//
command line options, Threads=yes|no
//
Embedded SQL statements and directives
//
Thread-safe Common library functions
//
1. Threads options
//
Specifying the Threads=yes,pro*c compiler on the proc precompiled command line guarantees that the resulting C code is thread safe. If THREADS=YES,PRO*C is specified, each function that contains the SQL execution statement is checked, whether the statements are executed in which run-time context, and if no such specified identity is found, the compiler returns an error.
//
2. Embedded SQL statements and directives
//
The following embedded SQL statements and directives are used to support the use of multithreading and run-time contexts:
//
EXEC SQL ENABLE THREADS;
//
EXEC SQL context Allocate:context_var;
//
EXEC SQL Context Use {: Context_var/default};
//
EXEC SQL context Free:context_var;
//
In the above SQL statement, Context_var is a run-time context handle that must be defined as a Sql_context type: such as Sql_context Context_var;
//
Using default means that the next SQL statement will use the default global run-time context until another contextual use statement overwrites it.
//
EXEC SQL ENABLE THREADS
//
This executable SQL statement Initializes a process that supports multithreading. It must be the first executable SQL statement in the program.
//
EXEC SQL Context ALLOCATE
//
This executable SQL statement allocates and initializes a piece of memory to point to a new Run-time context and returns a handle variable that identifies the context, which must be declared as a sql_context type.
//
EXEC SQL Context Use
//
This prescriptive statement tells the compiler that the SQL statement that is to be executed will use the specified Run-time context, where the run-time context must have been allocated and initialized previously with the allocate.
//
EXEC SQL Context Free
//
This statement frees the memory specified by the Run-time context handle and sets it to a null value.
//
3. Questions to consider when programming
//
Although Oracle guarantees that the SQL library is thread-safe, you still have the responsibility to ensure that your pro*c code is designed to work correctly under multithreading, for example, you must consider global variables and static variables.
//
In addition, multithreading requires the following issues to be considered:
//
Define the SQLCA structure as thread-safe. A typical practice is to define a local variable with the same name at the beginning of each function.
//
The SQLDA structure is also treated like the SQLCA structure.
//
Define the host variables in the program as thread-safe. This means being careful with global and static variables in your program.
//
Avoid using the same run-time context for different threads at the same time.
//
Five Multithreading examples
//
The following example runs on Red Hat9 and Oracle9. The purpose of the program is to insert 10,000 records into a table with two threads, each with its own run-time context.

//

#include <stdio.h>
#include <unistd.h>
#include <pthread.h>/* Linux Line Threading header file * *
#include "sqlca.h"/* Oracle header File * *
#define SQLCODE Sqlca.sqlcode

static int insert_data (SQL_CONTEXT);
static int start ();
int main ()
{
pthread_t Tid1, Tid2;
/* Create two threads */
if (Pthread_create (&tid1,null, (void *) start,null)) {
printf ("Create thread failed!/n");
Exit (1);
}

if (Pthread_create (&tid2,null, (void *) start,null)) {
printf ("Create thread failed!/n");
Exit (1);
}

/* Wait for thread to exit/*
if (Pthread_join (Tid1,null)) {
printf ("Wait for thread to end failure!/n");
Exit (1);
}

if (Pthread_join (Tid2,null)) {
printf ("Wait for thread to end failure!/n");
Exit (1);
}

Exit (0);
}

int start ()
{
Sql_context context;
struct SQLCA Sqlca; /* need to define a local SQLCA * *
Char uid[] = "dev/888888";

/* The execution order of the following SQL statements cannot be changed * *

EXEC SQL ENABLE THREADS;
EXEC SQL context Allocate:context;
EXEC SQL context Use:context;
EXEC SQL Connect:uid;

if (SQLCODE < 0) {
printf ("Create database connection failed,%d:%s/n", SQLCODE,SQLCA.SQLERRM.SQLERRMC);
Retur-1;
}
Insert_data (context);

EXEC SQL COMMIT WORK release;

if (SQLCODE < 0) {
printf ("Disconnect database connection failed!%d:%s/n", SQLCODE,SQLCA.SQLERRM.SQLERRMC);
Retur-1;
}

EXEC SQL context Free:context;

Retur0;
}

static int Insert_data (context)
Sql_context context;
{
struct SQLCA Sqlca; /* need to define a local SQLCA * *
Char name[11];
int age;
int i;

strcpy (name, "Test");
Age = 20;
EXEC SQL context Use:context; /* Specifies the context in which the SQL statement is executed * *
For (i=0 i<10000; i++) {
EXEC SQL INSERT into table1 VALUES (: Name,: Age);
if (SQLCODE < 0) {
printf ("Insert record failed!%d:%s/n", SQLCODE, SQLCA.SQLERRM.SQLERRMC);
Retur-1;
}
}
Retur0;
}