Bulk - Contains multiple calls in a single query template, optionally the calls can be sent in a single transaction.
Bulk queries
Cache
The query results can be cached using special helpers in query templates. These work for both single and bulk queries.
When used then automat will return the cached results if they exist. The second parameters is how many minutes the cache will be valid. If the cache is expired or doesn’t exist then the query will be executed and the results will be cached for future use.
{{ .SetCache 10 }}
Optionally we can also reset the cache if needed. This will do both return fresh results and reset the cache timer with the new results.
{{ .ResetCache }}
Results
Result is given as gjson.Result type. We can then read it similar to dynamic api results.
The regular query call will fail the entire template render process if the query fails. Use the new V2 method if the application
requires special customer error handling for possible query or db issues.
{{ $res = .Tools.AppData.RunQueryV2 "demo-app-single-query" }}
{{ if $res.IsSuccess }}
{{ .Tools.DebugLog "ok" }}
{{ end }}
{{ $res.Result }}
Check if there was an error and then record it where needed
{{ $res = .Tools.AppData.RunQueryV2 "demo-app-single-query" }}
{{ if $res.IsSuccess }}
{{ .Tools.DebugLog "ok" }}
{{ end }}
{{ if $res.IsError }}
{{ .Tools.DebugLog $r.Error }}
{{ .Tools.AddError $r.Error }}
{{ end }}
{{ $res.Result }}
Fetch the manual errors on the page (need to be after the function execution)
When we use the .Tools.AddError to set manual errors.
<div> {{ range .Tools.Errors }}
<span>{{ . }}</span> {{ end }}
</div>
Subsections of Query
Single query calls
Send a single query call and use its results.
Usage
Read arguments
Read an argument values from input index.
The value keeps its original type. Note that gjson.Result type does not convert to the contained type automatically so
it would need to be converted first (ex: value.Int, value.String etc).
{{ $id := .Arg 0 }}
Set query
Sets or replaces the current query.
{{ .SetQuery `SELECT id FROM product` }}
Add query
Appends to the query. Useful when adding conditional query elements.
{{ .AddQuery ` WHERE id = ?` }}
Return column types
By default, all values in the result are returned as strings (or not at all). By setting the column types we can return the values in their original type.
{{ .SetColumnTypes "id" "int" }}
Arguments
Important!
Always use ? placeholders and the following set argument call for queries. This protects against injections,
filling argument values directly into queries poses severe security risks.
{{ .AddArg $id }}
From array argument helper
We can use a helper to fill query in values from an array.
{{ .AddQueryAndArgsFromArray ` WHERE id IN (?)` $ids }}
From string argument helper
We can use a helper to fill query in values from a comma separated string.
{{ .AddQueryAndArgsFromString ` WHERE id IN (?)` $ids "," }}
Bulk query calls
Send multiple query calls in a single query template. Optionally the calls can be sent in a single transaction.
Usage
Read arguments
Read an argument values from input index.
The value keeps its original type. Note that gjson.Result type does not convert to the contained type automatically so
it would need to be converted first (ex: value.Int, value.String etc).
{{ $id := .Arg 0 }}
Bulk call size
Set the amount of calls in a single bulk query template. This is required to be set before any calls are added.
If we intend to send 3 calls in a single bulk query template then we would set the size to 3.
{{ .SetAsBulkQuery 3 }}
Transactional bulk query
We can send the calls in a single transaction by setting the transactional flag to true. This means that if any of the calls fail then all of the calls will be rolled back.
{{ .SetInTransaction }}
Set query
Sets or replaces the query in the given index.
Note that in bulk we always pass the index of the call as second parameter. This is required to differenciate the calls from each other.
{{ .SetBulkQuery `INSERT INTO prodgroup (name_eng) VALUES (?)` 0 }}
Add query
Appends to the query in the given index. Useful when adding conditional query elements.
{{ .AddBulkQuery ` WHERE id = ?` 0 }}
Return column types
By default, all values in the result are returned as strings (or not at all). By setting the column types we can return the values in their original type.
{{ .SetBulkColumnType "lastInsertId" "int" 0 }}
Arguments
Important!
Always use ? placeholders and the following set argument call for queries. This protects against injections,
filling argument values directly into queries poses severe security risks.
{{ .AddBulkArg $id 0 }}
Using first query values in send index calls
Sometimes we need to use the results of the first query in the following calls. We can do this by using the ‘first query values’ argument in the following calls.
Note that we need to set the column types for the returned values in the first query for this to work.
{{ .AddBulkArg "0.lastInsertId" 1 }}
From array argument helper
We can use an helper to fill query in values from an array.
{{ .AddBulkQueryAndArgsFromArray ` WHERE id IN (?)` $ids 0 }}
From string argument helper
We can use an helper to fill query in values from a comma separated string.
{{ .AddBulkQueryAndArgsFromString ` WHERE id IN (?)` $ids "," 0 }}