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 value 1,2,3,4,5
    • name=srssetup => business logic command that is always executed first
  • body of command
    • sql command/procedure

srssetup

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

Any property fro srs can be used to modify srs behavior 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 use default APP connection
    • link="self" is a special kind of provider you can query different commands (from diferent connection) like databases
  • label - display label
  • title - title on hover
  • css - class attribute
  • acl - role required
  • active - command is active default =true
  • lg - (true/false) show/hide on desktop
  • sm - (true/false) show/hide on mobile
  • ex - (true/false) show/hide dataset on export for example to excel
  • opts - options separated with space e.g. option1 option2 option3
    • UI only
      • uiclipboard - Adds additional checkboxes for each row in the UI. Important, first column should has unique value in command for select unique (one) row.
      • uiheader - Displays result in a header.
      • uifooter - UI footer
      • uinav - UI navigation (TODO: check if obsolete)
      • uimenu - Renders in the command tab of the UI.
      • uivisible - Makes the UI tab visible even without data. By default, commands without data are hidden.
        • uiopen when command is in accordion you can force initial state of accordion
        • uiclose when command is in accordion you can force initial state of accordion
    • Data transformations
      • dtsplit - This data transformation converts a single command into multiple commands based on the name column or first, and an optional acl column. The name column specifies the command's name, while the acl column assigns a role to the command. Only the first acl value is used as the default ACL for the new command. Optional css column for style and auto for automatic redirect to first in ui srs/[ID]/auto
      • dtpivot-cols - This data transformation creates a pivot table from the command's result. The pivot table will use the first column as the row header and the remaining columns as the rows. See the example below.
      • dtsingle - This data transformation
        • affects .json, .hbs, .frx renderer, first row is returned as a single object.
        • affects .html renderer, returns dtpivot-cols transformation
        • other renderers are not affected
    • Other
      • server - server side only used to compute properties, not returned
      • job - Initiates a "fire and forget" non-query execution performed as a job.
      • post - Executes when the request is a POST, set isolation level by default to jq-committed
      • get - Executes when the request is a GET.
      • pscope - Executes when command name in pscope
    • Query effects (optional)
      • jq-nonquery - non values
      • jq-committed - read committed
      • jq-uncommitted - read uncommited
      • jq-snapshot snapshot
      • jq-none none
  • type (default table view v_srs_table)
    • v_srs_tiles - UI tiles view
    • 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_.... - 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:

  • when API receives GET,POST request, the transaction isolation level is by default read uncommitted
  • if command opts contain post keyword then the transaction isolation level is read committed and transaction is used by the system and committed when command ends successfully otherwise rollback

You can override default behavior and force transaction and isolation by adding keyword to the command opts.:

  • read committed by adding jq-committed
  • read uncommited by adding jq-uncommitted
  • snapshot * by adding jq-snapshot
  • unspecified by adding jq-none - this will remove transaction context

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 transaction level require 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, you need to set the transaction to jq-none or experiment with 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