Figure 14: VALUES INTO example

 
#include <string.h> /* used for strcpy() */
#include <stdio.h>
#include <stdlib.h> /* defines system() */
#include <errno.h>
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
EXEC SQL BEGIN DECLARE SECTION;
  char stmt[1500];
  long c1val;
  char c2val[21];
  long countval;
EXEC SQL END DECLARE SECTION;
/*********************************************************************/
/*                       MAIN routine                                */
/*********************************************************************/
void main(int argc, char* argv[])
{ c1val = 0;
  strcpy(c2val, "XXX");

  exec sql WHENEVER SQLERROR CONTINUE;
  exec sql DROP TABLE VALI1;
  exec sql DROP TABLE VALI2;

 exec sql WHENEVER SQLERROR GOTO ERROR;
  exec sql CREATE TABLE VALI1
    ( C1 SMALLINT, C2 CHAR(20), C3 TIMESTAMP ) ;
  exec sql CREATE TABLE VALI2 LIKE VALI1;
  exec sql INSERT INTO VALI2
           VALUES(1,'row1',CURRENT TIMESTAMP);
  exec sql INSERT INTO VALI2
           VALUES(2,'row2',CURRENT TIMESTAMP);
  exec sql INSERT INTO VALI2
           VALUES(3,'row3',CURRENT TIMESTAMP);
  exec sql COMMIT;

  /* Attempt VALUES INTO from an INSERT with 1 row   */  
  exec sql WHENEVER SQLERROR GOTO ERROR;
  EXEC SQL VALUES
    ( SELECT C1,C2 
        FROM FINAL TABLE ( 
          INSERT INTO VALI1
            VALUES( 1, 'row1', CURRENT TIMESTAMP)  )
       WHERE C1 > 0
     ) INTO :c1val, :c2val ;

  printf(" C1, C2 returned: %d  %20s \n", c1val, c2val);
exec sql COMMIT;

  /* Attempt VALUES INTO from an INSERT with subselect  */  
  countval = 0;
  EXEC SQL VALUES
    ( SELECT COUNT(*) 
        FROM FINAL TABLE (
          INSERT INTO VALI1
            SELECT * FROM VALI2 )
      WHERE C1 > 1
    ) INTO :countval ;

  printf(" count(*) returned: %d \n", countval);
  exec sql COMMIT;
  return;
ERROR:
  exec sql WHENEVER SQLERROR CONTINUE;
  exec sql ROLLBACK;
  printf("Error, bad SQLCODE received. SQLCODE = %d", SQLCODE);
  return;
}