Empty Where Clause and Max Rows Returned

Because some tables are too large to return all records without causing significant performance degradation, the recommended practice is to write a select statement that prevents empty where clauses or one that does not select all records. Code that is generated by the wizard follows this recommendation. When you create a query database operation, you must decide whether to allow an empty where clause. If you decide that an empty where clause is appropriate for a particular query, you must modify the generated code to accommodate the empty where clause.

You must include a MaxRowsReturned business service property for all query database operations. This business service property contains the maximum number of rows to be returned to the caller from the selected resultSet variable. The business service property value is passed to the database select statement for processing. If an exception is caught while the system retrieves the business service property, the business service should stop all processing and create an E1MessageList object to pass the exception to the published business service.

Business services interpret a value of 0 (zero) in the business service property to mean return all rows. You must add code to check whether the value returned is zero, and if so, pass a CONSTANT: DBService.DB_FETCH_ALL to the database select call instead of the actual value retrieved. If zero is passed to the select call, an exception will be thrown.

This code sample shows how to check for zero:

//Call access property constants for Max Query Rows to be returned.
       //create long variable so it can be passed to bsfn call
       //initialize to 1 in the event, the business service property 
       //call fails.
       long maxReturnedRows = 0;
       //Call to return Business Service Property - if fails to 
       //retrieve value, use default and continue.
       try{
          maxReturnedRows = Long.parseLong
              (ServicePropertyAccess.getSvcPropertyValue(context,
                                          SVC_PROPERTY_QUERY_MAX_ROWS));
          //interpret property value of zero as "return all rows".  
          //Need to send constant to database call.
          if (maxReturnedRows==0){
              maxReturnedRows = DBService.DB_FETCH_ALL;
          }
       }

The MaxRowsReturned value does not eliminate the need to check for a null where clause. On a large table, the entire table is selected for processing regardless of how many records are returned to the caller. Because the select statement processes the entire table, performance can be affected.