Inline Method Restriction - Preprocessor2 for Embedded SQL

Teradata Preprocessor2 for Embedded SQL Programmer Guide

Product
Preprocessor2 for Embedded SQL
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-2446
lifecycle
previous
Product Category
Teradata Tools and Utilities
  • Data rows processed by Teradata PP2 can contain both non‑LOB and LOB data. Although BLOB and CLOB columns are treated the same as VARBYTE and VARCHAR columns, BLOB and CLOB columns must have an 8‑byte integer length indicator instead of the 2‑byte length indicator used for the VARBYTE and VARCHAR columns.
  • PP2 provides this LOB support in Descriptor mode (using SQLDA) only.
  • When USINGLOBDATA is ON, PP2 considers an extended SQLDA structure as below (a new element sqllenx is added to support LOB data):
  • struct sqlda {
      unsigned char sqldaid[8];
      int sqldabc;
      short sqln;
      short sqld;
      struct sqlvar {
        short sqltype;
        unsigned short sqllen;
        unsigned char *sqldata;
        short *sqlind;
    truct sqlname {
          short length;
          unsigned char data[30];
        } sqlname;
        int sqllenx;
      } sqlvar[1];
    };

    For all non‑LOB data types, sqllen will hold the data length, and for all LOB data, sqllenx will hold the data length. When the USINGLOBDATA is off, PP2 considers the old SQLDA structure only (without sqllenx element)

    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 ();
      }