Encoding in SQL functions

Introduce SQL functions like TO_BASE64(), FROM_BASE64(), and UUID() to enable data encoding, decoding, and unique identifier generation.

1️⃣ Description of the idea

Add built-in SQL functions to the InterSystems SQL engine for common utility tasks:

• TO_BASE64(input) – Encodes binary or string data to a Base64 string

• FROM_BASE64(input) – Decodes Base64 strings back to their original binary or string form

• UUID() – Generates a universally unique identifier (UUID) in standard string format (e.g., 550e8400-e29b-41d4-a716-446655440000)

These functions would be available directly in SQL queries, stored procedures, and triggers.

2️⃣ Who is the target audience?

SQL developers working with data transformation and API integration and developers using SQL for encoding.

3️⃣ What problem does it solve?

Currently, encoding/decoding data and generating UUIDs often require Calling ObjectScript methods manually or Stored procedures

Adding these functions solves Limited ability to work with encoded data directly in SQL

4️⃣ How does this impact the efficiency, stability, reliability, etc., of the product?

Simplifies development by enabling single-step transformations within SQL and Built-in functions are more consistent and tested than user-defined alternatives

5️⃣ Provide a specific use case or scenario that illustrates how this idea could be used in practice

When binary image data or encode conversion required fields is stored in the database and returned as Base64-encoded strings for JSON responses.

The current approach involves retrieving the data using SQL and then relying on ObjectScript logic. With these built-in functions, the process becomes much simpler and more efficient, as encoding can be handled directly within the SQL query.