Versions Compared
Key
- This line was added.
- This line was removed.
- Formatting was changed.
Table of Contents |
---|
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.
Code Block | ||||
---|---|---|---|---|
| ||||
// 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; } |
Code Block |
---|
// 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
:
Code Block |
---|
// 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, 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=false? and resolved=false?"; int updates = sql:execute(query, false, false); Mez:alert("alert.tickets_marked_as_spam"); return DSL_VIEWS.Support; } |
Code Block |
---|
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:
Code Block |
---|
// 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'.
Excerpt | ||
---|---|---|
| ||
sql:query | sql:execute |