DEV Community

Peter + AI
Peter + AI

Posted on

Leveraging Service Stored Procedures (SSPs) in Uniface 10.4

#uniface #sql #legacy #performance

In the world of enterprise application development with Uniface, performance is king. One often underutilized but powerful feature in Uniface 10.4 is the concept of Service Stored Procedures (SSPs).

If you are working with heavy data processing or complex SQL logic, moving that logic closer to the data (the DBMS) is usually the best optimization strategy. Here is a deep dive into what SSPs are, why you should use them, and the constraints you need to watch out for.

What are Service Stored Procedures?

In Uniface, a Service Stored Procedure isn't just a raw SQL script. It acts as a bridge. Technically, it uses the Uniface activate statement to address a Stored Procedure Component that you define within the Signature Editor.

Ideally, you use these when you want to execute DBMS-specific stored procedures but maintain a generic ProcScript activation method. This keeps your Uniface code relatively portable even if the underlying database logic is highly specific.

Why Use Them? (The "Why")

The primary driver is performance.

Imagine a scenario where you need to calculate a complex risk assessment value for a client based on thousands of transaction records.

  • Without SSP: You fetch all thousands of records to the client (or Uniface Server), loop through them in ProcScript, calculate the value, and display it. This creates massive network traffic.
  • With SSP: You call the stored procedure. The DBMS performs the calculation on the server. Only the single final result is sent back to Uniface.

Typical Use Cases

  • Complex Calculations: Doing heavy math where the data lives.
  • Conditional Logic: Selecting data from Table A or Table B depending on a value in Table C (a decision best made on the server side).
  • Business Rules: Checking inventory levels and triggering re-order flags in one atomic operation.

Note: If your application is simply waiting for user input, SSPs won't help much. They shine when the bottleneck is data processing, not user interaction.

How to Implement

While the developer (you) is responsible for writing the actual SQL Stored Procedure in the DBMS (be it SQL Server, Oracle, or Informix), Uniface handles the connection.

  1. Define the Component: Create a Stored Procedure Component in the Uniface Signature Editor.
  2. Set to Stateless: Since these are global operations, you must define the operations as stateless.
  3. Activate: Use ProcScript to call it. 
; Pseudo-code example of activation
variables
    string vResult
endvariables

; parameters: In_Value, Out_Result
activate "MY_STORED_PROC".CALCULATE_STOCK(12345, vResult)
Enter fullscreen mode Exit fullscreen mode

Uniface ensures the DBMS connection is established before the execution.

Important Restrictions (Read This Before Implementing!)

While powerful, SSPs in Uniface 10.4 come with a strict set of limitations. Knowing these saves you from debugging nightmares later:

  • No Hitlists: You cannot use standard Uniface hitlist functionality (e.g., $hits is not supported). Data is returned in one step.
  • No BLOBs/Images: Large object data types (Image, Raw) are not supported. Exception: Raw data parameters work on Sybase only.
  • No Multibyte Support: There is no support for multibyte language character sets within these procedures.
  • No Overflow Tables: Tables with overflow fields are off-limits.
  • No Uniface Wildcards: You cannot use Uniface-specific substitution characters or wildcards; you must rely on standard SQL syntax for the specific DBMS.

Summary

Service Stored Procedures are an excellent tool in the Uniface developer's belt for optimizing high-load queries. By offloading logic to the DBMS via the Signature Editor, you reduce network overhead. However, they require careful planning regarding data types and state management.

Happy Coding!

Top comments (0)