Exercise caution whenever character data is returned to host variables using dynamic SQL. When the PREPARE INTO or DESCRIBE statement is executed, the 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 64 KB.
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 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 ();
}