Executing SQL Natively

Owned by Jacques Marais
Last updated: Sept 05, 2024 by Charl Viljoen


Description

Helium provides two built-in functions which can be used to execute SQL natively. These are described below.



Querying Data Using SQL

To execute select queries natively the sql:query built-in function can be used. The arguments that need to be passed to the function are:

  • The query to be executed. This has to be a select query. It can be passed as argument using a string literal, result from a function or variable.
  • The arguments for the query parameters. Each presented as a separate function argument. Once again values can be from a literal value, function result or variable. If no arguments are required for the query, these can be left out.

The results returned from the function needs to be assigned to a collection of objects where the object attributes corresponds to the data columns returned by the query with respect to name and type. The match between the data column title and attribute names is, however, case insensitive.

The following result shows the usage of the sql:query function in a function that is used as a collection source for a data table. In addition the model object used for query results is also shown.

// Execute SQL for report data and return as collection source for data table
PurchaseFrequencyResult[] getPurchaseFrequency() {
    string query = /%
        WITH intervals AS (
            SELECT weekstarts.weekstart AS weekstart, weekstarts.weekstart + 7 AS weekend
            FROM (
                SELECT 
                weeks.i - cast(extract(dow from weeks.i) as int) + 1 as weekstart
                FROM (
                    SELECT i::date from generate_series(?, ?, '1 week'::interval) i
                ) AS weeks
            ) AS weekstarts
        )
        SELECT weekstart, weekend, count(*)::int
        FROM farmerpurchase 
        JOIN intervals ON farmerpurchase.purchasedon > (weekstart - 1) and farmerpurchase.purchasedon < (weekend + 1) where farmerpurchase.shop_fk = ? GROUP BY weekstart, weekend;
    %/;
    
    PurchaseFrequencyResult[] result = sql:query(query, startDate, endDate, selectedShop._id);
    return result;
}
// Object used for results from purchase frequency SQL select query
object PurchaseFrequencyResult {
    date weekStart;
    date weekEnd;
    int count;
}

Be aware that SQL that is executed using sql:query is not executed when the sql:query statement is encountered. Rather, it is executed when the result set is first referenced. This means that if the SQL contains errors which result in runtime errors, the error will not be generated as part of sql:query but rather as part of the code that accesses the result set namely the result collection in the example above.

For more detail on the usage of sql:query and this example please have a look at Lesson 22 in the Helium Tutorial.



Updating Data Using SQL

Helium also provides functionality to execute queries other than select queries. This is achieved by using the sql:execute built-in function. This function works similarly to the sql:query function with the exception of the return type. For sql:execute an int representing the number of updated records is returned. Note that this function also accepts parameters as with the sql:query and it is imperative that developers use this functionality instead of concatenating strings for their execution queries in order to prevent SQL Injection.

The following example demonstrates the usage of sql:execute:

// Resolve tickets that have not been resolved and not been marked as spam
DSL_VIEWS resolveAllTickets() {
    string query = "update supportticket set resolved=true where resolved=? and spam=?";
    int updates = sql:execute(query, false, false);
    Mez:alert("alert.tickets_resolved");
    return DSL_VIEWS.Support;
}

// Mark tickets as spam that have not been marked as such and have not been resolved
DSL_VIEWS markAllTicketsAsSpam() {
    string query = "update supportticket set spam=true where spam=? and resolved=?";
    int updates = sql:execute(query, false, false);
    Mez:alert("alert.tickets_marked_as_spam");
    return DSL_VIEWS.Support;
}
alert.tickets_resolved = {updates} tickets resolved.
alert.tickets_marked_as_spam = {updates} tickets marked as spam.


For more detail on the usage of sql:execute and this example please have a look at Lesson 23 in the Helium Tutorial.

A Note on Temp Tables

It's important to note that SQL making use of temporary tables are, in most cases, not supported in Helium and in general the use of temporary tables in DSL apps is discouraged. Please see additional notes on this here.

A Note on Functions and Procedures

Often developers would want to store PostgreSQL functions and procedures against their schema's and then run them during runtime execution. This can be achieved using the sql:execute DSL built-in function, but take note to execute these correctly:

// Execute a stored function on the database schema
DSL_VIEWS executeSqlFunction() {
    string query = "select fn_update_ticket_status(?, ?)";
    int updates = sql:execute(query, false, false);
    Mez:alert("alert.tickets_marked_as_spam");
    return DSL_VIEWS.Support;
}

// Execute a stored procedure on the database schema
DSL_VIEWS executeSqlProcedure() {
    string query = "call pd_update_ticket_status(?, ?)";
    int updates = sql:execute(query, false, false);
    Mez:alert("alert.tickets_marked_as_spam");
    return DSL_VIEWS.Support;
}

There is a caveat to this however, whereby the integer returned by the sql:execute built-in function will always be '0'.


Additional Mentions and References