How You Can Use the Select Statement Generically

5. Working with a Database : How You Can Access a Database with Standard SQL Statements : Select Statement : How You Can Use the Select Statement Generically

Share this page

You can use a standard select statement when you do not know the database table until runtime, provided you know the column names. Assume, for example, that you have several identically structured tables—po91, po92, and po93—that store purchase order information. You can write a single select statement to access all the purchase order tables as follows:

1. Use a variable to reference the table name.

You must notify the DBMS to interpret the variable representing the table name by preceding the variable with a leading colon (:).

2. Supply the table name at runtime.

The following statement selects data from table tblname into known fields on the form. The actual table name is substituted at runtime.

select ponum as :field1, podate as :field2,

vendorname as :field3

from :tblname;

If you also do not know the names of the columns until runtime, you can build a valid select statement using the execute immediate statement. However, you must know the name and data type of the variables that are to receive the data. If you do not have the necessary information about the target variables, create an SQLSelect object or a query object to build the retrieval statement dynamically.

For more information about dynamic query creation, see How You Can Access a Database with DataStream Objects (see How You Can Access a Database with DataStream Objects) and How You Can Use Query Objects (see How You Can Use Query Objects). For more information about the execute immediate statement, see the Language Reference Guide.