/*******************************************************
**
** A sample program that demonstrates the use of Static embedded SQL.
** Before compiling this program, be sure you have created a table
** called video and inserted some tuples in it. 
**
********************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION; 
 /* declare host variables    */

 char db_name[8];               
 char video_title[30];         
 short video_id;              
 char director[20];
char myday[10];
char mytime[5];
EXEC SQL END DECLARE SECTION;



/* These lines are redundant here because the default
   action is to continue. They just show the kind of
   errors that could arise and one way to control them.
*/

EXEC SQL WHENEVER SQLWARNING CONTINUE;  
EXEC SQL WHENEVER SQLERROR CONTINUE;    


/*whenever there are no more tuples to fetch from a cursor,break from the loop
and go to the label: point from where the program will continue*/
 
EXEC SQL WHENEVER NOT FOUND GOTO label  ;   
                                       

void main() {


strcpy(db_name, "c343d30");
strcpy(myday,"11/12/2002");
strcpy(mytime,"16:45");


/* C variables are preceded by a colon when they are passed to DB2 */

EXEC SQL CONNECT TO :db_name;

char aux[6];
strcpy(aux,"\0");
strcpy(aux,sqlca.sqlstate);

if (strcmp(aux,"08004")==0 || strcmp(aux,"08002")==0 || strcmp(aux,"08003")==0)
{ 
          printf("Connect failed!"  );
          exit(1);
}

/*The following 2 statements will intoduce  values in only one of the components of
 tuples of table date_and_time.The other component will be set to NULL.*/
EXEC SQL INSERT INTO c343d30.date_and_time(t) values(:mytime);
EXEC SQL INSERT INTO c343d30.date_and_time(d) values(:myday);

//The following statement  assigns values to both attributes in date_and_time table.
EXEC SQL INSERT INTO c343d30.date_and_time values(:myday, :mytime);


 
/* cursor declaration. Have to declare a cursor each time you
   want tuples back from db2

A cursor is an area reserved
in memory where the tuples retrieved from a query are stored. The tuples
selected will be stored in the cursor and then fetched to the program
one tuple at a time. 

To declare a cursor, use the DECLARE statement:
*/

EXEC SQL DECLARE c1 CURSOR FOR
        SELECT video_title
        FROM c343d30.video;

/* you have to open the cursor in order to get tuples back */

EXEC SQL OPEN c1;


for(;;){


  /* fetch tuples from the cursor. This will execute the statement
     the cursor implements and will return the results */
 
  EXEC SQL FETCH c1 into :video_title;

  printf("\n SQLSTATE is %s",sqlca.sqlstate);


  /* host variables should have ':' prefix when they are used in DB2 commands */

  printf("\n video_title = %s\n",video_title);
};

label:

/*
Note that there may be a upper limit to the number of cursor allowed 
to be opened. So after finished using a cursor, it is always good
practice to close it:*/

EXEC SQL CLOSE c1;
EXEC SQL CONNECT RESET;
 
}