Skip to content

Command

XML element that defines a command.

xpath: srs/def/itm[@model='command']

"Hello World"

XML
1
2
3
4
5
<srs label="Hello">
  <def>
    <itm model="command">select 'Hello World'</itm>
  </def>
</srs>

Attributes

Required

  • name - name of the dataset; default d1, d2, d3... (d + position); must not contain /
    • srssetup - business logic command that is always executed first, see srssetup
  • element value (text or CDATA) - SQL text or a stored procedure name
    • a single name without spaces (e.g. [dbo].[task_set]) is called as a stored procedure; request parameters are matched to the procedure parameters by name, null values are not sent so procedure defaults apply. See stored procedure as endpoint

Optional

  • link - connection name; by default the default application connection is used
    • link="self" - virtual query that lets you query the results of other commands like tables (SQLite syntax); waits for all previous commands
    • link="srs" - lists the SRS definitions of the application
  • label - display label
  • title - title on hover
  • css - CSS class
  • acl - permission required
  • active - whether the command is active; default is true
  • lg - (true/false) show/hide on desktop
  • sm - (true/false) show/hide on mobile
  • ex - (true/false) show/hide the dataset on export (.html, .xlsx, .md, .txt, .xml)
  • opts - options separated by spaces, e.g. option1 option2 option3
    • lg-true, lg-false - override desktop visibility; can be set through business logic in srssetup
    • sm-true, sm-false - override mobile visibility; can be set through business logic in srssetup
    • active-true, active-false - enable or disable the command; can be set through business logic in srssetup
    • UI only
      • uiclipboard - adds checkboxes for each row in the UI; the first column should have a unique value for single-row selection; required by options of type pa_post, pa_post_many and pa_post_clipboard
      • uiheader - displays the result above the tabs; when type is a v_ component, the component is rendered
      • uifooter - displays the result in a collapsible section below the active tab
      • uimenu - removes the command from the tabs; when type is a v_ component, the component is rendered next to the options menu
      • uiauto-sum - adds uisum to all tdecimal columns
      • uihighlight-min - highlights the lowest value in uisum columns; a column is eligible only if every row has a value greater than 0
      • uipaged10 - shows the first 5 rows with "show more" / "show less" buttons
      • uivisible - makes the UI tab visible even without data; by default, commands without data are hidden
      • uiopen - uifooter section is expanded on load
      • uiclose - uifooter section is collapsed on load; by default it is expanded when it has data (desktop only)
    • Data transformations
      • dtacl - filters rows based on the acl column; only rows with an acl value matching the user's role are returned; if no acl column is present, all rows are returned
      • dtsplit - converts a single command into multiple commands based on the name column (or the first column), which specifies the command name; an optional css column sets the style; use auto in the URL for an automatic redirect to the first item in the UI: srs/[ID]/auto
      • dtpivot-cols - creates a pivot table from the command's result; the first column becomes the row header and the remaining columns become rows; see the example below
      • dtsingle - data transformation that:
        • affects .json, .hbs, .frx renderers — first row is returned as a single object
        • affects .html renderer — the table is transposed (one row per column)
        • other renderers are not affected
        • must be set in opts, not in type
    • Other
      • server - server-side only; used to compute properties and not returned in the response
      • job - initiates a "fire and forget" non-query execution performed as a background job; the command timeout is at least 300 seconds
      • post - executes when the request is a POST; runs in a read committed transaction
      • get - executes when the request is a GET
      • pscope - executes only when the command name is in pscope; in the UI, the tab is loaded when it is opened, and a select parameter with the same name loads its list from this command
    • Query effects (optional)
      • jq-nonquery - non-query execution
      • jq-committed - read committed
      • jq-uncommitted - read uncommitted
      • jq-snapshot - snapshot
      • jq-none - none
  • type (default table view v_srs_table)
    • v_srs_tiles - UI tiles view
    • v_srs_description - shows the description node, for example <itm model="command" name="view" label="Opis" type="v_srs_description">select getdate()</itm>
    • v_srs_map - UI map view
    • v_srs_form - UI form view
    • v_srs_header - UI header view
    • v_srs_heading - UI heading view
    • v_srs_iframe - embeds a standalone HTML page
    • v_... - any registered Vue component; it receives the table and api props and can emit reload-event to run the SRS again

srssetup

A command named srssetup always runs first. If it throws an error, nothing else is executed.

Any property of the SRS can be used to modify its behavior with double brackets [[propertyname]], especially:

  • description
  • column label
  • column value
  • column opts (use lg-true, lg-false, sm-true and sm-false to override column visibility)
  • column css
  • parameter value
  • option value
  • command type
  • command opts
  • command css

UI Example

To render a command with a custom UI component, set the type attribute to v_..., where v_... is the name of the component.

XML
1
2
3
4
5
6
7
<srs>
  <def>
  <itm model="command" type="v_srs_heading"  opts="uiheader" name="header" >
      select 'test1' column1 , 'test' column2
  </itm>
  </def>
</srs>

Data transformation examples

dtpivot-cols

Input:

YEAR ESTIMATED BUDGET SPENT
2024 900 1000 1200
2025 1100 1000 1300
2026 1200 1200 1400
2027 1400 1300 1500

Output:

YEAR 2024 2025 2026 2027
estimated 900 1100 1200 1400
budget 1000 1000 1200 1300
spent 1200 1300 1400 1500

Transactions and isolation levels - SQL Server

By default:

  • every command runs in a transaction with isolation level read uncommitted
  • if command opts contain post, the isolation level is read committed and the command runs in a transaction that is committed when the command ends successfully, otherwise rolled back

You can override the default behavior by adding a keyword to the command opts:

  • read committed in a transaction by adding jq-committed
  • read uncommitted by adding jq-uncommitted
  • snapshot in a transaction by adding jq-snapshot
  • unspecified by adding jq-none - no transaction and no isolation level is set

Example jq-none scenario:

Imagine a complex procedure where you want to manage the transaction manually and you don't want an outer transaction context, for example to write to a custom log table or to commit part of the code. Add jq-none to the command opts to achieve this.

When the isolation level is defined in the command:

SQL
1
2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
select 'Force transaction isolation level Read committed'

Note

Snapshot isolation requires ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to be set to ON in the database.

linked servers

If the command returns "Unable to enlist in the transaction", set the transaction to jq-none or review the linked server properties.

Isolation Level Dirty Reads Phantom Reads Unrepeatable Reads Readers Block Writers Writers Block Readers Readers And Writers Deadlock
Read Uncommitted/NOLOCK Yes Yes Yes No No No
Read Committed No Yes Yes Yes Yes Yes
Read Committed Snapshot Isolation No No Yes No No No