🤖 This blog post was created with the help of an AI assistant and is based on the official Uniface 10.4 documentation for “Informix Stored Procedures”.
When you build Uniface applications on Informix, performance matters. One simple way to speed up basic database operations is to use stored procedures generated by the Uniface Informix connector (INF).
What is a stored procedure? 🤔
A stored procedure is one or more SQL statements that are already parsed and compiled and stored in the Informix database. Because of this, Informix can execute them immediately without parsing the SQL text again each time.
Compared to sending raw SQL from your application, stored procedures can be faster and more consistent for common operations.
How the INF connector uses stored procedures
The INF connector can create stored procedures automatically when it creates the base tables in Informix. These procedures handle the most basic I/O operations: insert, fetch, update, delete, and select.
To enable this behavior, you must set the procs connector option in the Uniface assignment file for Informix. Without this option, Uniface does not create or use these Informix stored procedures.
Stored procedures created by INF 🔧
For each table (let’s call it table_name), the connector can create several procedures with specific suffixes. Here are the most important ones:
-
table_name_E– Existence check. This procedure checks iftable_namealready has the expected procedures. -
table_name_I– Insert a row intotable_name. This one is not created for tables that contain segmented fields. -
table_name_F– Fetch all nonsegmented fields in one row oftable_nameusing a specific primary key, and optionally lock the row. -
table_name_U– Update one row intable_namebased on the primary key, or the primary key plus theU_VERSIONfield. This is also not created for tables that contain segmented fields. -
table_name_D– Delete a row intable_namebased on the primary key, or the primary key plusU_VERSION. -
table_name_S– Select nonsegmented fields for all rows oftable_name, using a select cache for better performance. -
table_name_S#– Select nonsegmented fields using a specific index number#, again using the select cache. The connector generates one stored procedure for each index that is used in such select operations.
In addition, Uniface can generate extra stored procedures to enforce referential integrity, depending on your settings and table relationships.
Segmented fields and stored procedures 📦
Informix supports segmented fields (for example, large text or byte data stored in separate locations). The INF connector has special rules for these fields.
- The connector never uses stored procedures to manipulate segmented fields.
- For tables that contain segmented fields, the connector does not create the insert (
_I) or update (_U) procedures. - The other procedures (
_F,_D,_S,_S#) are still created but they only work with nonsegmented fields.
For read, write, and update operations that involve segmented fields, the connector falls back to dynamic SQL instead of using stored procedures.
Serial fields and the serial insert option 🔁
Informix also supports serial fields (auto-increment numeric values). In Uniface, the behavior for tables with serial fields depends on the connector options.
If the serial insert option is turned on together with the procs option, the INF connector changes its behavior:
- For tables that contain serial fields, the connector does not use the insert stored procedure.
- Instead, it uses dynamic SQL to execute the write request, so that serial values are handled correctly.
Example: Simple customer table 🧑💻
Let’s look at a small example. Imagine a table called customer with a primary key customer_id, no segmented fields, and no serial fields.
With the procs option enabled, you can expect the INF connector to create procedures like:
-
customer_I– Insert a new customer row. -
customer_F– Fetch one customer row by primary key (and optionally lock it). -
customer_U– Update an existing customer row using the primary key (and possiblyU_VERSION). -
customer_D– Delete a customer row using the primary key (and possiblyU_VERSION). -
customer_S– Select all customer rows.
In your Uniface component, the connector can call these stored procedures internally when you do normal store, read, or update operations. You do not have to call the procedures manually; Uniface does it for you based on your configuration.
When should you enable stored procedures? 💡
Using the procs option makes most sense when:
- You want better performance for common CRUD operations on Informix.
- Your tables mainly use nonsegmented fields (or you are fine with dynamic SQL for the segmented ones).
- You like a clear, predictable mapping between Uniface I/O and Informix stored procedures.
If you have many segmented fields or very complex SQL logic, you may still rely more on dynamic SQL or custom SQL statements. But for standard, frequent operations, the generated stored procedures are a clean and efficient solution.
Final thoughts 😊
Uniface 10.4 and Informix work well together when you use the INF connector options correctly. With the procs option, you get a set of ready-made stored procedures that speed up basic I/O.
Add in the special behavior for segmented and serial fields, and you have a flexible setup: fast where possible, dynamic where necessary.
Happy coding with Uniface and Informix! 🚀
Top comments (0)