f2s_table_fetch_mode¶

Applies to: Oracle, MS SQL, MySql/MariaDB, PostgreSQL Drivers

Syntax

set_attribute f2s_table_fetch_mode of {FileNumber} to {variable}
get_attribute f2s_table_fetch_mode of {FileNumber} to {variable}

Description

This attribute allows switching between set-oriented (multiple records are fetched from the server) and record oriented (only one record is fetched from the server) fetch modes.

Consider the following function:

Function CalculateTotalPurchases Returns Number
     Number nTotalPurchase

     Send Clear to oCustomer_DD
     Send Find to oCustomer_DD GE Index.1

     While (Found)
         Move (nTotalPurchase+Customer.Purchases) to nTotalPurchase

         Send Find to oCustomer_DD GT Index.1
     Loop

     Send Clear to oCustomer_DD

     Function_Return nTotalPurchase
 End_Function

If f2s_table_fetch_mode is off (set to 0) every time the find commmand is sent, the program makes a call to the server to get a single row of data.

If, however f2s_table_fetch_mode is on (set to 1), how many rows retrieved will depend on the mode of the find call and fs2_table_max_query_rows. If the mode is EQ, LE, or GE, the program will call the server and retrieve only a single row of the table. For example:

Send find to oCustomer GE Index.1

only a single row will be returned. However, if the mode is GT or LT, then up to fs2_table_max_query_rows rows will be retrieved. The requested row will be put in the buffer and the rest cached. For example, the first time this line is run in the loop:

Send Find to oCustomer_DD GT Index.1

the call will fetch up to fs2_table_max_query_rows from the server. The requested row will be put in the buffer and the rest cached. The next time the loop runs this line, the row requested will be copied from the cache to the buffer. No call to the server will be made. The program will continue to use the cached data until it gets a request that is not cached or the cache is invalidated by some other event such as a record save or clearing the buffer. At that point, another call to the server is made and another batch of records is fetched.

This attribute can also be specified in the .INT file during migration through Flex2SQL.

This attribute affects both the DataDictionary and record buffers.

Note: Turning off f2s_table_fetch_mode can have a detrimental impact on performance. Unless there is a specific reason to turn it off, this option should be left on (1).

When to Use

Unless there is a specific reason to turn it off (see below, When not to use) the cache should be turned on (set to 1).

When not to use

Sometimes, especially in business processes, there may be a situation where many GT/LT find commands are called, but only the first row returned is used. In this case it may be useful to turn off cacheing for the process but then turn it back on once it ends. Testing the process would be needed to determine if turning off cacheing improves performance.

Caution: Turning off caching can have significant detrimental effects on performance.

Examples

Open Orderhea
Set_Attribute f2s_table_fetch_mode of Orderhea.File_Number to 1 //do not use the cache
Set_Attribute f2s_table_fetch_mode of Orderhea.File_Number to 0 //use the cache

int iAmUsingCache
get_attribute f2s_table_fetch_mode of Orderhea.FileNumber to iAmUsingCache

Related Attributes

• f2s_table_max_query_rows

ESQL Equivalents

Replaces: DF_FILE_SET_MODE