6.51 LceRequestSelectWhere
Identifies one or more records matching the search criteria in a file on the host. No data is transferred from the host.
|
BOOL |
LceRequestSelectWhere |
(int |
iSession, |
|
|
|
char * |
strFieldList, |
|
|
|
char * |
strFileName, |
|
|
|
char * |
strKeyList, |
|
|
|
char * |
strConditions, |
|
|
|
BOOL |
fGeneric) |
Parameters
|
iSession |
The session identifier. |
|
strFieldList |
A list of field names. All fields must have been declared using an LceUseField function. Field (i.e. column) names may be specified in upper or lower case. Field names do NOT begin with "#" (hash/pound) signs. This list may refer to any fields from the specified file. Fields may be real or virtual. |
|
strFileName |
The name of the file. This file must have been declared using an LceUseFile function |
|
strKeyList |
A list of key field names. These key fields must have been declared using the LceUseField function. The values for the keys are set using the LceSetFieldValue/ LceSetFieldValueU function. Key fields must not be specified if the *BLOCKBYKEY option was used in the preceding LceSetSelectOptions. Refer to the notes before using these key fields. |
|
strConditions |
The 'Where' clause used for the select. Refer to the notes for more information, including how to correctly use DateTime field values. |
|
fGeneric |
Refer to the notes for more information. True = apply a generic search. False = do not apply a generic search. |
Return Values
TRUE is returned if the session can select records.
FALSE is returned if an error occurs.
Notes
The Where clause, specified in strConditions, must conform to the rules defined for Expression format and definition in Specifying Conditions and Expressions in the Guide.
- If the Where expression uses only real fields, the expression is evaluated before any virtual field derivation logic is executed. This means that if the expression is not true, the record/row is skipped before incurring the virtual field derivation overhead.
- If the Where expression includes one or more virtual fields, the expression is not evaluated until all virtual logic has been executed.
- Bracketing of multiple expression components is mandatory. For example:
(SALARY *LT 30000) *AND (DEPTMENT *EQ 'ADM')
- Single simple expression components should not be bracketed. For example:
SALARY *LT 30000
- The decimal point used must match the decimal point used for the value in the host file.
- Numeric expression calculations and comparisons are carried out at (30,9) precision i.e. 21 integer digits and 9 decimal digits of precision. Calculations or comparisons requiring a precision above this level may produce unexpected results.
- Alphanumeric literal values must be enclosed in quotes and cannot exceed 256 characters.
- The entire Where expression cannot exceed 2048 characters.
- DateTime field values must be converted from local time to UTC before being used in a where clause. Use LceLocalDateTimeToServer to do the adjustment.
- Unicode field values must be converted to a formatted hex byte stream before being used in a where clause. Use LceUnicodeToHex to convert a Unicode string to a hex byte stream.
Key and Generic search considerations:
- You must know the specific key fields to the file. The key fields listed can be a subset of the actual keys to the file, but the sequence in which the keys are listed must match the sequence of the keys in the file. This is very important. The sequence of the keys must match the file. If you need the keys in a different sequence you must use a logical file.
- The fGeneric flag determines whether the keys must match exactly or whether a generic key search should apply. If a generic search is used on a file with more than one key field, then the search will apply only to the last non-blank key field specified. Any blank key field will be considered generic.
- The fGeneric flag is ignored for fields of type Date, Time, DateTime, Integer, and Float.
- You do not have to use the complete key list. You could use the first two fields in a file with three key fields. If you use a subset of the key fields, you are implicitly using a generic search on the unspecified keys, i.e. match key1 and key2 while key3 can have any value. In this case, you do not use the fGeneric search. If you did use the generic search in this situation, you will get a generic search based on key2 and key3.
The LceRequestSelectWhere function is used with a number of other select processing functions. If the *RECEIVEIMMED option is not used with LceSetSelectOptions, the general pattern of these functions is as follows :
- LceSetSelectOptions sets the selection search characteristics.
- LceRequestSelectWhere selects records on the host.
- LceReceiveSelect transfers the records from the host to the PC.
- LceGetRecordCount determines how many records were transferred.
- LceGetSelect retrieves a single record from the group of records transferred.
If the *RECEIVEIMMED option is used, then the functions will be as follows:
- LceSetSelectOptions sets the selection search characteristics.
- LceRequestSelectWhere selects records on the host.
- LceReceiveNextX reads the next record transferred.
- This function can only be executed once a session has been opened.
Tip
Performance Considerations
For better performance, whenever possible use an LceRequestSelect instead of this LceRequestSelectWhere function.
If your host is an IBM i and when LceRequestSelectWhere must be used, consider using the *BLOCKBYRRN and *RECEIVEIMMED select options in the preceding LceSetSelectOptions for best performance and lowest impact on the IBM i server. Review the LceSetSelectOptions for more details.
For example:
LceSetFieldValue(iSessionId, "DEPTMENT", "ADM");
LceRequestSelectWhere(iSessionId,
"EMPNO,SURNAME,SALARY"
"PSLMST",
"DEPTMENT",
"SALARY *GT 30000",
FALSE);
will normally be more efficient than:
LceRequestSelectWhere(iSessionId,
"EMPNO,SURNAME,SALARY"
"PSLMST",
"",
"(SALARY *GT 30000) AND (DEPTMENT = 'ADM')",
FALSE);
Constantly consider the viability of what you are doing.
For example, a select operation reading 10,000 rows to select 100, frequently used from multiple PC clients, is not a viable operation on any host system. Consider setting up a key/logical view to better support this type of operation.
The concept of what is viable varies according to
- the power and loading of the host
- the speed and loading of the communications sub-system moving data between the host and the PC.
- the frequency with which the application is used and the importance of the result it produces.
Remember that the LANSA Open functions are primarily designed to act as high performance "back ends" to programmer designed and implemented applications, not to be generic end user query tools by themselves.
Related Functions