DEV Community

Peter + AI
Peter + AI

Posted on

🚀 Understanding the SQL Statement in Uniface 10.4: A Developer's Guide

#uniface #procscript #database #4gl

Note: This blog post was created with AI assistance, based on official Uniface 10.4 documentation.

The sql statement in Uniface 10.4 is a powerful tool that allows developers to execute raw database queries directly from ProcScript code. Whether you're working with Oracle, SQL Server, MySQL, or other supported databases, this statement gives you the flexibility to perform complex database operations that might be difficult to achieve with standard Uniface components. 💪

🤔 What is Uniface?

Before diving into the SQL statement, let's clarify what Uniface is. Uniface is a 4GL (Fourth Generation Language) development platform created by Rocket Software. It's designed for building enterprise applications that can run on web, desktop, and mobile platforms. The platform uses a model-driven approach and includes ProcScript - Uniface's scripting language for application behavior.

📝 Basic Syntax

The basic syntax of the SQL statement is straightforward:

sql{/data {/fieldname}} {/print} DMLStatement, "PathString"
Enter fullscreen mode Exit fullscreen mode

Here's what each part means:

  • DMLStatement: Your SQL query (up to 32KB)
  • PathString: Database connection name without the $ sign
  • Qualifiers: Optional switches that change how results are returned

🎯 Understanding the Qualifiers

The SQL statement comes with three useful qualifiers that control how your results are returned:

/data - Get Structured Results 📊

The /data qualifier returns complete query results as a nested Uniface list. Each row becomes a list item, and each column becomes a sub-item. This makes it perfect for programmatic data processing:

sql/data "select customer_id, customer_name from customers", "ora"
; Results are now in $result as a structured list
Enter fullscreen mode Exit fullscreen mode

/fieldname - Add Column Headers 📋

When combined with /data, the /fieldname qualifier adds column names as the first item in your result list:

sql/data/fieldname "select customer_id, customer_name from customers", "ora"
; First item in $result now contains: customer_id, customer_name
Enter fullscreen mode Exit fullscreen mode

/print - Formatted Output 🖨️

The /print qualifier returns results in a human-readable format with columns and whitespace, similar to what you'd see in a SQL workbench:

sql/print "select * from customers where city = 'London'", "mssql"
putmess $result  ; Display formatted results
Enter fullscreen mode Exit fullscreen mode

💡 Practical Examples

Basic Validation Check ✅

Here's a common use case - checking if a value already exists in the database:

trigger loseFocus
  if ($fieldendmod != 0)
    sql "select invnum from invoices where invnum = '%%invnum%%%'", "ora"
    if ($status > 0)
      message "Invoice number already exists!"
      return (-1)
    endif
  endif
end
Enter fullscreen mode Exit fullscreen mode

Dynamic Value Lists 📝

You can use sql/data to populate dropdown lists dynamically:

function getCountryList
  sql/data "select country_code, country_name from countries order by country_name", "def"
  forlist vRecord in $result
    getitem vCode, vRecord, 1
    getitem vName, vRecord, 2
    putitem/id pCountryList, vCode, vName
  endfor
end
Enter fullscreen mode Exit fullscreen mode

⚠️ Understanding Return Values

After executing an SQL statement, Uniface provides feedback through system variables:

$status Values 📊

  • ≥ 0: Number of records found/affected
  • -1: Invalid or missing path
  • -3: I/O error or empty DML statement
  • -9: Maximum database connections reached
  • -11: Record already locked
  • -31: SQL statement exceeds 32KB limit

$result Variable 💾

The $result variable contains your query results. For basic queries, it holds the first column of the last selected row. With qualifiers, it contains formatted or structured data as described above.

🔧 Best Practices and Tips

Keep It Portable 🌐

While the SQL statement is powerful, consider using standard Uniface I/O operations when possible. This keeps your application more portable across different database systems and easier to maintain.

Avoid Transaction Control ❌

Don't use COMMIT or ROLLBACK statements within your SQL commands. Uniface manages transactions internally, and manual transaction control can disrupt this consistency.

Handle Long Data Carefully 📏

When working with Oracle's Long data types, be aware that the SQL statement truncates at 42 bytes. Use standard Uniface entity retrieval for Long fields instead.

Error Handling 🛡️

Always check $status after SQL operations:

sql "update customers set status = 'ACTIVE' where customer_id = %%vCustomerId", "mssql"
if ($status < 0)
  message "Update failed: %%$procerror"
  return (-1)
endif
Enter fullscreen mode Exit fullscreen mode

🎯 When to Use SQL vs Standard Uniface I/O

Use the SQL statement when you need:

  • Complex joins across multiple tables
  • Aggregate functions (COUNT, SUM, AVG)
  • Database-specific functions that Uniface doesn't support
  • Bulk operations that would be inefficient with standard I/O

Stick with standard Uniface retrieve/store operations for:

  • Simple CRUD operations
  • Form-based data entry
  • Applications that need database portability

🏁 Conclusion

The SQL statement in Uniface 10.4 bridges the gap between Uniface's high-level data operations and raw database power. While it should be used judiciously, it's an essential tool for complex database operations that go beyond standard form-based data manipulation. Remember to always handle errors properly and consider the portability implications of your SQL code! 🎉

Happy coding with Uniface! 👨‍💻👩‍💻

Top comments (0)