How You Can Use SQLSelect Objects
The following text uses examples that might be included in a frame that retrieves information from a customer table.
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.
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.
How You Can Run the SQLSelect Query
To execute the query after specifying it, invoke the Open method. If you open the SQLSelect object in QY_ARRAY mode, each column's value goes directly to the Expression attribute of the SQLSelect object's target array. If you mapped the Expression attribute for each column to a specific field on the form, each column's value is displayed on the form.
If, however, you open the SQLSelect object in any mode but array mode, the following additional methods are required to display the data:
NextRow
Retrieves the data row by row from the database into internal buffers
Load
Moves data from the internal buffers into the target fields on the form
Opening an SQLSelect object requires setting several parameters in addition to specifying the query mode. The Open method has the following parameters:
QueryMode
Data Type: integer
(Required.) Specifies one of the following modes:
• QY_ARRAY
• QY_CACHE
• QY_CURSOR
• QY_DIRECT
Default: None
CheckCols
Data Type: integer
(Optional.) Checks the number of columns specified in the Query text with the number of columns in the Columns array. When FALSE, populates columns in the Columns array if they have not been explicitly set.
Default: FALSE
IsRepeated
Data Type: integer
(Optional.) Specifies whether to make a repeat query. Set to TRUE to make a repeat query.
Default: FALSE
MaxRows
Data Type: integer
(Optional.) Specifies a maximum number of array rows to populate (if greater than 0). Exceeding MaxRows causes an error (applies only to QY_ARRAY mode).
Default: 0
Scope
Data Type: Scope
(Required.) Specifies the scope for evaluation of expressions in the Columns array Targets attribute.
Default: Value of the current Scope attribute
It is always necessary to set the scope for an SQLSelect object or QueryObject object. Scope can be set as a parameter to the Open method or by setting the DataStream's Scope attribute directly. The following code sets the SQLSelect object's Scope attribute to the scope of the current frame:
ss.Scope = CurFrame.Scope;
if ss.Scope != CurFrame.Scope then
/* error */
endif;
Note: Because setting the scope causes compilation of expressions in the SQLSelect object, errors during compilation could prevent the scope from setting as expected. It is recommended, therefore, that you check for appropriate setting of the scope while you are developing the application and make the necessary changes to your 4GL code.
Rather than setting the scope by setting the Scope attribute, you can set it using the Open method. The following code opens the SWLSelect object in cache mode, with CheckCols set to TRUE, and specifies the current frame as the scope:
status = ss.Open
(
querymode = QY_CACHE,
checkcols = TRUE,
cope = CurFrame.Scope
);
if (status != ER_OK) then
/* There was an error. */
rollback;
/* Notify user of error. */
...
resume;
endif;
commit;
if (ss.ErrorNo != 0 and ss.IsDBError = TRUE) then
/* There was a database error */
rollback;
/* Notify user of error. */
...
resume;
endif;
The value returned by the Open method is ER_OK if no errors occur in executing the query. If no errors have occurred, the example code commits the transaction opened by executing the query.
Like any SQL statement, SQLSelect objects (and QueryObject objects) participate in a transaction. Because closing the SQLSelect object does not close the transaction, your application must explicitly commit or roll back the transaction.
Because all the data retrieval is done at once when the Open method is invoked, the code in the previous example commits the transaction immediately.
After committing the transaction, the sample code checks for errors using the following DataStream attributes:
ErrorNo
Provides the error number of the last operation, generated either by:
• The DBMS—for example, a column specified as a target does not exist in the table
• The application—for example, the 4GL code provides an illegal scope or contains expressions that do not compile
IsDBError
Set to TRUE if the error is a DBMS error.
If the SQLSelect has been opened successfully, this frame uses the SQLSelect's MaxRow attribute to determine the next operation. The MaxRow attribute contains the number of rows retrieved. If the value of the MaxRow attribute is not zero, you can invoke the NextRow and Load methods to retrieve data.
The following code performs these operations:
if (MaxRow > 0) then
status = ss.NextRow();
status = ss.Load();
endif;
Because the ss SQLSelect object was opened in QY_CACHE mode, data was retrieved from the database table into the cache. The data is not directly displayed on the form. Therefore, the example code invokes the NextRow method to move the next row (in this case the first row) from the cache to internal buffers, and the Load method to move it from internal buffers to fields (or variables) in the frame.
How You Can Get Next and Previous Rows
Suppose that your frame has buttons labeled Next and Previous. The following code shows how you can use these buttons to fetch the next and previous rows:
/*
** Initialize the buttons.
*/
if (ss.CurRow >1) then
field(prev_btn).CurBias = FB_CHANGEABLE;
else
field(prev_btn).CurBias = FB_DIMMED;
endif;
if (ss.CurRow <ss.MaxRow) then
field(next_btn).CurBias = FB_CHANGEABLE;
else
field(next_btn).CurBias = FB_DIMMED;
endif;\
...
on click next_btn =
{
ss.NextRow();
ss.Load();
/*
** Display the data.
*/
...
/*
** See if there are more rows.
*/
if (ss.CurRow >= ss.MaxRow) then
field(next_btn).CurBias = FB_DIMMED);
endif;
field(prev_btn).CurBias = FB_CHANGEABLE;
}
on click prev_btn =
{
ss.PrevRow();
ss.Load();
/*
** Display the data.
*/
if (ss.CurRow <= 1) then
field(prev_btn).CurBias = FB_DIMMED);
endif;
field(next_btn).CurBias = FB_CHANGEABLE;
}
A third DataStream method used to get rows is the FetchRow method. This method sets the current row in the cache to the row specified in the RowIndex parameter. The value of this parameter must be an integer.
For example, the following code sets the fourth row in the cache as the next row to be loaded:
ss.FetchRow(rowindex = 4);
To position the current row on the first row of the cache, set the RowIndex parameter to “1”, for example:
ss.FetchRow(rowindex = 1);
Both the PrevRow and FetchRow methods are valid only in QY_CACHE mode.
How You Can Close the SQLSelect Object
To close an SQLSelect object, invoke DataStream's Close method. It is necessary to close the SQLSelect object before changing any of the query parameters and reexecuting it. Attempting to close an SQLSelect object that is not open, or open one that is still active, causes an error.
The following code closes the SQLSelect object:
if ss.State = QS_ACTIVE then
ss.Close();
endif;
When you close an SQLSelect object that was opened in cache mode, you clear it of all data. Therefore, do not close the query until the user has selected an operation that uses different data.
How You Can Use Parameterized Queries
To allow greater flexibility, the text of the SQLSelect's Query attribute can contain parameter substitutions. There are two types of query parameters:
Parameters evaluated before the query is sent to the DBMS server
These parameters involve straight textual substitution and can appear anywhere in the text, even inside quoted strings and substituting for SQL keywords and operators. They cannot, however, be used with repeat queries. These parameters begin with a leading ampersand (&).
The following example contains parameterized column names and a parameterized table name:
ss.Query.Value = 'select &(col1), &(col2),
&(col3) ' +'from &tablename';
The variables representing the column names and table name must be visible in the scope specified in the SetCols or Open method. When the ampersand is followed by an expression, the expression must be enclosed within parentheses as shown in the following example:
ss.Query.Value = 'select &(col1 + col2/2) ' +
'from &tablename';
Parameters interpreted by the Ingres server
These parameters can appear only where the Ingres DBMS accepts parameters (that is, in the same places as constants). They begin with a leading colon (:).
The following example evaluates an expression and uses the result as the value for the where clause:
select number from infotbl
where number = :(var1 + var2);
The following example shows how you can set the Value attribute of an SQLSelect with a parameterized where clause:
initialize () =
declare
ss = SQLSelect;
wclause = varchar(60) not null;
...
enddeclare
begin
ss.Query.Value = 'select * from customer
&(wclause)' +' order by cname';
You can set the where clause later. The value of the where clause is assigned after the SQLSelect is opened and evaluated and before the query is sent to the DBMS server.
For a discussion of setting the Query's Value attribute, see
How You Can Specify the Query.
How You Can Use the SetCols Method to Specify the Query
Note that in addition to using parameter substitution, the preceding query differs from previous examples in that it does not specify the columns to be retrieved. Instead it uses the "select *" syntax to retrieve all columns from the customer table.
After loading the SQL text into the Query attribute, you can use the SetCols method to get relevant column information for each column and to initialize the Columns array. The SetCols method also checks the query for syntax errors.
The SetCols method gets information from the database table and assigns it to relevant attributes in the SQLSelect's Columns array. As an array of type QueryCol, the Columns attribute contains the following information:
AsName
Specifies the column name used in the select statement to see the retrieved column (for example, select name as EmpName ...); used in group by or order by clauses
ColumnName
Specifies the name of the database column; required for QueryObject objects but not SQLSelect objects
DataType
Specifies the data type of the database column; supplied or checked by the SetCols and Open methods
DataTypeLength
Specifies the length of the data type; supplied or checked by the SetCols and Open methods
DataTypeNullable
Set to TRUE if the data type is nullable; supplied or checked by the SetCols and Open methods
Targets
Specifies an array of field mappings for each column
Value
Specifies the current column value (valid only if a row has been fetched)
The attributes that contain data type information are assigned directly by invoking the SetCols or Open methods. In addition, if it has not already been filled in, the AsName attribute is also assigned a value by invoking these two methods.