Overview:

This session explores how PostgreSQL’s JSON and JSONB features can be combined with Omnis for flexible, structured data exchange.   We’ll discuss passing JSON arrays to PostgreSQL procedures for bulk operations and cross-system queries, as well as storing JSON in Omnis for metadata.

What You’ll Learn:

In this session we’ll cover:

• Passing JSON arrays between PostgreSQL and Omnis.   By way of example, an invoice can have a complex Object Relational Model (ORM) that comprises many relational database tables.   We’ll use this to show how Omnis can transmit the invoice data as a JSON array; and how PostgreSQL can parse & insert it in a set-based operation using a single call to the database.
• Writing PostgreSQL procedures that accept JSON input and transform it into relational sets for insert or update operations.
• How to combine data from multiple systems using JSON. Encode data from an Omnis datafile as JSON, send it to PostgreSQL, and query or join it with external sources like SQL Server.
• Dynamic data injection via JSON structures: using JSON as a mechanism to drive procedure logic or queries.   This opens possibilities for flexible, metadata-driven interactions.

Overview:

This session will focus on advanced SQL features available in nearly any SQL server.   We’ll discuss eliminating costly Omnis side list looping exercises.   In addition, we will talk about table pivoting, analytic functions, aggregations, inline views, and using Common Table Expressions (CTEs) for complex insert and upsert operations.

Part of the session will show how to create an abstracted backend layer for flexible data access across different database engines.

What You’ll Learn:

• Building code with complex SQL concepts like pivots, aggregations, temporary tables, and inline views.
• Using CTEs to simplify multi-step inserts, like parent/child inserts (e.g., invoice headers and details), in a single upsert statement for improved efficiency and readability.
• Designing backend-specific Omnis SQL objects with consistent method names to abstract SQL operations across backends.   This allows your application to target multiple databases with minimal changes or expand into dynamic backend support.
• Enhancing Omnis smartlists with modular SQL logic for inserts, updates, and deletes across supported backends.