Embedded SQl in C

/* 
 QUERY001.C

 Programmed by Richel Bilderbeek at 27-11-2006



 Problem: 

    

 Every case_id needs to have unique interr_id's.

 For case_id = '00066967' there are two interr_id = '8@@@@@@'.



 Solution:

 This program updates a single interr_id to '9@@@@@@'

/


include <stdio.h>  


exec sql include SQLCA;
pragma mapinc("INTERR","*LIBL/INTERR(*ALL)","both","D Z",,"INTERR")


int main()
{
  long count = -1; //'int' is not supported by SQL
  printf("Start of Query001.c\n");
  /* Count cases to be modified */
  exec sql 
    select count(*) into :count from INTERR 
    where case_id = '00066967' 
      and interr_id = '8@@@@@@' 
      and verwijderd <> 'J';
  
  if (count==0) 
  { 
    printf("Error: Case not found!\n"); 
    return 1; 
  }  
  else if (count>2)  
  { 
    printf("Error: More cases found!\n"); 
    return 2; 
  }  
  /* Perform the update */
  exec sql 
    update INTERR 
    set interr_id = '9@@@@@@' 
    where case_id = '00066967' 
      and interr_id = '8@@@@@@' 
      and verwijderd <> 'J';
  /* Again, count cases to be modified */
  exec sql 
    select count(*) into :count from INTERR 
    where case_id = '00066967' 
      and interr_id = '8@@@@@@' 
      and verwijderd <> 'J';
    
  if (count==0) 
  { 
    printf("Succesful update"); 
    return 0; 
  }  
  else 
  {
    printf("Error: Unsuccesful update: count equals %i",count); 
    return 3;
  }
}

• You cannot put a macro in an Embedded SQL statement
• You cannot store values from COUNT in an integer, but in a long.

External links

• Study guide: http://www.csc.liv.ac.uk/~frans/Notes/embeddedSQL.html