DEV Community

Peter + AI
Peter + AI

Posted on

🚀 Mastering the Uniface read Statement: Your Complete Guide to Database Operations

#uniface #database #procscript #development

As a software developer working with Uniface, understanding the read statement is crucial for effective database operations. This comprehensive guide explores everything you need to know about fetching occurrences from the hitlist in Uniface 10.4. 📊

Note: AI assistance was used in creating this article, based on the official Uniface documentation 10.4.

🔍 What is the read Statement?

The read statement is a powerful Uniface command that fetches occurrences from the hitlist. It should only be used within the read trigger and provides extensive options for controlling how data is retrieved from your database.

Basic Syntax 

read{/lock} %/
{using Index | options "{index= Index} { ; maxhits= n | ALL } { ; cache= n | ALL } { ; step= n } {; offset = n} "} %/
{u_where (SelectionCriteria) | where DMLStatement | u_condition (Condition)} %/
{order by " Field1 {desc}{, FieldN {desc}}"}
Enter fullscreen mode Exit fullscreen mode

⚡ Key Features and Qualifiers

🔒 Lock Qualifier

The /lock qualifier enables paranoid locking, locking the occurrence when it's read (if your DBMS supports it):

read/lock order by "CUSTOMER_ID"
Enter fullscreen mode Exit fullscreen mode

🎯 Performance Options

The options clause provides several performance-related parameters:

  • maxhits: Maximum number of hits (up to 32767 or ALL)
  • cache: Cache size in bytes for occurrences
  • step: Step size of the query (default: 10)
  • offset: Number of records to skip before reading starts 
read options "maxhits=100;cache=1024;step=20" order by "INVOICE_DATE"
Enter fullscreen mode Exit fullscreen mode

🔍 Selection Criteria: Three Powerful Options

1. u_where - Database Independent

Perfect for DBMS-independent selection criteria:

trigger read
 read u_where (SALARY < 25000) order by "EMPLOYEE_NAME"
end
Enter fullscreen mode Exit fullscreen mode

2. where - Database Specific

For advanced SQL-based filtering:

trigger read
 read where "INVAMOUNT > 100" order by "INVOICE_DATE desc"
end
Enter fullscreen mode Exit fullscreen mode

3. u_condition - Profile-Based

Uses DBMS-independent profiles for complex conditions.

📊 Sorting with order by

The order by clause is essential for data organization:

; Simple ascending sort
read order by "INVOICE_MONTH"

; Descending sort
read order by "INVOICE_MONTH desc"

; Multiple fields
read order by "CUSTOMER_ID, INVOICE_DATE desc"
Enter fullscreen mode Exit fullscreen mode

🔄 Dynamic Sorting

You can even create dynamic sort profiles:

operation exec
if ($1="month")
 SORT_PROFILE.DUMMY = "INVOICE_MONTH"
else
 SORT_PROFILE.DUMMY = "INVOICE_AMOUNT"
endif
end

; trigger: Read
read order by SORT_PROFILE.DUMMY
Enter fullscreen mode Exit fullscreen mode

📄 Database Paging with offset

For modern web applications, the offset option enables efficient data paging:

; Fetch 10 records starting from the 101st record
read options "maxhits=10;offset=100" order by "BIRTHDATE"

; Dynamic paging example
trigger read
vOffset = PAGESIZE * $vPageNum$
read options "maxhits=%%PAGESIZE%%;offset=%%vOffset%%%" order by "LASTNAME"
end
Enter fullscreen mode Exit fullscreen mode

⚠️ Important Considerations

🔄 Read Trigger Behavior

  • First activation: Executes the full read statement and builds hitlist
  • Subsequent activations: Fetch from existing hitlist
  • Parameters like u_where only apply to the first read

⚡ Performance Tips

  • Use maxhits to limit result sets
  • Consider cache settings for frequently accessed data
  • The offset option may not be supported by all database connectors
  • Order by works only on fixed-length fields

🚨 Error Handling

Always check the return status after a read operation:

trigger read
 read u_where (STATUS = "ACTIVE")
 if ($status == 0)
   ; Success - initialize non-DB fields
   POSTAL_ADDRESS.ENT = $concat(STREET.ENT, ", ", CITY.ENT)
 else
   ; Handle errors based on $procerror
   ; -2: No records found
   ; -8: End of hitlist
   ; -11: Record locked (when using /lock)
 endif
end
Enter fullscreen mode Exit fullscreen mode

🎯 Best Practices

  1. Always use line continuation markers (%/) for long read statements
  2. Test return status before processing data
  3. Use unqualified field names in order by clauses
  4. Consider database-specific limitations when using where clauses
  5. Be cautious with string substitution in where clauses due to %% conflicts

🎉 Conclusion

The Uniface read statement is a versatile and powerful tool for database operations. By understanding its various qualifiers, options, and best practices, you can build efficient and robust database applications. Whether you're implementing simple data retrieval or complex paging scenarios, mastering the read statement is essential for any Uniface developer! 🚀

Happy coding! 💻✨

Top comments (0)