Using Dynamic & Embedded SQL with InterSystems IRIS

Hi Community, 
In this article, we will explore the concepts of Dynamic SQL and Embedded SQL within the context of InterSystems IRIS, provide practical examples, and examine their differences to help you understand how to leverage them in your applications.

InterSystems SQL provides a full set of standard relational features, including the ability to define table schema, execute queries, and define and execute stored procedures. You can execute InterSystems SQL interactively from the Management Portal or programmatically using a SQL shell interface. Embedded SQL enables you to embed SQL statements in your ObjectScript code, while Dynamic SQL enables you to execute dynamic SQL statements from ObjectScript at runtime. While static SQL queries offer predictable performance, dynamic and embedded SQL offer flexibility and integration, respectively.

 Dynamic SQL

Dynamic SQL refers to SQL statements that are constructed and executed at runtime, as opposed to static SQL, which is predefined and embedded directly in the application code. Dynamic SQL is particularly useful when the structure of a query is not known in advance or needs to be dynamically adjusted based on user input or application logic.

In InterSystems IRIS, Dynamic SQL is implemented through the %SQL.Statement class, which provides methods for preparing and executing SQL statements dynamically.

Key Benefits of Dynamic SQL

1. Flexibility: Dynamic SQL allows you to build queries programmatically, making it ideal for applications with complex or changing requirements.
2. Adaptability: You can modify queries based on runtime conditions, such as user input or application state.
3. Ad-Hoc Queries: If the application needs to generate custom queries based on user input, Dynamic SQL allows the construction of these queries at runtime.
4. Complex Joins and Conditions: In scenarios where the number of joins or conditions can change based on data, Dynamic SQL enables the construction of complex queries.

Practical Examples

1- Dynamic Table Creation: Building Database Schemas on the Fly

This example demonstrates how to dynamically create a table at runtime using InterSystems Dynamic SQL, enabling flexible and adaptive database schema management.

ClassMethod CreateDynamicTable(tableName As%String, columns As%String) As%Status
{
    // Construct sql textSet sql = "CREATE TABLE " _ tableName _ " (" _ columns _ ")"//Create an instance of %SQL.StatementSet statement = ##class(%SQL.Statement).%New()
    //Prepare the querySet status = statement.%Prepare(sql)
    If$$$ISERR(status) {
        Quit status
    }
    //Execute the querySet result = statement.%Execute()
    //Check for errorsIf result.%SQLCODE = 0 {
        Write"Table created successfully!", !
    } Else {
        Write"Error: ", result.%SQLCODE, " ", result.%SQLMSG, !
    }
    Quit$$$OK
}

Invoke Method

USER>do##class(dc.DESql).CreateDynamicTable("Books","BookID NUMBER NOT NULL,Title VARCHAR(100),Author VARCHAR(300),PublicationYear  NUMBER NULL, AvailableFlag  BIT")

Output

2- Dynamic Table Search: Querying Data with User-Defined Filters

This example illustrates how to perform a dynamic table search based on user-defined criteria, enabling flexible and adaptable querying.

ClassMethod DynamicSearchPerson(name As%String = "", age As%Integer = "") As%Status
{
    // Create an instance of %SQL.Statementset stmt = ##class(%SQL.Statement).%New()

    // Base queryset query = "SELECT ID, Name, Age, DOB FROM Sample.Person"// Add conditions based on input parametersif name '= "" {
        set query = query _ " WHERE Name %STARTSWITH ?"
    }
    if (age '= "") && (name '= "") {
        set query = query _ " AND Age = ?"
    }
    if (age '= "") && (name = "") {
        set query = query _ " WHERE Age = ?"
    }
    
    // Prepare the queryset status = stmt.%Prepare(query)
    if$$$ISERR(status) {
        do$System.Status.DisplayError(status)
        quit status
    }
   
    // Execute the query with parametersif (age '= "") && (name '= "") {
        set rset = stmt.%Execute(name, age)
    }
    if (age '= "") && (name = "") {
        set rset = stmt.%Execute(age)
    }
    if (age = "") && (name '= "") {
        set rset = stmt.%Execute(name)
    }

    // Display resultswhile rset.%Next() {
        write"ID: ", rset.ID, " Name: ", rset.Name, " Age: ", rset.Age,  !
    }

    quit$$$OK
}

Invoke Method

do##class(dc.DESql).DynamicSearchPerson("Y",67)

Output

3- Dynamic Pivot Tables: Transforming Data for Analytical Insights

This example showcases how to dynamically generate a pivot table using InterSystems Dynamic SQL, transforming raw data into a structured summary.

ClassMethod GeneratePivotTable(tableName As%String, rowDimension As%String, columnDimension As%String, valueColumn As%String) As%Status
{
    // Simplified example; real pivot tables can be complexSet sql = "SELECT " _ rowDimension _ ", " _ columnDimension _ ", SUM(" _ valueColumn _ ") FROM " _ tableName _ " GROUP BY " _ rowDimension _ ", " _ columnDimension
    //Create an instance of %SQL.StatementSet statement = ##class(%SQL.Statement).%New()
    // Prepare the querySet status = statement.%Prepare(sql)
   
    If$$$ISERR(status) {
        Quit status
    }
    // Execute the querySet result = statement.%Execute()
    // Check for errorsIf result.%SQLCODE = 0 {
        While result.%Next() {
            do result.%Display()
        }
    } Else {
        Write"Error: ", result.%SQLCODE, " ", result.%SQLMSG, !
    }
    Quit$$$OK
}

Invoke Method

Do##class(dc.DESql).GeneratePivotTable("Sales", "Region", "ProductCategory", "Revenue")

Output

4- Schema Exploration: Unlocking Database Metadata with Dynamic SQL

This example demonstrates how to explore and retrieve metadata about database schemas dynamically, providing insights into table structures and column definitions.

ClassMethod ExploreTableSchema(tableName As%String) As%Status
{
    // Create a new SQL statement objectset stmt = ##class(%SQL.Statement).%New()
    
    // Construct the query dynamicallyset sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA||'.'||TABLE_NAME = ?"// Prepare the queryset status = stmt.%Prepare(sql)
    if$$$ISERR(status) {
        do$System.Status.DisplayError(status)
        quit status
    }
   
    // Execute the queryset result = stmt.%Execute(tableName)
    
    // Display schema informationwrite !, "Schema for Table: ", tableName
    write !, "-------------------------"write !, "Column Name",?15, "Data Type", ?30, "Nullable ",?45,"Column#"write !, "-------------------------"while result.%Next() {
        
        write !, result.%Get("COLUMN_NAME"),?15, result.%Get("DATA_TYPE"), ?30,  result.%Get("IS_NULLABLE"), ?45,result.%Get("ORDINAL_POSITION") 
        
    }
    
    quit$$$OK
}

Invoke Method

Do##class(dc.DESql).ExploreTableSchema("Sample.Person")

Output

 

Embedded SQL

Embedded SQL is a method of including SQL statements directly within your programming language (in this case, ObjectScript or another InterSystems-compatible language). Embedded SQL is not compiled when the routine that contains it is compiled. Instead, compilation of Embedded SQL occurs upon the first execution of the SQL code (runtime). It is quite powerful when used in conjunction with the object access capability of InterSystems IRIS.

You can embed SQL statements within the ObjectScript code used by the InterSystems IRIS® data platform. These Embedded SQL statements are converted to optimized, executable code at runtime. Embedded SQL is particularly useful for performing database operations such as querying, inserting, updating, and deleting records.

There are two kinds of Embedded SQL:

• A simple Embedded SQL query can only return values from a single row. Simple Embedded SQL can also be used for single-row insert, update, and delete, and for other SQL operations.
• A cursor-based Embedded SQL query can iterate through a query result set, returning values from multiple rows. Cursor-based Embedded SQL can also be used for multiple-row update and delete SQL operations.

Key Benefits of Embedded SQL

1. Seamless Integration: Embedded SQL allows you to write SQL statements directly within ObjectScript code, eliminating the need for external calls or complex interfaces.
2. Performance: By embedding SQL within ObjectScript, you can optimize database interactions and reduce overhead.
3. Simplicity: Embedded SQL simplifies the process of working with databases, as it eliminates the need for separate SQL scripts or external tools.
4. Error Handling: Embedded SQL allows for better error handling since the SQL code is part of the application logic.

 Practical Examples

1-Record Creation: Inserting Data with Embedded SQL

This example demonstrates how to insert a new record into a table using Embedded SQL, ensuring seamless data integration.

ClassMethod AddBook(bookID As%Integer, title As%String, author As%String, year As%Integer, available As%Boolean) As%Status
{
    // Embedded SQL to insert a new book
    &sql(INSERTINTO SQLUser.Books (BookID, Title, Author, PublicationYear, AvailableFlag)
        VALUES (:bookID, :title, :author, :year, :available)
    )

    // Check for errorsif SQLCODE '= 0 {
        write"Error inserting book: ", %msg, !
        quit$$$ERROR($$$GeneralError, "Insert failed")
    }

    write"Book added successfully!", !
    quit$$$OK
}

Invoke Method

Do##class(dc.DESql).AddBook(1,"To Kill a Mockingbird","Harper Lee", 1960,1)

Output

2-Data Retrieval: Fetching and Displaying Records with Embedded SQL

This example retrieves a list of books from a database using Embedded SQL, showcasing how to fetch and display data efficiently.

ClassMethod ListBooks()
{
    // Embedded SQL to query books
    &sql(DECLARE BookCursor CURSORFORSELECT BookID, Title, Author, PublicationYear, AvailableFlag
        FROM SQLUser.Books
        WHERE AvailableFlag = 1)

    // Open the cursor
    &sql(OPEN BookCursor)

    // Fetch and display resultsfor {
        &sql(FETCH BookCursor INTO :bookID, :title, :author, :year, :available)
        quit:(SQLCODE '= 0)

        write"Book ID: ", bookID, !
        write"Title: ", title, !
        write"Author: ", author, !
        write"Publication Year: ", year, !
        write"Available: ", available, !
        write"-----------------------------", !
    }

    // Close the cursor
    &sql(CLOSE BookCursor)
}

Invoke Method

Do##class(dc.DESql).ListBooks()

Output

3- Transaction Management: Ensuring Data Integrity with Embedded SQL

This example demonstrates how to manage database transactions using Embedded SQL, ensuring data integrity during fund transfers.

ClassMethod TransferFunds(fromAccount As%Integer, toAccount As%Integer, amount As%Decimal) As%Status
{
    // Start a transactionTSTART// Deduct amount from the source account
    &sql(UPDATE Accounts
         SET Balance = Balance - :amount
         WHERE AccountID = :fromAccount)
    
    if SQLCODE '= 0 {
        TROLLBACKquit$$$ERROR($$$GeneralError, "Failed to deduct amount from source account.")
    }
    
    // Add amount to the destination account
    &sql(UPDATE Accounts
         SET Balance = Balance + :amount
         WHERE AccountID = :toAccount)
    
    if SQLCODE '= 0 {
        TROLLBACKquit$$$ERROR($$$GeneralError, "Failed to add amount to destination account.")
    }
    
    // Commit the transactionTCOMMITwrite !, "Funds transferred successfully."quit$$$OK
}

Invoke Method

do##class(MyApp.FundManager).TransferFunds(101, 102, 500.00)

Output

4- Validate Username Availability

This example checks if a username is available for use by querying the database to ensure it does not already exist.

ClassMethod ValidateUserName(username As%String) As%Boolean
{
    // Embedded SQL to check if the username exists
    &sql(SELECTCOUNT(*) INTO :count
         FROM SQLUser.Users
         WHERE Name = :username)
    //Check for errorsif SQLCODE = 0 {
        if count > 0 {
            write !, "Username already exists."quit0
        } else {
            write !, "Username is available."quit1
        }
    } else {
        write !, "Error validating username: ", %msgquit0
    }
}

Invoke Method

Do##class(dc.DESql).ValidateUserName("Admin")

Output

Comparison Between Dynamic SQL & Embedded SQL

Conclusion

Dynamic SQL and Embedded SQL are powerful tools in InterSystems IRIS that cater to different use cases. Dynamic SQL provides flexibility for runtime query construction, while Embedded SQL offers performance benefits for static queries. By understanding their strengths and combining them effectively, you can build robust and efficient applications on the InterSystems IRIS platform.
Thanks