How You Can Specify the Query
When creating an SQLSelect object, you must first specify the query, which involves:
• Creating the SQLSelect object
• Providing an SQL query
• Providing specifications for at least one target field or variable for each column selected
Except for building the where clause, you might set up the query in an initialize block.
The following code from the initialize block declares the necessary variables and assigns the query text to the selectstring variable:
initialize () =
declare
ss = SQLSelect;
selectstring = varchar(200) not null;
wclause = varchar(60) not null;
...
enddeclare
begin
selectstring = 'select acctno, cphone, cname,
caddr, ccity,'
+ ' cstate, czip, cdistrict, cstatus,
cacctbal' +
' from customer';
Because the ss SQLSelect object is declared without specifying default null, it is created at the same time that it is declared. If your application may never need the SQLSelect object, declare it with default null and later, when the SQLSelect object is required, explicitly create it using the Create method. For examples of using the Create method, see
Creating Dynamic Frames (see
Creating Dynamic Frames).
In addition to declaring necessary variables and providing the query text, specifying the query requires assigning the value in each database column to an SQLSelect target.
Each column's value must be assigned to the SQLSelect's Columns attribute, which is an array of the QueryCol system class. The Columns attribute must contain at least one row for each column to be retrieved from the database.
The syntax for assigning a column value to an SQLSelect target is:
sqlselect_name.Columns[n].Targets[m].Expression = field_name | var_name | value
When you assign each column participating in the query to a row in the Columns attribute, you assign it to at least one target in the Targets array (which is an attribute of the Columns array attribute). Specifying more than one target for a column lets you assign it to more than one field or variable in the frame.
For example, you can assign a column to different targets if you want to:
• Put a column's value into both a simple field and a table field
• Store a column's original value (for comparison to changes made later by the user)
Because the Targets attribute is an array of type QueryParm, it contains the following attributes, two of which are required for using an SQLSelect object. The following target attributes are required for SQLSelect objects:
Expression
Contains the name of the target field or variable, such as “field1” or “tbl[i].col1”
IsDBHandleField
Set to TRUE if the column's value is to be retrieved as a DBHandle and the target expression evaluates to a bitmap object or string object to be loaded using this DBHandle
IsSelectTarget
Set to TRUE if the column is used as a target for a select statement; defaults to FALSE
Because the IsDBHandleField attribute defaults to FALSE, specifying it is required only for columns whose values are retrieved as a DBHandle. Setting the IsSelectTarget attribute is required even though the select clause includes every column for selection.
The index of the Columns array is incremented for each column selected from the database. The index of the Targets array is incremented only if the column is to be sent to more than one target field or variable. Each column must be sent to at least one target.
Continuing our example, the following code provides the name of each target field to the Expression attribute and sets the IsSelectTarget attribute to TRUE for each column:
/* Fill in the Expression and IsSelectTarget for
** every column to be retrieved. */
ss.Columns[1].Targets[1].Expression =
'customer.acctno';
ss.Columns[2].Targets[1].Expression =
'customer.cphone';
ss.Columns[3].Targets[1].Expression =
'customer.cname';
ss.Columns[4].Targets[1].Expression =
'customer.caddr';
...
for i = 1 to ss.Columns.LastRow do
ss.Columns[i].Targets[1].IsSelectTarget = TRUE;
endfor;
end;
The where clause can be specified to select a subset of rows from the table. The following code illustrates getting either all accounts or only accounts that are closed (cstatus = 0):
if (get_all_accounts = TRUE) then
/*
** Fetch all customers
*/
wclause = ' order by cname';
else
/*
** Fetch customers whose accounts are closed.
*/
wclause = ' where cstatus = 0 order by name';
endif;
The text of an SQLSelect object's query is stored in its Query attribute. The data type of the Query attribute is StringObject. You can load the Query attribute directly with the text of a select statement or create the text from variables that contain pieces of the select statement.
The following statement sets the value of the Query attribute from its two varchar variables, selectstring and wclause:
ss.Query.Value = selectstring + wclause;
Although the complete select statement is assigned to the Query's Value attribute, the actual data retrieval occurs only when you invoke DataStream's Open method.