Executing SQL Natively
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.
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=false and spam=false";
int updates = sql:execute(query);
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=false and resolved=false";
int updates = sql:execute(query);
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.