| Home | Previous Lesson: Dynamic SQL Next Lesson: Format 2 |
If a series of SQL statements contain no SELECT statements and has no parameters, it can be executed without an explicit PREPARE. This is done with the EXECUTE IMMEDIATE statement which has the following format:
EXECUTE IMMEDIATE : String_Variable_that_contains_SQL_Statement ;
The following example creates a table called t_product_price. This table is meant to store history of product prices.
String lSqlString
lSqlString = 'CREATE TABLE "dba"."t_product_price"' + &
'("product_no" integer NOT NULL ,' + &
'"effective_date" date NOT NULL ,' + &
'"price" numeric(8,0) NOT NULL' + &
', PRIMARY KEY ("product_no","effective_date")' + &
', FOREIGN KEY "fkey1_to_product_master" ("product_no"'+ &
') REFERENCES "dba"."product_master"' + &
'ON DELETE RESTRICT )'
EXECUTE IMMEDIATE :lSqlString ;
If SQLCA.SQLCODE <> 0 Then
MessageBox( "ERROR", string( sqlca.sqlcode ) + &
SQLCA.SqlErrText )
End If
Return 0
This SQL statement doesn't take any input from the user and doesn't generate any result set - it simply create a new table.
| Home | Previous Lesson: Dynamic SQL Next Lesson: Format 2 |