Skip to content

Command

XML element with command definition 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>

Command Attributes

Required

  • name - name of dataset; default d1, d2, d3... (d + position). Must not contain /.
    • name=srssetup => business logic command that is always executed first
  • body of command
    • 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

srssetup

  • srs with name =srssetup run always as first
  • if throw error nothing will be executed

Any property from srs can be used to modify srs behavior, required double brackets [[propertyname]] especially:

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

Optional

  • link - connection name; by default uses the default app connection
    • link="self" virtual query that lets you query different commands like tables (SQLite syntax); waits for all previous commands
    • link="srs" lists SRS definitions of the application
  • label - display label
  • title - title on hover
  • css - class attribute
  • acl - role required
  • active - 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 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.
      • uiheader - Displays the result in a header.
      • uifooter - UI footer
      • uinav - UI navigation (TODO: check if obsolete)
      • uimenu - Renders in the command tab of the UI.
      • uiauto-sum - sum all decimal values
      • uihighlight-min - highlight column with lowest values a column is only eligible for the if every row has a value >0
      • uivisible - Makes the UI tab visible even without data. By default, commands without data are hidden.
        • uiopen - when the command is in an accordion, forces the initial state to open
        • uiclose - when the command is in an accordion, forces the initial state to closed
    • 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) The name column specifies the command's name. Optional css column for style. Use in url auto for automatic redirect to the first item in UI via srs/[ID]/auto.
      • dtpivot-cols - Creates a pivot table from the command's result. The first column becomes the row header; 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, 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 when the command name is in pscope.
    • 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 - show description node ex <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 header view
    • v_srs_iframe - embed a standalone html page
    • v_.... - any component

UI Example

When you want render command using custom UI component you need to use type attribute with value v_... . v_... is a name of component that will be used to render command.

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:

  • the isolation level is read uncommitted
  • a SQL text command runs without a transaction; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is added in front of the command in the same batch
  • a stored procedure or a jq-nonquery command runs in a read uncommitted transaction
  • 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 complex procedure, when you want manage transaction manually inside the procedure and you don't want outer transaction context. for example write to custom log table or commit part of code that can be achieved by adding jq-none to the command opts.

When isolation level is defined in 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