15.10 - A Caution When Using Dynamic SQL - Preprocessor2 for Embedded SQL

Teradata Preprocessor2 for Embedded SQL Programmer Guide

prodname
Preprocessor2 for Embedded SQL
vrm_release
15.10
category
Programming Reference
featnum
B035-2446-035K

Exercise caution whenever character data is returned to host variables using dynamic SQL. When the PREPARE INTO or DESCRIBE statement is executed, the Teradata Database returns a data type code of 452 or 453 (fixed character) in the SQLTYPE field of the SQLDA or 448 or 449 (varying character), according to the SQL data type (CHAR or VARCHAR, respectively). When dynamic SQL statement text is stored in a varchar host variable, the statement text cannot be longer than 64k.

If this SQLTYPE is not changed before the data is returned, no terminating null is appended. This means that the C string conventions are not desired; the appropriate host variable type is probably varying character.

If the data is to be handled as a C string, the terminating null character is required. In this case, the program must alter the SQLTYPE field to 460 or 461 before the data is retrieved, and the appropriate host data type is character string, with a length of one greater than the length of the SQL data (found in the SQLLEN field of the SQLDA).

 

Sequence

Description

SELECT

SELECT is a data returning statement (potentially). Therefore, the statement is executed using a dynamic cursor.

The PREPARE readies the statement in the SQL string SQL_STATEMENT for execution.

The DESCRIBE returns information about the data coming back into the user-defined SQLDA (SQLDA).

Notice that the program initializes the SqlDAID, SqlDABC and SqlN fields prior to executing the DESCRIBE request.

SqlDABC is set by PP2 runtime, while SqlN indicates to the DESCRIBE the number of repeating element groups available to contain field information.

The OPEN statement specifies the host variable whose value is to replace the null found in the SELECT statement. OPEN executes the select, returning an indication of success or failure to the application, as well as the number of rows that are returned if the statement is successfully executed.

The FETCH loop returns the data into the specified host variables (through the SQLDA structure) until no more data is found (+100 in the SQLCODE) or an error occurs (negative SQLCODE).

The CLOSE terminates the cursor and performs the cleanup related to the select statement.

INSERT

INSERT does not return data, nor does it require input host variables.

This facility allows the statement to execute using an EXECUTE IMMEDIATE.

DELETE

DELETE does not return data, but requires an input host variable, which requires an EXECUTE statement.

The PREPARE readies the statement in the SQL string for execution.

A DESCRIBE is not required for the delete because no data is returned.

 

The EXECUTE submits the DELETE statement to the Teradata Database for processing, passing the value of the host variable indicated in the SQLDA (SQLDA) for the null.

 

The success or failure of the transaction is reflected in the SQLCODE.

 

The number of rows deleted, if any, appears in the first SQLERRD element.

EXEC SQL BEGIN DECLARE SECTION;
char  LOGON_STRING[13];
long  EMPNUM;
long  MANNUM;
long  DPTNUM;
long  JOBNUM;
char  LSTNAM[21];
char  FSTNAM[31];
char  HIRDAT[11];
char  BRTDAT[11];
float SALARY;
short EMPIND;
short MANIND;
short DPTIND;
short JOBIND;
short LSTIND;
short FSTIND;
short HIRIND;
short BRTIND;
short SALIND;
VARCHAR  SQL_STATEMENT[180];
EXEC SQL END DECLARE SECTION;
int      ERROR_CODE;
struct
      {
       short      len;
       char       arr[255];
      } ERROR_MSG;
short    MAX_LENGTH = 255;
char  REQUEST_TYPE[9];
struct {
         char    SqlDAID[8];
         int     SqlDABC;
         short   SqlN;
         short   SqlD;
         struct {
               short    SqlTYPE;
               short    SqlLEN;
               char     *SqlDATA;
               char     *SqlIND;
               struct {
                       short  strlen;
                       char   str[30];
                     } SqlNAME;
               } SqlVAR[9];
            } SQLDA;
EXEC SQL INCLUDE SQLCA;
EXEC SQL
         DECLARE EMPCUR CURSOR FOR EMPSTMT;
/*******************************************************/
/********** ERROR CHECK                                */
/*******************************************************/
ERROR_CHECK ()
{
   if (SQLCODE ! = 0)
      {
      PPRTEXT  (&SQL_RDTRTCON,
               &ERROR_CODE,
               &ERROR_MSG,
               &MAX_LENGTH);
      ERROR_MSG.arr[ERROR_MSG.len] = ’\0’;
      printf (“\n”);
      printf (“ERROR/WARNING DETECTED IN %s\n”,
              REQUEST_TYPE);
      printf (“   CODE: %d\n”, ERROR_CODE);
      printf (“   MSG : %s\n”, ERROR_MSG.arr);
      }
}
/*******************************************************/
/********** FETCH                                      */
/*******************************************************/
FETCH_EMPCUR ()
{
 strcpy (REQUEST_TYPE, “FETCH”);
EXEC SQL
      FETCH EMPCUR USING DESCRIPTOR SQLDA;
ERROR_CHECK ();
if (SQLCODE == 0)
   {
   printf (“\n”);
   printf (“EMPLOYEE NUMBER   : %ld\n”, EMPNUM);
   printf (“MANAGER NUMBER    : %ld\n”, MANNUM);
   printf (“DEPARTMENT NUMBER : %ld\n”, DPTNUM);
   printf (“JOB CODE          : %ld\n”, JOBNUM);
   printf (“LAST NAME         : %s\n”,  LSTNAM);
   printf (“FIRST NAME        : %s\n”,  FSTNAM);
   printf (“HIRE DATE         : %s\n”,  HIRDAT);
   printf (“BIRTH DATE        : %s\n”,  BRTDAT);
   printf (“SALARY            : %lf\n”, SALARY);
   }
}
/*******************************************************/
/********** LOGOFF                                     */
/*******************************************************/
LOGOFF ()
{
strcpy (REQUEST_TYPE, “LOGOFF”);
EXEC SQL
      LOGOFF;
ERROR_CHECK ();
}
/*******************************************************/
/********** MAIN                                       */
/*******************************************************/
main ()
{
printf (“EXECUTING SAMPLE...\n\n”);
/*******************************************************/
/********** LOGON                                      */
/*******************************************************/
strcpy (REQUEST_TYPE, “LOGON”);
strcpy (LOGON_STRING, “tdp/user,psw”);
EXEC SQL
      LOGON :LOGON_STRING;
ERROR_CHECK ();
if (SQLCODE  ! = 0)
      return;
/*******************************************************/
/********** PREPARE                                    */
/*******************************************************/
strcpy (REQUEST_TYPE, “PREPARE”);
strcpy (SQL_STATEMENT.arr, “SELECT EMPLOYEE_NUMBER, ”);
strcat (SQL_STATEMENT.arr, “MANAGER_EMPLOYEE_NUMBER, ”);
strcat (SQL_STATEMENT.arr, “DEPARTMENT_NUMBER, ”);
strcat (SQL_STATEMENT.arr, “JOB_CODE, ”);
strcat (SQL_STATEMENT.arr, “LAST_NAME, ”);
strcat (SQL_STATEMENT.arr, “FIRST_NAME, ”);
strcat (SQL_STATEMENT.arr, “HIRE_DATE, ”);
strcat (SQL_STATEMENT.arr, “BIRTHDATE, ”);
strcat (SQL_STATEMENT.arr, “SALARY_AMOUNT ”);
strcat (SQL_STATEMENT.arr, “FROM EMPLOYEE ”);
strcat (SQL_STATEMENT.arr, “WHERE EMPLOYEE_NUMBER = ?”);
SQL_STATEMENT.len = strlen (SQL_STATEMENT.arr);
EXEC SQL
      PREPARE EMPSTMT FROM :SQL_STATEMENT;
ERROR_CHECK ();
if (SQLCODE != 0)
   {
   LOGOFF ();
   return;
   }
/*******************************************************/
/********** DESCRIBE                                   */
/*******************************************************/
strcpy (REQUEST_TYPE, “DESCRIBE”);
strncpy (SQLDA.SqlDAID, “SQLDA    ”, 8);
SQLDA.SqlDABC = 0;
SQLDA.SqlN    = 9;
EXEC SQL
      DESCRIBE EMPSTMT INTO SQLDA;
ERROR_CHECK ();
if (SQLCODE != 0)
   {
   LOGOFF ();
   return;
   }
SQLDA.SqlVAR[0].SqlDATA    = (char *) (&EMPNUM);
SQLDA.SqlVAR[0].SqlIND     = (short *) (&EMPIND);
SQLDA.SqlVAR[1].SqlDATA    = (char *) (&MANNUM);
SQLDA.SqlVAR[1].SqlIND     = (short *) (&MANIND);
SQLDA.SqlVAR[2].SqlDATA    = (char *) (&DPTNUM);
SQLDA.SqlVAR[2].SqlIND     = (short *) (&DPTIND);
SQLDA.SqlVAR[3].SqlDATA    = (char *) (&JOBNUM);
SQLDA.SqlVAR[3].SqlIND     = (short *) (&JOBIND);
SQLDA.SqlVAR[4].SqlDATA    = LSTNAM;
SQLDA.SqlVAR[4].SqlIND     = (short *) (&LSTIND);
SQLDA.SqlVAR[5].SqlDATA    = FSTNAM;
SQLDA.SqlVAR[5].SqlIND     = (short *)) (&FSTIND);
SQLDA.SqlVAR[6].SqlDATA    = HIRDAT;
SQLDA.SqlVAR[6].SqlIND     = (short *) (&HIRIND);
SQLDA.SqlVAR[7].SqlDATA    = BRTDAT;
SQLDA.SqlVAR[7].SqlIND     = (short *) (&BRTIND);
SQLDA.SqlVAR[8].SqlDATA    = (char *) (&SALARY);
SQLDA.SqlVAR[8].SqlIND     = (short *) (&SALIND);
SQLDA.SqlVAR[4].SqlTYPE    = 460;
SQLDA.SqlVAR[5].SqlTYPE    = 460;
SQLDA.SqlVAR[6].SqlTYPE    = 460;
SQLDA.SqlVAR[6].SqlLEN     = 10;
SQLDA.SqlVAR[7].SqlTYPE    = 460;
SQLDA.SqlVAR[7].SqlLEN     = 10;
SQLDA.SqlVAR[8].SqlTYPE    = 480;
SQLDA.SqlVAR[8].SqlLEN     = 4;
/*******************************************************/
/********** OPEN                                       */
/*******************************************************/
strcpy (REQUEST_TYPE, “OPEN”);
EMPNUM = 1001;
EXEC SQL
      OPEN EMPCUR USING :EMPNUM;
ERROR_CHECK ();
if (SQLCODE < 0)
   {
    LOGOFF ();
    return;
   }
while (SQLCODE == 0)
   {
    FETCH_EMPCUR ();
   }
/*******************************************************/
/********** CLOSE                                      */
/*******************************************************/
strcpy (REQUEST_TYPE, “CLOSE”);
EXEC SQL
      CLOSE EMPCUR;
ERROR_CHECK ();
/*******************************************************/
/********** EXECUTE IMMEDIATE                          */
/*******************************************************/
strcpy (REQUEST_TYPE, “EXECUTE”);
strcpy (SQL_STATEMENT.arr, “INSERT EMPLOYEE VALUES (”);
strcat (SQL_STATEMENT.arr, “2010,1003,2216,8201,’JONES’,”);
strcat (SQL_STATEMENT.arr, “’FREDDY’,’20/06/14’,”);
strcat (SQL_STATEMENT.arr, “’19/05/26’,200000)”);
SQL_STATEMENT.len = strlen (SQL_STATEMENT.arr);
EXEC SQL
      EXECUTE IMMEDIATE :SQL_STATEMENT;
ERROR_CHECK () ;
/*******************************************************/
/********** PREPARE                                    */
/*******************************************************/
strcpy (REQUEST_TYPE, “PREPARE”);
strcpy (SQL_STATEMENT.arr, “DELETE FROM EMPLOYEE ”);
strcat (SQL_STATEMENT.arr, “WHERE EMPLOYEE_NUMBER = ?”);
SQL_STATEMENT.len = strlen (SQL_STATEMENT.arr);
EXEC SQL
      PREPARE DELSTMT FROM :SQL_STATEMENT;
ERROR_CHECK ();
if (SQLCODE != 0)
   {
   LOGOFF ();
   return;
   }
/*******************************************************/
/********** EXECUTE                                    */
/*******************************************************/
EMPNUM = 2010;
SQLDA.SqlDABC = 60;
SQLDA.SqlN    = 1;
SQLDA.SqlD    = 1;
SQLDA.SqlVAR[0].SqlTYPE = 496;
SQLDA.SqlVAR[0].SqlLEN  = 4;
SQLDA.SqlVAR[0].SqlDATA = (char *) (&EMPNUM);
SQLDA.SqlVAR[0].SqlIND  = 0;
strcpy (REQUEST_TYPE, “EXECUTE”);
EXEC SQL
      EXECUTE DELSTMT USING DESCRIPTOR SQLDA;
ERROR_CHECK ();
LOGOFF ();
}