 |
» |
|
|
|
A dynamic parameter has the following characteristics: It is an input value to the database or an input or output parameter to
or from a procedure. It is specified as a question mark within a string in a prepared
statement in your application. Its datatype is determined based on its use in the prepared statement. You assign its value at run time via a host variable or a data
buffer array. It is replaced by its assigned value when the OPEN or EXECUTE statement
executes.
For example, the following statement specifying two dynamic parameters could be put into a string in your program:
UPDATE PurchDB.Parts SET SalesPrice = ? WHERE PartNumber = ?
|
The string itself can be used as a parameter of the PREPARE statement, or it can be assigned to a host variable that is a parameter of the PREPARE statement, as shown in the following sections. Examples in C of Preparing a Statement with Dynamic Parameters | |
The following example uses a string as a parameter of the PREPARE statement:
EXEC SQL PREPARE CMD1 FROM 'INSERT INTO PurchDB.Parts (PartNumber,PartName)
VALUES (?,?);';
|
In the following example, a host variable is used:
In the declare section, declare a character array host variable large enough to hold the string plus one byte for a delimiting ASCII 0:
EXEC SQL BEGIN DECLARE SECTION;
.
.
.
char DynamicCmdLine[81];
.
.
.
EXEC SQL END DECLARE SECTION;
.
.
.
Assign the string to the host variable:
strcpy(DynamicCmdLine,"INSERT INTO PurchDB.Parts (PartNumber, PartName)");
strcpy(tmpstr, " VALUES (?,?);");
strcat(DynamicCmdLine,tmpstr);
Prepare the statement:
EXEC SQL PREPARE CMD1 FROM :DynamicCmdLine;
|
Examples in COBOL of Preparing a Statement with Dynamic Parameters | |
The following example uses a string as a parameter of the PREPARE statement:
EXEC SQL PREPARE CMD1 FROM "INSERT INTO PurchDB.Parts (PartNumbe
- r, PartName") VALUES (?,?);"
END-EXEC.
|
In the following example, a host variable is used:
In the declare section, declare a host variable large enough to hold the string:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
.
.
.
01 DYNAMICCMD PIC X(80).
.
.
.
EXEC SQL END DECLARE SECTION END-EXEC.
.
.
.
Assign the string to the host variable:
MOVE "INSERT INTO PurchDB.Parts (PartNumber, PartName)" TO TEMP1.
MOVE " VALUES (?,?);" TO TEMP2.
STRING TEMP1 DELIMITED BY SIZE
TEMP2 DELIMITED BY SIZE
INTO DYNAMICCMD.
Prepare the statement:
EXEC SQL PREPARE CMD1 FROM :DYNAMICCMD END-EXEC.
|
Examples in FORTRAN of Preparing a Statement with Dynamic Parameters | |
The following example uses a string as a parameter of the PREPARE statement:
EXEC SQL PREPARE CMD1 FROM 'INSERT INTO PurchDB.Parts
1 (PartNumber,PartName) VALUES (?,?);'
|
In the following example, a host variable is used:
In the declare section, declare a host variable large enough to hold the string:
EXEC SQL BEGIN DECLARE SECTION
CHARACTER*80 DynamicCommand
EXEC SQL END DECLARE SECTION
.
.
.
Assign the string to the host variable:
DynamicCommand = 'INSERT INTO PurchDB.Parts (PartNumber,PartName)
1 VALUES (?,?)'
Prepare the statement:
EXEC SQL PREPARE CMD1 FROM :DynamicCommand
|
Examples in Pascal of Preparing a Statement with Dynamic Parameters | |
The following example uses a string as a parameter of the PREPARE statement:
EXEC SQL PREPARE CMD FROM 'INSERT INTO PurchDB.Parts (PartNumber,PartName)
VALUES (?,?);';
|
In the following example, a host variable is used:
In the declare section, declare a host variable large enough to hold the string:
EXEC SQL BEGIN DECLARE SECTION;
.
.
.
DynamicCmdLine:string[80];
.
.
.
EXEC SQL END DECLARE SECTION;
.
.
.
Assign the string to the host variable:
DynamicCmdLine := 'INSERT INTO PurchDB.Parts (PartNumber, PartName)'+
' VALUES (?,?);';
Prepare the statement:
EXEC SQL PREPARE CMD1 FROM :DynamicCmdLine;
|
|
