Example: Working with local SQL databases in multithreaded Pthread programs
This example demonstrates how Embedded SQL can be used within a threaded ILE C program.
Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.
/*********************************************************/
/* Testcase: SQLEXAMPLE */
/* */
/* Function: */
/* Demonstrate how Embedded SQL can be used within a */
/* threaded ILE C program. This program creates and */
/* populates an SQL Table with data. Then, threads are */
/* created which each open a cursor and read all the */
/* data from the table. A semaphore is used to show */
/* how threads execution can be controlled. */
/* */
/* To compile program: */
/* CRTSQLCI OBJ(QGPL/SQLEXAMPLE) SRCFILE(QGPL/QCSRC) */
/* COMMIT(*NONE) RDB(*NONE) OBJTYPE(*MODULE) */
/* OUTPUT(*PRINT) DBGVIEW(*SOURCE) */
/* */
/* To bind program: */
/* CRTPGM PGM(QGPL/SQLEXAMPLE) */
/* MODULE(QGPL/SQLEXAMPLE) ACTGRP(*CALLER) */
/* */
/* To invoke program: */
/* SPAWN QGPL/SQLEXAMPLE */
/* */
/*********************************************************/
#define _MULTI_THREADED
#include <pthread.h>
#include <sys/sem.h>
#include <sys/types.h>
#include <fcntl.h>
#include <stdio.h>
#include <unistd.h>
static int semid;
static struct sembuf op_try1[1] = {0,0,0};
#define MAXTHREADS 2
void *threadrtn(void *parm);
int main(int argc, char **argv)
{
int rc;
int *status;
pthread_t thids[MAXTHREADS];
EXEC SQL BEGIN DECLARE SECTION;
int i, j;
char insert[200];
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
/* create a new semaphore */
semid=semget(IPC_PRIVATE,1,S_IRUSR|S_IWUSR);
printf("\nsemaphore created\n");
rc=semctl(semid, 0, SETVAL, 1);
printf("semaphore inited\n");
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL CREATE TABLE QGPL/SQLEXMP (COL1 INT,COL2 INT);
printf("SQL table created\n");
EXEC SQL WHENEVER SQLERROR GO TO :mainerror;
for (i=1,j=100;i<10;i++,j++) {
(void) sprintf(insert, "INSERT INTO QGPL/SQLEXMP \
VALUES(%d, %d)", i, j);
EXEC SQL EXECUTE IMMEDIATE :insert;
}
printf("Table primed with data\n");
for (i=0;i<MAXTHREADS;i++) {
pthread_create(&thids[i], NULL, threadrtn, NULL);
}
printf("Threads created\n");
rc=semctl(semid, 0, SETVAL, 0);
printf("Threads turned loose\n");
for (i=0;i<MAXTHREADS;i++) {
pthread_join(thids[i], &status);
}
printf("Threads joined\n");
return;
mainerror:
printf("ERROR: sqlcode = %d sqlstate = %d\n", SQLCODE, SQLSTATE);
}
/**********************************************************/
/* This thread will do the following: */
/* - Declare a cursor for the example table */
/* - Block on a semaphore until initial thread */
/* is ready for us to run */
/* - Open the cursor */
/* - Fetch data one row at a time in a loop until */
/* End of File is reached or an error occurs */
/* - Close the cursor and return */
/**********************************************************/
void *threadrtn(void *parm)
{
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
EXEC SQL BEGIN DECLARE SECTION;
long HV1, HV2;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR GO TO :thderror;
EXEC SQL WHENEVER NOT FOUND GO TO :thdeof;
EXEC SQL DECLARE C1 CURSOR FOR SELECT * FROM QGPL/SQLEXMP;
/* block on semaphore */
semop(semid,&op_try1[0],1);
EXEC SQL OPEN C1;
printf("thid:%.8x %.8x: cursor open\n",pthread_getthreadid_np());
/* Loop until End of File (EOF) */
for (;;) {
EXEC SQL FETCH C1 INTO :HV1, :HV2;
printf("thid:%.8x %.8x: fetch done... COL1=%d COL2=%d\n",
pthread_getthreadid_np(), HV1, HV2);
}
thderror:
printf("thid:%.8x %.8x: sqlcode = %d sqlstate = %d\n",
pthread_getthreadid_np(), SQLCODE, SQLSTATE);
EXEC SQL CLOSE C1;
return;
thdeof:
printf("thid:%.8x %.8x: Done!\n",
pthread_getthreadid_np());
return;
}
Testcase output:
semaphore created
semaphore inited
SQL table created
Table primed with data
Threads created
Threads turned loose
thid:00000000 00000022: cursor open
thid:00000000 00000023: cursor open
thid:00000000 00000023: fetch done... COL1=1 COL2=100
thid:00000000 00000022: fetch done... COL1=1 COL2=100
thid:00000000 00000023: fetch done... COL1=2 COL2=101
thid:00000000 00000022: fetch done... COL1=2 COL2=101
thid:00000000 00000023: fetch done... COL1=3 COL2=102
thid:00000000 00000022: fetch done... COL1=3 COL2=102
thid:00000000 00000023: fetch done... COL1=4 COL2=103
thid:00000000 00000022: fetch done... COL1=4 COL2=103
thid:00000000 00000023: fetch done... COL1=5 COL2=104
thid:00000000 00000022: fetch done... COL1=5 COL2=104
thid:00000000 00000023: fetch done... COL1=6 COL2=105
thid:00000000 00000022: fetch done... COL1=6 COL2=105
thid:00000000 00000023: fetch done... COL1=7 COL2=106
thid:00000000 00000022: fetch done... COL1=7 COL2=106
thid:00000000 00000023: fetch done... COL1=8 COL2=107
thid:00000000 00000022: fetch done... COL1=8 COL2=107
thid:00000000 00000023: fetch done... COL1=9 COL2=108
thid:00000000 00000022: fetch done... COL1=9 COL2=108
thid:00000000 00000023: Done!
thid:00000000 00000022: Done!
Threads joined