org.labkey.remoteapi.query

## Class ExecuteSqlCommand

- java.lang.Object
- - org.labkey.remoteapi.Command<ResponseType>
  - - org.labkey.remoteapi.PostCommand<SelectRowsResponse>
    - - org.labkey.remoteapi.query.ExecuteSqlCommand

- All Implemented Interfaces:
    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/wiki/home/Documentation/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`.

- - ### Nested Class Summary

    - ### Nested classes/interfaces inherited from class org.labkey.remoteapi.Command

      `Command.CommonParameters, Command.Response`

  - ### Field Summary

    - ### Fields inherited from class org.labkey.remoteapi.Command

      `CONTENT_TYPE_JSON`

  - ### Constructor Summary

    Constructors
    Constructor and Description
    `ExecuteSqlCommand(ExecuteSqlCommand source)`
    `ExecuteSqlCommand(java.lang.String schemaName)` Constructs an ExceuteSqlCommand, initialized with a schema name.
    `ExecuteSqlCommand(java.lang.String schemaName, java.lang.String sql)` Constructs an ExecuteSqlCommand, initialized with a schema name and SQL query.

  - ### Method Summary

    All Methods Instance Methods Concrete Methods
    Modifier and Type | Method and Description
    --- | ---
    `ExecuteSqlCommand` | `copy()` Returns a copy of this object.
    `protected SelectRowsResponse` | `createResponse(java.lang.String text, int status, java.lang.String contentType, org.json.simple.JSONObject json)` Creates an instance of the response class, initialized with the response text, the HTTP status code, and parsed JSONObject.
    `ContainerFilter` | `getContainerFilter()` Returns the container filter set for this command
    `org.json.simple.JSONObject` | `getJsonObject()` Returns the JSON object to post, or null if the JSON object has not yet been set.
    `int` | `getMaxRows()` Returns the current row limit value.
    `int` | `getOffset()` Returns the index of the first row in the resultset to return (defaults to 0).
    `java.util.Map<java.lang.String,java.lang.Object>` | `getParameters()` Returns the current parameter map, or null if a map has not yet been set.
    `java.util.Map<java.lang.String,java.lang.String>` | `getQueryParameters()` Map of name (string)/value pairs for the values of parameters if the SQL references underlying queries that are parameterized.
    `java.lang.String` | `getSchemaName()` Returns the current schema name.
    `java.util.List<Sort>` | `getSorts()` A sort specification to apply over the rows returned by the SQL.
    `java.lang.String` | `getSql()` Returns the current SQL query.
    `boolean` | `isExtendedFormat()` Returns whether the extended format will be requested.
    `boolean` | `isIncludeDetailsColumn()` Include the Details link column in the set of columns (defaults to false).
    `boolean` | `isIncludeTotalCount()` Include the total number of rows available (defaults to true).
    `boolean` | `isSaveInSession()` Whether or not the definition of this query should be stored for reuse during the current session.
    `void` | `setContainerFilter(ContainerFilter containerFilter)` Sets the container filter for the sql to be executed.
    `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.
    `void` | `setIncludeDetailsColumn(boolean includeDetailsColumn)` Include the Details link column in the set of columns (defaults to false).
    `void` | `setIncludeTotalCount(boolean includeTotalCount)` Include the total number of rows available (defaults to true).
    `void` | `setMaxRows(int maxRows)` Sets the current row limit value.
    `void` | `setOffset(int offset)` Sets the index of the first row in the resultset to return from the server.
    `void` | `setQueryParameters(java.util.Map<java.lang.String,java.lang.String> parameters)` Map of name (string)/value pairs for the values of parameters if the SQL references underlying queries that are parameterized.
    `void` | `setSaveInSession(boolean saveInSession)` Whether or not the definition of this query should be stored for reuse during the current session.
    `void` | `setSchemaName(java.lang.String schemaName)` Sets the current schema name.
    `void` | `setSort(java.util.List<Sort> sorts)` A sort specification to apply over the rows returned by the SQL.
    `void` | `setSql(java.lang.String sql)` Sets the SQL query to execute.

    - ### Methods inherited from class org.labkey.remoteapi.PostCommand

      `createRequest, setJsonObject`

    - ### Methods inherited from class org.labkey.remoteapi.Command

      `_execute, execute, getActionName, getActionUrl, getControllerName, getHttpRequest, getParamValueAsString, getQueryString, getRequiredVersion, getTimeout, setParameters, setRequiredVersion, setTimeout`

    - ### Methods inherited from class java.lang.Object

      `clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait`

    - ### Methods inherited from interface org.labkey.remoteapi.query.BaseSelect

      `execute, getRequiredVersion, setRequiredVersion`

- - ### Constructor Detail

    - #### ExecuteSqlCommand

          public ExecuteSqlCommand(java.lang.String schemaName)

      Constructs an ExceuteSqlCommand, 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(ExecuteSqlCommand source)

    - #### ExecuteSqlCommand

          public ExecuteSqlCommand(java.lang.String schemaName,
                                   java.lang.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 Detail

    - #### 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 java.lang.String getSchemaName()

      Returns the current schema name.
      Returns:
        The current schema name.

    - #### setSchemaName

          public void setSchemaName(java.lang.String schemaName)

      Sets the current schema name.
      Parameters:
        `schemaName` - The new schema name to query.

    - #### getSql

          public java.lang.String getSql()

      Returns the current SQL query.
      Returns:
        The current SQL query.

    - #### setSql

          public void setSql(java.lang.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).

    - #### 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).

    - #### getSorts

          public java.util.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).

    - #### setSort

          public void setSort(java.util.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).

    - #### isSaveInSession

          public boolean isSaveInSession()

      Whether or not 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.

    - #### setSaveInSession

          public void setSaveInSession(boolean saveInSession)

      Whether or not 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.

    - #### 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.

    - #### 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.

    - #### getQueryParameters

          public java.util.Map<java.lang.String,java.lang.String> getQueryParameters()

      Map of name (string)/value pairs for the values of parameters if the SQL references underlying queries that are parameterized.

    - #### setQueryParameters

          public void setQueryParameters(java.util.Map<java.lang.String,java.lang.String> parameters)

      Map of name (string)/value pairs for the values of parameters if the SQL references underlying queries that are parameterized.

    - #### 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)

    - #### createResponse

          protected SelectRowsResponse createResponse(java.lang.String text,
                                                      int status,
                                                      java.lang.String contentType,
                                                      org.json.simple.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>`
      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.simple.JSONObject getJsonObject()

      Description copied from class: `PostCommand`
      Returns the JSON object to post, or null if the JSON object has not yet been set. Override this method to provide the JSON object dynamically.
      Overrides:
        `getJsonObject` in class `PostCommand<SelectRowsResponse>`
      Returns:
        The JSON object to post.

    - #### copy

          public ExecuteSqlCommand copy()

      Description copied from class: `Command`
      Returns a copy of this object. Derived classes should override this to copy their own data members
      Overrides:
        `copy` in class `PostCommand<SelectRowsResponse>`
      Returns:
        A copy of this object

    - #### getParameters

          public java.util.Map<java.lang.String,java.lang.Object> getParameters()

      Description copied from class: `Command`
      Returns the current parameter map, or null if a map has not yet been set. Derived classes will typically override this method to provide a parameter map based on data members set by specialized setter methods.
      Overrides:
        `getParameters` in class `Command<SelectRowsResponse>`
      Returns:
        The current parameter map.