Example C Program - Teradata Preprocessor2

The following PP2 example C program creates a table with one of the columns as CLOB data type, inserts the data using a descriptor, and retrieves it back. It also contains an embedded SQL statement for setting USINGLOBDATA ON.

EXEC SQL BEGIN DECLARE SECTION;
struct {
          char SqlDAID[8];
          int SqlDABC;
          shortSqlN;
          shortSqlD;
          struct {
                  short SqlTYPE;
                  short SqlLEN;
                  char *SqlDATA;
                  char *SqlIND;
                  struct {
                          short strlen;
                          char str[30];
                         } SqlNAME;
                  int sqllenx;
                  } SqlVAR[3];
       } MYSQLDAP;

char LOGON_STRING[30];
int res=1;
int col1;
int col3;
struct {
        long long length;
        char data;
        } col2;
int f1hv;
int f3hv;
char f2hv[5];

VARCHAR SQL_STATEMENT[100];
VARCHAR errmsg[81];

long error_code;
short maxlen = 80;
EXEC SQL END DECLARE SECTION;

char REQUEST_TYPE[9];
int NUMVARS;

EXEC SQL INCLUDE SqlCA;

/*********************************************************************/
/********** ERROR CHECK                                              */
/*********************************************************************/

ERROR_CHECK ()
{
  if (SqlCA.SqlCode != 0)
  {
    PPRTEXT (&SQL_RDTRTCON, &error_code, &errmsg, &maxlen);
    errmsg.arr[errmsg.len] = '\0';
    printf ("\n");
    printf ("ERROR/WARNING DETECTED IN %s\n", REQUEST_TYPE);
    printf ("SQL CODE  : %d\n", sqlca.sqlcode);
    printf ("   ERROR CODE: %d\n", sqlca.sqlerrd[0]);
    printf ("   MSG : %s\n", errmsg.arr);
  }
}
 /*********************************************************************/
 /********** MAIN                                                     */
 /*********************************************************************/
  main (int i,char **j)
  {
/*********************************************************************/
/********** LOGON                                                    */
/*********************************************************************/
  strcpy (REQUEST_TYPE, "LOGON");

  strcpy (LOGON_STRING, j[1]);

EXEC SQL
 LOGON :LOGON_STRING;
ERROR_CHECK ();

if (SqlCA.SqlCode != 0)
   return;

printf ("\n");
printf ("LOGON SUCCESSFUL\n");
  strcpy (REQUEST_TYPE, "CREATE");
EXEC SQL
 CREATE TABLE CLOB1(F1 int,F2 CLOB,F3 int);
ERROR_CHECK ();

if (SqlCA.SqlCode == 0)
 printf ("CREATE SUCCESSFUL\n");

  strcpy (REQUEST_TYPE, "SET");
EXEC SQL
   SET USINGLOBDATA ON;
ERROR_CHECK ();

if (SqlCA.SqlCode == 0)
 printf ("SET ON SUCCESSFUL\n");
/*********************************************************************/
/********** PREPARE                                                  */
/*********************************************************************/
  strcpy (REQUEST_TYPE, "INSERT");
  strcpy (SQL_STATEMENT.arr, "INSERT INTO CLOB1 VALUES(?,?,?);");
  SQL_STATEMENT.len = strlen (SQL_STATEMENT.arr);

  strcpy (REQUEST_TYPE, "PREPARE");
  EXEC SQL
   PREPARE CLOBSTMT FROM :SQL_STATEMENT;
  ERROR_CHECK ();

  if (SqlCA.SqlCode != 0)
    res=0;
  else
    printf("PREPARE SUCCESSFUL\n");

  col1 = 1;
  col2.length = 1;
  col2.data = '3';
  col3 = 500;

  printf("BLOB DATA : %c\n",col2.data);
  printf("LENGTH OF BLOB %lld\n",col2.length);
  printf("\n Size of BLOB is: %d\n", sizeof(col2.data));

  NUMVARS =3;

  MYSQLDAP.SqlDABC = (16 + (44 * NUMVARS));
  MYSQLDAP.SqlN= NUMVARS;
  MYSQLDAP.SqlD= NUMVARS;
  MYSQLDAP.SqlVAR[0].SqlTYPE = 496;
  MYSQLDAP.SqlVAR[0].SqlLEN  = 4;
  MYSQLDAP.SqlVAR[0].SqlDATA = (char *) (&col1);
  MYSQLDAP.SqlVAR[0].SqlIND  = 0;
  MYSQLDAP.SqlVAR[0].sqllenx  = 4;
  MYSQLDAP.SqlVAR[1].SqlTYPE = 417;
  MYSQLDAP.SqlVAR[1].SqlLEN  = 9;
  MYSQLDAP.SqlVAR[1].SqlDATA =  (char *) (&col2);
  MYSQLDAP.SqlVAR[1].SqlIND  = 0;
  MYSQLDAP.SqlVAR[1].sqllenx  = 9;
  MYSQLDAP.SqlVAR[2].SqlTYPE = 496;
  MYSQLDAP.SqlVAR[2].SqlLEN  = 4;
  MYSQLDAP.SqlVAR[2].SqlDATA = (char *) (&col3);
  MYSQLDAP.SqlVAR[2].SqlIND  = 0;
  MYSQLDAP.SqlVAR[2].sqllenx  = 4;

  strcpy (REQUEST_TYPE, "EXECUTE");
  EXEC SQL
   EXECUTE CLOBSTMT USING DESCRIPTOR MYSQLDAP;
  ERROR_CHECK ();

  if (SqlCA.SqlCode != 0)
    res=0;
  else
    printf ("INSERT IS SUCCESSFULL\n");

  strcpy (REQUEST_TYPE, "SELECT");
  EXEC SQL
     SELECT F1,F2,F3 INTO :f1hv,:f2hv,:f3hv FROM CLOB1;
  ERROR_CHECK ();

  printf("\n F1 value %d",f1hv);
  printf("\n F2 value %s",f2hv);
  printf("\n F3 value %d\n",f3hv);

  strcpy (REQUEST_TYPE, "DROP");
  EXEC SQL
  DROP TABLE CLOB1;
  ERROR_CHECK ();

/*********************************************************************/
/********** LOGOFF                                                   */
/*********************************************************************/
    strcpy (REQUEST_TYPE, "LOGOFF");
    EXEC SQL
     LOGOFF;
    ERROR_CHECK ();
  }