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 from 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 uses the default app connection
    • link="self" is a special provider that lets you query different commands like databases
  • 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 (e.g. to Excel)
  • opts - options separated by spaces, e.g. option1 option2 option3
    • 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.
      • 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
      • dtsplit - Converts a single command into multiple commands based on the name column (or the first column), and an optional acl column. The name column specifies the command's name; the acl column assigns a role. 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 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 — returns a dtpivot-cols transformation
        • other renderers are not affected
    • 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.
      • post - Executes when the request is a POST; sets isolation level to jq-committed by default.
      • 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_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 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