read

Fetch an occurrence from the hitlist.

Example: read order by "INVOICE_MONTH"

/lock

The optional /lock switch locks the occurrence when it is read, if the DBMS supports this feature. (This is called paranoid locking.)

Using /lock eliminates the extra read action implicit in the normal lock statement and overrides optimistic locking in any DBMS.

The lock trigger is not activated when the /lock switch is used.

u_where and where

You can use either the u_where clause or the where clause within the same read statement, but not both. Not all databases can handle these clauses; in this situation Uniface itself handles the clause.

• u_where provides a database-independent way of specifying additional selection criteria. For more information, see u_where.
• where is database-specific, and the database must support some form of DML, such as SQL, QUEL, or RDO. Consult your DBMS connector documentation to check whether the where clause is supported for your DBMS. For more information, see Data Retrieval Support.

When a u_where or where clause is used on a read statement, all occurrences that match the selection criteria are read. If the entity is an Up entity, the u_where or where clause is considered only if the foreign key is complete and not NULL. These clauses can be used to further restrict the number of occurrences read for the up entity.

These clauses are used in addition to those specified by the user in the retrieve profile entered before the ^RETRIEVE function is activated. For example, the following ProcScript in the read trigger only allows retrieves occurrences where the value of the SALARY field is less than 25000:

trigger read
  read u_where (SALARY < 25000)
end; read

where

The where clause has a DBMS-specific format; consult your DBMS documentation for the correct syntax.

The where clause is inserted into the DML Select statement, which is passed to the connector in Uniface's internal format. This may be double the size of the original DML statement. The data is limited to 32 kilobytes.

Caution: Uniface passes the DML clause as is to the DBMS connector, but it will attempt to do string substitution first. The Uniface %% symbols for the substitution may conflict with the SQL-syntax of the remaining expression.

To prevent such conflicts, prepare the selection profile clause carefully. For example, you want to select records in which FLD1 contains the string abc, using SQL wildcards. You could prepare the profile in several ways.

• Include SQL wildcards (% symbols) in the profile and use Uniface %% characters in the where clause:

  $Profile$ = "%abc%" 
   retrieve

  trigger read
     read where "FLD1 = %%($Profile$)"
   end

• Alternatively, use $concat to build up the clause with the correct syntax. The profile clause does not have to handle the %-symbols, as there is no conflict:

  $Profile$ = "abc"  
    retrieve

  Instead, SQL wildcards can be handled by the next line:

  trigger read
     read where $concat("F1 like '%", $Profile$, "%'")
   end

The where clause is not parsed by Uniface because it is DBMS-specific, so it is wise to check the return value of the read statement and, if necessary, the value of $dberror.

Uniface enables you to define subtype entities which reference a common database table, or redirect entities to database tables in the [ENTITIES] section of the assignment file. However, subtypes and entity redirection are not understood by DBMSs.

Caution: When specifying the entity in a where clause, use the name of the database table, not the entity.

offset

Important: The offset option is not supported by all database connectors, so you should consult the connector documentation for your target database before using it. For more information, see Data Retrieval Support.

The offset option makes use of database-specific functionality to specify where to start reading the data. It must be used with the maxhits option and order by clause, making it possible to implement data paging functionality. For example, to start with the 11th record, use offset=10. The following statement fetches 10 records from a dataset that has been order by the BIRTHDATE field , starting at the 101st record:

read options "maxhits=10;offset=100" order by "BIRTHDATE"

The offset option is especially useful in web applications, which cannot use stepped hitlists. (In non-web applications, using offset has no effect on the stepped hitlist mechanism.)

Typically, the value of offset is a variable that holds a calculated number, making it possible to reset the value to retrieve the previous or next set of records (where the set is determined by maxhits). For example, in the following code the offset is calculated using the value of the PAGESIZE field and the requested page number:

trigger read
...
vOffset= PAGESIZE * $vPageNum$
read options "maxhits=%%PAGESIZE%%%·;offset=%%vOffset%%%" order by "LASTNAME"
...
end

During compilation, if the offset= string is detected and there is no order by clause, a warning is issued:

error:   1000 - Syntax error ("order by" missing, or
                            incomplete or wrong options for offset)

At runtime, the read command fails with error -38 if any of the following is conditions apply:

• offset specifies a negative number
• maxhits is not specified, is 0, or negative.

  An order by clause is not specified.

Because offset relies on native database paging features, it can have a performance advantage over stepped hitlists. However not all databases have such a feature, so using offset means that your application is no longer database-independent.

For more information, see Example: Using Database Data Paging .

order by

The order by clause specifies a comma-separated list of fields as a string. It determines the ordering sequence of the selected records and is valid for all DBMSs. Each field name provided must be unqualified; for example, you can use the unqualified field name INVAMOUNT, but not the qualified field name INVAMOUNT.INVOICES.

The default ordering is ascending, but you can set it to descending using the desc keyword. For example:

; trigger: Read
read order by "INVOICE_MONTH desc"

However, order by only works on fixed-length fields. Also, you cannot use a field that has been defined as part of an entity but is not stored in the database to sort records in an order by clause. If you require records sorted on such a field, use the sort ProcScript statement.

Caution: The compiler does not flag invalid fields as an error if you attempt it, nor does Uniface check for the existence of the field names in an order by clause. Using an unknown field name can cause unpredictable results in sorting.

If you use an order by clause in combination with a DBMS that does not support ordering, Uniface retrieves and sorts all matching records. This can have a noticeable impact on performance. Additionally, when the component ends, the retrieved data must be dropped from memory. Consequently, the order by clause can affect performance not only for retrieval, but also when exiting the component.

Not all DBMSs support descending sorts, so check the appropriate connector documentation to see if your DBMS supports this feature.