SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Simple and Compound Statements
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
5-18
Using PROTOTYPE Host Variables as Table Names
After you declare a host variable for the table name, you can specify it within an
embedded SQL statement by using the PROTOTYPE clause. For the syntax, see
PROTOTYPE Host Variables For SQL/MP and SQL/MX Objects on page 8-4.
You must initialize the value of the PROTOTYPE host variable before the execution of
the embedded SQL statement.
Example
This example selects like columns from multiple tables that are specified dynamically.
There is a separate job code table for each division within a corporation:
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR hv_tablename[161];
unsigned NUMERIC (4) hv_jobcode;
VARCHAR hv_jobdesc[19];
...
EXEC SQL END DECLARE SECTION;
...
/* Initialize prototyped host variable name for the table. */
printf("Enter the fully qualified name of the table: ");
scanf("%s", &hv_tablename);
/* Initialize host variable in WHERE clause. */
printf("Enter job code to be retrieved: ");
scanf("%hu", &hv_this_jobcode);
...
EXEC SQL
SELECT jobcode, jobdesc
INTO :hv_jobcode, hv_jobdesc
FROM :hv_tablename PROTOTYPE 'samdbcat.persnl.job'
WHERE jobcode = :hv_this_jobcode;
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 hv-tablename PIC X(160).
01 hv-jobcode PIC 9(4).
01 hv-this-jobcode PIC 9(4).
01 hv-jobdesc.
02 LEN PIC S9(4) COMP.
02 VAL PIC X(18).
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
* Initialize protyped host variable name for the table.
MOVE ALL SPACES TO hv-tablename.
DISPLAY "Enter the fully qualified name of the table: ".
ACCEPT hv-tablename.
* Initialize host variable in WHERE clause.
DISPLAY "Enter job code to be retrieved: ".
C
COBOL