Package org.labkey.remoteapi.query

Class ExecuteSqlCommand

java.lang.Object
org.labkey.remoteapi.Command<ResponseType,org.apache.hc.client5.http.classic.methods.HttpPost>
org.labkey.remoteapi.PostCommand<SelectRowsResponse>
org.labkey.remoteapi.query.ExecuteSqlCommand
All Implemented Interfaces:
HasRequiredVersion, BaseSelect
public class ExecuteSqlCommand extends PostCommand<SelectRowsResponse> implements BaseSelect
Command for executing arbitrary LabKey SQL.

LabKey SQL is variant of standard SQL that supports most of the SELECT-related operations. For more information on LabKey SQL, see the following page:

https://www.labkey.org/Documentation/wiki-page.view?name=labkeySql

The response of this command is exactly the same as the SelectRowsCommand, so the response object will be of type SelectRowsResponse.

  • Constructor Details

    • ExecuteSqlCommand

      public ExecuteSqlCommand(String schemaName)
      Constructs an ExecuteSqlCommand, initialized with a schema name.

      When using this constructor, you must call the setSql(String) method before executing the command.

      Parameters:
      schemaName - The schema name to query.

    • ExecuteSqlCommand

      public ExecuteSqlCommand(String schemaName, String sql)
      Constructs an ExecuteSqlCommand, initialized with a schema name and SQL query.
      Parameters:
      schemaName - The schema name ot query.
      sql - The SQL query.

  • Method Details

    • setExtendedFormat

      public void setExtendedFormat(boolean extendedFormat)
      Set to true to request the extended response format, which includes URLs, Missing-Value indicators and display values in addition to the raw values. Use the getRowset() method on the response to iterate over the returned rows in a format-neutral manner.
      Parameters:
      extendedFormat - True to get the extended format.

    • isExtendedFormat

      public boolean isExtendedFormat()
      Returns whether the extended format will be requested. See setExtendedFormat() for details.
      Returns:
      true if extended format will be requested.

    • getSchemaName

      public String getSchemaName()
      Returns the current schema name.
      Returns:
      The current schema name.

    • setSchemaName

      public void setSchemaName(String schemaName)
      Sets the current schema name.
      Parameters:
      schemaName - The new schema name to query.

    • getSql

      public String getSql()
      Returns the current SQL query.
      Returns:
      The current SQL query.

    • setSql

      public void setSql(String sql)
      Sets the SQL query to execute.
      Parameters:
      sql - The new SQL query.

    • getMaxRows

      public int getMaxRows()
      Returns the current row limit value. Defaults to -1, meaning return all rows.
      Specified by:
      getMaxRows in interface BaseSelect
      Returns:
      The current row limit value.

    • setMaxRows

      public void setMaxRows(int maxRows)
      Sets the current row limit value. If this is set to a positive value, only the first maxRows rows will be returned from the server.
      Specified by:
      setMaxRows in interface BaseSelect
      Parameters:
      maxRows - The maximim number of rows to return, or -1 to get all rows (default).

    • getOffset

      public int getOffset()
      Returns the index of the first row in the resultset to return (defaults to 0).
      Specified by:
      getOffset in interface BaseSelect
      Returns:
      The current offset index.

    • setOffset

      public void setOffset(int offset)
      Sets the index of the first row in the resultset to return from the server. Use this in conjunction with setMaxRows(int) to return pages of rows at a time from the server.
      Specified by:
      setOffset in interface BaseSelect
      Parameters:
      offset - The current offset index.

    • isIncludeTotalCount

      public boolean isIncludeTotalCount()
      Include the total number of rows available (defaults to true). If false totalCount will equal number of rows returned (equal to maxRows unless maxRows == 0).
      Returns:
      indication of whether total count should be included or not

    • setIncludeTotalCount

      public void setIncludeTotalCount(boolean includeTotalCount)
      Include the total number of rows available (defaults to true). If false totalCount will equal number of rows returned (equal to maxRows unless maxRows == 0).
      Parameters:
      includeTotalCount - setting for whether to include the total count

    • getSorts

      public List<Sort> getSorts()
      A sort specification to apply over the rows returned by the SQL. In general, you should either include an ORDER BY clause in your SQL, or specific a sort specification in this config property, but not both. The value of this property should be a comma-delimited list of column names you want to sort by. Use a - prefix to sort a column in descending order (e.g., 'LastName,-Age' to sort first by LastName, then by Age descending).
      Returns:
      the list of sorts to apply

    • setSort

      public void setSort(List<Sort> sorts)
      A sort specification to apply over the rows returned by the SQL. In general, you should either include an ORDER BY clause in your SQL, or specific a sort specification in this config property, but not both. The value of this property should be a comma-delimited list of column names you want to sort by. Use a - prefix to sort a column in descending order (e.g., 'LastName,-Age' to sort first by LastName, then by Age descending).
      Parameters:
      sorts - the sort specifications to apply to the query

    • isSaveInSession

      public boolean isSaveInSession()
      Whether the definition of this query should be stored for reuse during the current session. If true, all information required to recreate the query will be stored on the server and a unique query name will be passed to the success callback. This temporary query name can be used by all other API methods, including Query Web Part creation, for as long as the current user's session remains active.
      Returns:
      whether to save the query definition in session or not

    • setSaveInSession

      public void setSaveInSession(boolean saveInSession)
      Whether the definition of this query should be stored for reuse during the current session. If true, all information required to recreate the query will be stored on the server and a unique query name will be passed to the success callback. This temporary query name can be used by all other API methods, including Query Web Part creation, for as long as the current user's session remains active.
      Parameters:
      saveInSession - indication of whether to save in session or not

    • isIncludeDetailsColumn

      public boolean isIncludeDetailsColumn()
      Include the Details link column in the set of columns (defaults to false). If included, the column will have the name "~~Details~~". The underlying table/query must support details links or the column will be omitted in the response.
      Returns:
      whether to include the details column in the set of columns

    • setIncludeDetailsColumn

      public void setIncludeDetailsColumn(boolean includeDetailsColumn)
      Include the Details link column in the set of columns (defaults to false). If included, the column will have the name "~~Details~~". The underlying table/query must support details links or the column will be omitted in the response.
      Parameters:
      includeDetailsColumn - indication of whether to include the details column or not

    • getQueryParameters

      public Map<String,String> getQueryParameters()
      Map of name (string)/value pairs for the values of parameters if the SQL references underlying queries that are parameterized.
      Returns:
      map of query parameters for the SQL references

    • setQueryParameters

      public void setQueryParameters(Map<String,String> parameters)
      Map of name (string)/value pairs for the values of parameters if the SQL references underlying queries that are parameterized.
      Parameters:
      parameters - a map of the named parameters to use in the underlying parameterized queries

    • getContainerFilter

      public ContainerFilter getContainerFilter()
      Returns the container filter set for this command
      Specified by:
      getContainerFilter in interface BaseSelect
      Returns:
      the container filter (may be null)

    • setContainerFilter

      public void setContainerFilter(ContainerFilter containerFilter)
      Sets the container filter for the sql to be executed. This will cause the query to be executed over more than one container.
      Specified by:
      setContainerFilter in interface BaseSelect
      Parameters:
      containerFilter - the filter to apply to the query (may be null)

    • getWafEncoding

      public boolean getWafEncoding()

    • setWafEncoding

      public void setWafEncoding(boolean wafEncoding)
      By default, this command encodes the SQL parameter to allow it to pass through web application firewalls. This is compatible with LabKey Server v23.9.0 and above. If targeting an earlier server, pass false to this method.

    • createResponse

      protected SelectRowsResponse createResponse(String text, int status, String contentType, org.json.JSONObject json)
      Description copied from class: Command
      Creates an instance of the response class, initialized with the response text, the HTTP status code, and parsed JSONObject.

      Override this method to create an instance of a different class that extends CommandResponse

      Overrides:
      createResponse in class Command<SelectRowsResponse,org.apache.hc.client5.http.classic.methods.HttpPost>
      Parameters:
      text - The response text from the server.
      status - The HTTP status code.
      contentType - The Content-Type header value.
      json - The parsed JSONObject (or null if no JSON was returned).
      Returns:
      An instance of the response object.

    • getJsonObject

      public org.json.JSONObject getJsonObject()
      Description copied from class: PostCommand
      Returns the JSON object to post or null for no JSON. Override this method to provide parameters as JSON.
      Overrides:
      getJsonObject in class PostCommand<SelectRowsResponse>
      Returns:
      The JSON object to post.

    • createParameterMap

      protected Map<String,Object> createParameterMap()
      Description copied from class: Command
      Returns a new, mutable parameter map. Derived classes will typically override this method to put values passed to specialized setter methods into the map.
      Overrides:
      createParameterMap in class Command<SelectRowsResponse,org.apache.hc.client5.http.classic.methods.HttpPost>
      Returns:
      The parameter map to use when building the URL.