6.64 LceSetSelectOptions

Specifies the options to be used for the next SELECT or FETCH function. This function sets the characteristic of the search when an LceRequestSelect or LceSelect function is used. It is also used for record locking when an LceFetch function is used.

BOOL	LceSetSelectOptions	(int	iSession,
		char far *	strOptions )

  

Parameters

iSession	The session identifier as returned by LceGetSessionId.
strOptions	A comma-separated list of SELECT or FETCH options. The options used will revert to their default values after the next LceRequestSelect or LceFetch function.  Refer to the Notes for details.

  

strOptions

Valid SELECT options:

*BACKWARDS	Records are retrieved in reverse order.
*STARTKEY	This key is used as the starting position for the select.
*MAXRECnn	Select a maximum of nn records.
*BLOCKBYKEY[nnnn]	Block by key. Only valid on IBM i hosts.
*BLOCKBYRRN[nnnn]	Block by relative record number. Only valid on IBM i hosts.
*RECEIVEIMMED	Receive immediately.

  

Valid FETCH option:

*LOCK

Locks the record to prevent access by another application.

  

If no options are specified, or if one is missed, the default for that option is used. After the next Select request, these defaults are re-instated.

The default options for SELECT are:

	Forward record retrieval.
	No *STARTKEY option.
	No record selection limit.
	No record locking.
	No blocked I/O performed.
	The records will not be returned until they've all been read at the host.

  

The default option for FETCH:

No record locking.

  

Return Values

TRUE is returned if valid options are set.

FALSE is returned if an error occurs.

Notes

Portability Considerations

The options *BLOCKBYKEY and *BLOCKBYRRN and *LOCK are only valid on IBM i hosts. They are ignored on other platforms. For RDMLX files, *BLOCKBYKEY and *BLOCKBYRRN are always ignored. For RDML files, *BLOCKBYKEY and *BLOCKBYRRN are valid for IBM i hosts, but ignored on other platforms. The option *LOCK is only valid on IBM i hosts. It is ignored on other platforms. For RDMLX files, if the OAM cannot support locking because it is using SQL, an error will be returned from the LceFetch call.

  

*BACKWARDS

Records are retrieved in reverse order. This option indicates that rows are to be processed in reverse order to that which would normally be used. Backwards processing by sequential, full or partial key is supported.

This option must not be used with either of the *BLOCKBY options.

*STARTKEY

The key is used as the starting position for the first read operation. The first record read will be the first one which has a key equal to or greater than the key values specified. All subsequent records are then processed with no regard to the keys.

*MAXRECnn

This option is used to indicate the maximum number of records which are to be returned. It can be used with all other select options.

The nn parameter must be specified and it must be greater than zero, otherwise no records will be returned.

*BLOCKBYKEY[nnnn]

Block by key.

This is a select option that will improve application performance, depending on whether the data is in the same physical order as the primary key of the file. *BLOCKBYKEY is generally less efficient than *BLOCKBYRRN unless the IBM i physical file has been recently reorganized. (Reorganization ensures that the data is in the same order as the file's primary key.)

This option cannot be specified with the *BLOCKBYRRN option. i.e. You cannot specify both options.

Important Note: This option is only valid on IBM i hosts. It is ignored on other platforms.

Note the following about using this option:

• It may only be used when an entire file is to be processed from end to end.

• The "nnnn" portion of the option is optional. This is the blocking factor that the host based I/O module should use when reading data during the select operation. If used, it must be an integer in the range 1 to 9999.  Generally the "nnnn" option should be reserved for use in high speed file transfer operations. When the "nnnn" option is not specified, the IBM i operating system will automatically calculate an appropriate value.

• Before using this option, the associated I/O module on the host must have been recompiled some time after the system definition data area, DC@OSVEROP, was set to contain options *IOMXSERVER and *IOMBLOCKBYKEY. Failure to do this will cause your application to fail. Refer to Version Dependancy Data Area DC@OSVEROP in the LANSA for i User Guide for more details about data area DC@OSVEROP.

• This option may be used with the *RECEIVEIMMED option.

• Rows are returned in the order of the primary key of the physical file from which data is being selected. The host's relative record numbers are not available and have no defined value when used with this option.

• The following LceRequestSelect or LceRequestSelectWhere functions may only refer to a physical file. References to logical files are not supported and may cause unpredictable results and/or errors.

• The key fields must not be specified on the following LceRequestSelect or LceRequestSelectWhere functions.

• This option only persists until the next Select request. After the Select request is executed, this option is destroyed and the defaults are reinstated.

*BLOCKBYRRN[nnnn]

Block by relative record number.

*BLOCKBYRRN is generally the most efficient type of IBM i sequential database file access. Used correctly and with an appropriate blocking factor, it can improve sequential access time by a factor of 10 or more. This improvement may not be significant, however, if communications between the IBM i and your PC are slow.

This option cannot be used with the *BLOCKBYKEY option. i.e. You cannot specify both options.

Important Note: This option is only valid on IBM i hosts. It is are ignored on other platforms.

Note the following about using this option:

• It may only be used when the entire file is to be processed from end to end.

• The "nnnn" portion of the option is optional. This is the blocking factor that the IBM i based I/O module should use when reading data during the select operation. If used, it must be an integer in the range 1 to 9999.  Generally the "nnnn" option should be reserved for use in high speed file transfer operations. When the "nnnn" option is not specified, the IBM i operating system will automatically calculate an appropriate value.

• Rows are returned in the order of their relative record number (i.e. the order the rows were created or organized into) from the physical file. The IBM i's relative record numbers are not available for use with this option.

• Before using this option, the associated I/O module on the IBM i must have been recompiled some time after the system definition data area, DC@OSVEROP, was set to contain options *IOMXSERVER and *IOMBLOCKBYKEY. Failure to do this will cause application failure. Refer to Version Dependancy Data Area DC@OSVEROP in the LANSA for i User Guide for more details about data area DC@OSVEROP.

• *BLOCKBYRRN can be used with the *RECEIVEIMMED option.

• This option only persists until the next Select request. After the Select request is executed, this option is destroyed and  the  defaults are reinstated.

*RECEIVEIMMED

Receive immediately, to indicate that the next Select operation is to be performed in "immediate" mode. This option may substantially improve the time taken to select rows, particularly where a large number are involved.

Note the following about using this option:

• This option only persists until the next Select request. After the Select request is executed, this option is destroyed and the system's defaults are reinstated.

• This option can only be used with the LceRequestSelect and LceRequestSelectWhere functions - not with LceSelect.

• Once a "Select" has been issued under this option, ALL selected records must be received by using LceReceiveNextX until LceReceiveNextX indicates that there are no more rows to receive. An LceDeleteSelect can be issued to purge any remaining records.

• This option cannot be used with the LceSelect function or the LceReceiveSelect, LceGetSelect and LceGetRecordCount functions. Doing so will lead to unpredictable results and/or application failure.

• While repeated LceReceiveNextX operations are receiving ALL selected records, no other I/O operation at all (e.g. LceFetch) is permitted with the same Session Id, regardless of whether it involves the same file or not.

• This function can only be executed once a session has been opened.

The LceSetSelectOptions function is used with a number of other select processing functions. The general pattern of these functions is as follows if the *RECEIVEIMMED option is not used:

• LceSetSelectOptions sets the selection search characteristics.

• LceRequestSelect 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.

• LceRequestSelect selects records on the host.

• LceReceiveNextX reads the next records sent and stored on the PC.

Related Functions

6.15 LceFetch

6.50 LceRequestSelect

6.49 LceReceiveSelect

6.51 LceRequestSelectWhere

6.48 LceReceiveNextX

6.37 LceGetRecordCount

6.38 LceGetSelect

6.53 LceSelect

6.11 LceDeleteSelect