Example:SQL databases in multithreaded Pthread pgm

Example: Working with local SQL databases in multithreaded Pthread programs

 

/*********************************************************/
/* 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
#include
#include
#include
#include
#include

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      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     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