Class Index | File Index

Classes


Namespace LABKEY.Query

Query static class to programmatically retrieve, insert, update and delete data from LabKey public queries.

LABKEY.Query.selectRows works for all LabKey public queries. However, LABKEY.Query.updateRows, LABKEY.Query.insertRows and LABKEY.Query.deleteRows are not available for all tables and queries.

Additional Documentation:


Defined in: Query.js.

Namespace Summary
Constructor Attributes Constructor Name and Description
 
Field Summary
Field Attributes Field Name and Description
<static>  
LABKEY.Query.containerFilter
An enumeration of the various container filters available.
Method Summary
Method Attributes Method Name and Description
<static>  
LABKEY.Query.buildQueryParams(schemaName, queryName, filterArray, sort, dataRegionName)
Build and return an object suitable for passing to the Ext.Ajax 'params' configuration property.
<static>  
LABKEY.Query.columnSelectInput(config)
Load the set of columns for a given schema/query into a standard <select> input.
<static>  
LABKEY.Query.deleteQueryView(config)
Delete a query view.
<static>  
LABKEY.Query.deleteRows(config)
Delete rows.
<static>  
LABKEY.Query.executeSql(config)
Execute arbitrary LabKey SQL.
<static>  
LABKEY.Query.exportSql(config)
Execute arbitrary LabKey SQL and export the results to Excel or TSV.
<static>  
LABKEY.Query.getDataViews(config)
Returns a list of reports, views and/or datasets in a container
<static>  
LABKEY.Query.getQueries(config)
Returns the set of queries available in a given schema.
<static>  
LABKEY.Query.getQueryDetails(config)
Returns details about a given query including detailed information about result columns
<static>  
LABKEY.Query.getQueryViews(config)
Returns the set of views available for a given query in a given schema.
<static>  
LABKEY.Query.getSchemas(config)
Returns the set of schemas available in the specified container.
<static>  
LABKEY.Query.getServerDate(config)
Returns the current date/time on the LabKey server.
<static>  
LABKEY.Query.importData(config)
Bulk import data rows into a table.
<static>  
LABKEY.Query.insertRows(config)
Insert rows.
<static>  
LABKEY.Query.querySelectInput(config)
Load the set of queries from this container for a given schema into a standard <select> input.
<static>  
LABKEY.Query.queryViewSelectInput(config)
Load the set of views for a given schema/query into a standard <select> input.
<static>  
LABKEY.Query.saveQueryViews(config)
Creates or updates a custom view or views for a given query in a given schema.
<static>  
LABKEY.Query.saveRows(config)
Save inserts, updates, and/or deletes to potentially multiple tables with a single request.
<static>  
LABKEY.Query.schemaSelectInput(config)
Load the set of user visible schemas from the given container into a standard <select> input element.
<static>  
LABKEY.Query.selectDistinctRows(config)
Select Distinct Rows
<static>  
LABKEY.Query.selectRows(config)
Select rows.
<static>  
LABKEY.Query.sqlDateLiteral(date)
Converts a JavaScript date into a format suitable for using in a LabKey SQL query, does not include time.
<static>  
LABKEY.Query.sqlDateTimeLiteral(date, withMS)
Converts a javascript date into a format suitable for using in a LabKey SQL query, includes time but not milliseconds.
<static>  
LABKEY.Query.sqlStringLiteral(str)
Converts a JavaScript string into a format suitable for using in a LabKey SQL query.
<static>  
LABKEY.Query.updateRows(config)
Update rows.
<static>  
LABKEY.Query.validateQuery(config)
Validates the specified query by ensuring that it parses and executes without an exception.
Namespace Detail
LABKEY.Query
Field Detail
<static> LABKEY.Query.containerFilter
An enumeration of the various container filters available. Note that not all data types and queries can contain that spans multiple containers. In those cases, all values will behave the same as current and show only data in the current container. The options are as follows:
Method Detail
<static> {Object} LABKEY.Query.buildQueryParams(schemaName, queryName, filterArray, sort, dataRegionName)
Build and return an object suitable for passing to the Ext.Ajax 'params' configuration property.
Parameters:
{string} schemaName
Name of a schema defined within the current container. See also: How To Find schemaName, queryName & viewName.
{string} queryName
Name of a query table associated with the chosen schema. See also: How To Find schemaName, queryName & viewName.
{LABKEY.Filter[]} filterArray Optional
Array of objects created by LABKEY.Filter.create.
{string} sort Optional
String description of the sort. It includes the column names listed in the URL of a sorted data region (with an optional minus prefix to indicate descending order). In the case of a multi-column sort, up to three column names can be included, separated by commas.
{string} dataRegionName Optional, Default: query
Returns:
{Object} Object suitable for passing to the Ext.Ajax 'params' configuration property.

<static> LABKEY.Query.columnSelectInput(config)
Load the set of columns for a given schema/query into a standard <select> input. The config object must define the schemaName and queryName to be used to source the column listing.
Parameters:
config
An object which contains the following configuration properties.
{String} config.renderTo
the id of the <select> input to load the LabKey queries into. Required.
{String} config.schemaName
the name of the schema. Required.
{String} config.queryName
the name of the query. Required.
{String} config.initValue
the initial value to try and set the <select> element value after it loads. Optional.
{String} config.filterFn
a function to call to filter the column set (ex. by data type). Optional.
{String} config.sortFn
a function to call to sort the column set. Optional.

<static> LABKEY.Query.deleteQueryView(config)
Delete a query view.
Parameters:
config
An object that contains the following configuration parameters
{String} config.schemaName
The name of the schema.
{String} config.queryName
the name of the query.
{String} config.viewName Optional
the name of the view. If a viewName is not specified, the default view will be deleted/reverted.
{boolean} config.revert Optional
Optionally, the view can be reverted instead of deleted. Defaults to false.

<static> {Mixed} LABKEY.Query.deleteRows(config)
Delete rows.
Parameters:
{Object} config
An object which contains the following configuration properties.
{String} config.schemaName
Name of a schema defined within the current container. See also: How To Find schemaName, queryName & viewName.
{String} config.queryName
Name of a query table associated with the chosen schema. See also: How To Find schemaName, queryName & viewName.
{Object} config.extraContext Optional
Experimental: Optional extra context object passed into the transformation/validation script environment.
{Function} config.success
Function called when the "deleteRows" function executes successfully. Will be called with the following arguments: the parsed response data (LABKEY.Query.ModifyRowsResults), the XMLHttpRequest object and (optionally) the "options" object (LABKEY.Query.ModifyRowsOptions).
{Function} config.failure Optional
Function called when execution of the "deleteRows" function fails. See LABKEY.Query.selectRows for more information on the parameters passed to this function.
{Array} config.rows
Array of record objects in which each object has a property for each field. The row data array needs to include only the primary key column value, not all columns.
{String} config.containerPath Optional
The container path in which the schema and query name are defined. If not supplied, the current container path will be used.
{Integer} config.timeout Optional
The maximum number of milliseconds to allow for this operation before generating a timeout error (defaults to 30000).
{boolean} config.transacted Optional
Whether all of the deletes should be done in a single transaction, so they all succeed or all fail. Defaults to true
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"
Returns:
{Mixed} In client-side scripts, this method will return a transaction id for the async request that can be used to cancel the request (see Ext.data.Connection.abort). In server-side scripts, this method will return the JSON response object (first parameter of the success or failure callbacks.)
See:
LABKEY.Query.ModifyRowsResults
LABKEY.Query.ModifyRowsOptions

<static> {Mixed} LABKEY.Query.executeSql(config)
Execute arbitrary LabKey SQL. For more information, see the LabKey SQL Reference.
Example, from the Reagent Request Confirmation Tutorial and Demo: 
         // This snippet extracts a table of UserID, TotalRequests and
         // TotalQuantity from the "Reagent Requests" list.
         // Upon success, the writeTotals function (not included here) uses the
         // returned data object to display total requests and total quantities.

             LABKEY.Query.executeSql({
                     containerPath: 'home/Study/demo/guestaccess',
                     schemaName: 'lists',
                     sql: 'SELECT "Reagent Requests".UserID AS UserID, \
                         Count("Reagent Requests".UserID) AS TotalRequests, \
                         Sum("Reagent Requests".Quantity) AS TotalQuantity \
                         FROM "Reagent Requests" Group BY "Reagent Requests".UserID',
                     success: writeTotals
             });  
Parameters:
config
An object which contains the following configuration properties.
{String} config.schemaName
name of the schema to query.
{String} config.sql
The LabKey SQL to execute.
{String} config.containerPath Optional
The path to the container in which the schema and query are defined, if different than the current container. If not supplied, the current container's path will be used.
{String} config.containerFilter Optional
One of the values of LABKEY.Query.containerFilter that sets the scope of this query. Defaults to containerFilter.current, and is interpreted relative to config.containerPath.
{Function} config.success
Function called when the "selectRows" function executes successfully. This function will be called with the following arguments:
  • data: If config.requiredVersion is not set, or set to "8.3", the success handler will be passed a LABKEY.Query.SelectRowsResults object. If set to "9.1" the success handler will be passed a LABKEY.Query.ExtendedSelectRowsResults object. If requiredVersion is greater than 13.2 the success handler will be passed a LABKEY.Query.Response object.
  • responseObj: The XMLHttpResponseObject instance used to make the AJAX request
  • options: The options used for the AJAX request
{Function} config.failure Optional
Function called when execution of the "executeSql" function fails. See LABKEY.Query.selectRows for more information on the parameters passed to this function.
{Integer} config.maxRows Optional
The maximum number of rows to return from the server (defaults to returning 100,000 rows).
{Integer} config.offset Optional
The index of the first row to return from the server (defaults to 0). Use this along with the maxRows config property to request pages of data.
{Boolean} config.includeTotalCount Optional
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).
{String} config.sort Optional
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).
{Boolean} config.saveInSession Optional
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.
{Boolean} config.includeDetailsColumn Optional
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.
{Object} config.parameters Optional
Map of name (string)/value pairs for the values of parameters if the SQL references underlying queries that are parameterized. For example, the following passes two parameters to the query: {'Gender': 'M', 'CD4': '400'}. The parameters are written to the request URL as follows: query.param.Gender=M&query.param.CD4=400. For details on parameterized SQL queries, see Parameterized SQL Queries.
{Double} config.requiredVersion Optional
If not set, or set to "8.3", the success handler will be passed a LABKEY.Query.SelectRowsResults object. If set to "9.1" the success handler will be passed a LABKEY.Query.ExtendedSelectRowsResults object. If greater than 13.2 the success handler will be passed a LABKEY.Query.Response object. The main difference between SelectRowsResults and ExtendedSelectRowsResults is that each column in each row will be another object (not just a scalar value) with a "value" property as well as other related properties (url, mvValue, mvIndicator, etc.). In the LABKEY.Query.Response format each row will be an instance of LABKEY.Query.Row. In the "16.2" format, multi-value columns will be returned as an array of values, each of which may have a value, displayValue, and url. In the "17.1" format, "formattedValue" may be included in the response as the display column's value formatted with the display column's format or folder format settings.
{Integer} config.timeout Optional
The maximum number of milliseconds to allow for this operation before generating a timeout error (defaults to 30000).
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"
Returns:
{Mixed} In client-side scripts, this method will return a transaction id for the async request that can be used to cancel the request (see Ext.data.Connection.abort). In server-side scripts, this method will return the JSON response object (first parameter of the success or failure callbacks.)
See:
LABKEY.Query.SelectRowsOptions
LABKEY.Query.SelectRowsResults
LABKEY.Query.ExtendedSelectRowsResults
LABKEY.Query.Response

<static> LABKEY.Query.exportSql(config)
Execute arbitrary LabKey SQL and export the results to Excel or TSV. After this method is called, the user will be prompted to accept a file from the server, and most browsers will allow the user to either save it or open it in an appropriate application. For more information, see the LabKey SQL Reference.
Parameters:
config
An object which contains the following configuration properties.
{String} config.schemaName
name of the schema to query.
{String} config.sql
The LabKey SQL to execute.
{String} config.format Optional
The desired export format. May be either 'excel' or 'tsv'. Defaults to 'excel'.
{String} config.containerPath Optional
The path to the container in which the schema and query are defined, if different than the current container. If not supplied, the current container's path will be used.
{String} config.containerFilter Optional
One of the values of LABKEY.Query.containerFilter that sets the scope of this query. Defaults to containerFilter.current, and is interpreted relative to config.containerPath.

<static> LABKEY.Query.getDataViews(config)
Returns a list of reports, views and/or datasets in a container
Parameters:
config
{String} config.containerPath Optional
A container path in which to execute this command. If not provided, the current container will be used
{Array} config.dataTypes Optional
An array of data types to return, which can be any of: 'reports', 'datasets' or 'queries'. If null, all will be returned
{Function} config.success Optional
A function called on success. It will be passed a single argument with the following properties:
  • data: An array with one element per dataview. Each view is a map with the following properties:
    • access:
    • allowCustomThumbnail: A flag indicating whether the thumbnail can be customized
    • category: The category to which this item has been assigned
    • categoryDisplayOrder: The display order within that category
    • container: The container where this dataView is defined
    • created: The displayName of the user who created the item
    • createdByUserId: The user Id of the user who created the item
    • dataType: The dataType of this item, either queries, reports or datasets
    • detailsUrl: The url that will display additional details about this item
    • icon: The url of the icon for this report
    • id: The unique Id of this item
    • reportId: The unique report Id if this item is a report. Value is null if this item is not a report.
    • modified: The date this item was last modified
    • name: The display name of this item
    • runUrl: The url that can be used to execute this report
    • shared: A flag indicating whether this item is shared
    • thumbnail: The url of this item's thumbnail image
    • type: The display string for the Data Type.
    • visible: A flag indicating whether this report is visible or hidden
  • types: a map of each dataType, and a boolean indicating whether it was included in the results (this is based on the dataTypes param in the config)
{Function} config.failure Optional
A function called when execution of "getDataViews" fails.
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"

<static> {Mixed} LABKEY.Query.getQueries(config)
Returns the set of queries available in a given schema.
Parameters:
config
An object that contains the following configuration parameters
{String} config.schemaName
The name of the schema.
{function} config.success
The function to call when the function finishes successfully. This function will be called with the following parameters:
  • queriesInfo: An object with the following properties
    • schemaName: the name of the requested schema
    • queries: an array of objects, each of which has the following properties
      • name: the name of the query
      • title: this is the label used when displaying this table. If the table has no title, this will be the same as the name.
      • hidden: true if this is a hidden query or table.
      • inherit: true if this query is marked as inheritable in sub-folders.
      • isUserDefined: true if this is a user-defined query
      • canEdit: true if the current user can edit this query
      • isMetadataOverrideable: true if the current user may override the query's metadata
      • moduleName: the module that defines this query
      • isInherited: true if this query is defined in a different container.
      • containerPath: if isInherited, the container path where this query is defined.
      • description: A description for this query (if provided)
      • viewDataUrl: the server-relative URL where this query's data can be viewed. Available in LabKey Server version 10.2 and later.
      • columns: if config.includeColumns is not false, this will contain an array of objects with the following properties
        • name: the name of the column
        • caption: the caption of the column (may be undefined)
        • description: the description of the column (may be undefined)
{function} config.failure Optional
The function to call if this function encounters an error. This function will be called with the following parameters:
  • errorInfo: An object with a property called "exception," which contains the error message.
{Boolean} config.includeUserQueries Optional
If set to false, user-defined queries will not be included in the results. Default is true.
{Boolean} config.includeSystemQueries Optional
If set to false, system-defined queries will not be included in the results. Default is true.
{Boolean} config.includeColumns Optional
If set to false, information about the available columns in this query will not be included in the results. Default is true.
{String} config.containerPath Optional
A container path in which to execute this command. If not supplied, the current container will be used.
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"
Returns:
{Mixed} In client-side scripts, this method will return a transaction id for the async request that can be used to cancel the request (see Ext.data.Connection.abort). In server-side scripts, this method will return the JSON response object (first parameter of the success or failure callbacks.)

<static> {Mixed} LABKEY.Query.getQueryDetails(config)
Returns details about a given query including detailed information about result columns
Parameters:
{Object} config
An object that contains the following configuration parameters
{String} config.schemaName
The name of the schema.
{String} config.queryName
The name of the query.
{String} config.viewName Optional
A view name or Array of view names to include custom view details. Use '*' to include all views for the query.
{String} config.fields Optional
A field key or Array of field keys to include in the metadata.
{Boolean} config.initializeMissingView Optional
Initialize the view based on the default view iff the view doesn't yet exist.
{function} config.success
The function to call when the function finishes successfully. This function will be called with the following parameters:
  • queryInfo: An object with the following properties
    • schemaName: the name of the requested schema
    • name: the name of the requested query
    • isUserDefined: true if this is a user-defined query
    • canEdit: true if the current user can edit this query
    • isMetadataOverrideable: true if the current user may override the query's metadata
    • moduleName: the module that defines this query
    • isInherited: true if this query is defined in a different container.
    • containerPath: if isInherited, the container path where this query is defined.
    • viewDataUrl: The URL to navigate to for viewing the data returned from this query
    • title: If a value has been set, this is the label used when displaying this table
    • description: A description for this query (if provided)
    • columns: Information about all columns in this query. This is an array of LABKEY.Query.FieldMetaData objects.
    • defaultView: An array of column information for the columns in the current user's default view of this query. The shape of each column info is the same as in the columns array.
    • views: An array of view info (XXX: same as views.getQueryViews()
{function} config.failure Optional
The function to call if this function encounters an error. This function will be called with the following parameters:
  • errorInfo: An object with a property called "exception," which contains the error message.
{String} config.containerPath Optional
A container path in which to execute this command. If not supplied, the current container will be used.
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"
Returns:
{Mixed} In client-side scripts, this method will return a transaction id for the async request that can be used to cancel the request (see Ext.data.Connection.abort). In server-side scripts, this method will return the JSON response object (first parameter of the success or failure callbacks.)
See:
LABKEY.Query.FieldMetaData

<static> {Mixed} LABKEY.Query.getQueryViews(config)
Returns the set of views available for a given query in a given schema.
Parameters:
config
An object that contains the following configuration parameters
{String} config.schemaName
The name of the schema.
{String} config.queryName
the name of the query.
{String} config.viewName Optional
A view name (empty string for the default view), otherwise return all views for the query.
{Boolean} config.metadata Optional
Include view column field metadata.
{function} config.success
The function to call when the function finishes successfully. This function will be called with the following parameters:
  • viewsInfo: An object with the following properties
    • schemaName: the name of the requested schema
    • queryName: the name of the requested query
    • views: an array of objects, each of which has the following properties
      • name: the name of the view (default view's name is empty string)
      • label: the label of the view
      • default: true if this is the default view info
      • viewDataUrl: the server-relative URL where this view's data can be viewed. Available in LabKey Server version 10.2 and later.
      • columns: this will contain an array of objects with the following properties
        • name: the name of the column
        • fieldKey: the field key for the column (may include join column names, e.g. 'State/Population')
      • filter: TBD Available in LabKey Server version 10.3 and later.
      • sort: TBD Available in LabKey Server version 10.3 and later.
      • fields: TBD if metadata Available in LabKey Server version 10.3 and later.
{function} config.failure Optional
The function to call if this function encounters an error. This function will be called with the following parameters:
  • errorInfo: An object with a property called "exception," which contains the error message.
{String} config.containerPath Optional
A container path in which to execute this command. If not supplied, the current container will be used.
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"
Returns:
{Mixed} In client-side scripts, this method will return a transaction id for the async request that can be used to cancel the request (see Ext.data.Connection.abort). In server-side scripts, this method will return the JSON response object (first parameter of the success or failure callbacks.)

<static> {Mixed} LABKEY.Query.getSchemas(config)
Returns the set of schemas available in the specified container.
Parameters:
config
An object that contains the following configuration parameters
{String} config.schemaName
Get schemas under the given schemaName.
{String} config.apiVersion
Version of the API. Changed the structure of the server's response.
{String} config.includeHidden
Default true.
{function} config.success
The function to call when the function finishes successfully. This function will be called with the following parameters:
  • schemasInfo: An object with a property called "schemas". If no apiVersion is specified, or it is less than 9.3, it will be an array of schema names. If apiVersion is 9.3 or greater, it will be a map where the keys are schemaNames and the values are objects with the following properties:
    • schemaName: the short name of the schema
    • fullyQualifiedName: the fully qualified name of the schema, encoded as a string with "." separators as described in LABKEY.SchemaKey
    • description: a short description of the schema
    • schemas: a map of child schemas, with values in the same structure as this object
{function} config.failure Optional
The function to call if this function encounters an error. This function will be called with the following parameters:
  • errorInfo: An object with a property called "exception," which contains the error message.
{String} config.containerPath Optional
A container path in which to execute this command. If not supplied, the current container will be used.
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"
Returns:
{Mixed} In client-side scripts, this method will return a transaction id for the async request that can be used to cancel the request (see Ext.data.Connection.abort). In server-side scripts, this method will return the JSON response object (first parameter of the success or failure callbacks.)

<static> {Mixed} LABKEY.Query.getServerDate(config)
Returns the current date/time on the LabKey server.
Parameters:
config
An object that contains the following configuration parameters
{function} config.success
The function to call when the function finishes successfully. This function will be called with a single parameter of type Date.
{function} config.failure Optional
The function to call if this function encounters an error. This function will be called with the following parameters:
  • errorInfo: An object with a property called "exception," which contains the error message.
Returns:
{Mixed} In client-side scripts, this method will return a transaction id for the async request that can be used to cancel the request (see Ext.data.Connection.abort). In server-side scripts, this method will return the JSON response object (first parameter of the success or failure callbacks.)

<static> {Mixed} LABKEY.Query.importData(config)
Bulk import data rows into a table. One of 'text', 'path', 'moduleResource', or 'file' is required and cannot be combined.
Example, importing tsv data from a module: 
 LABKEY.Query.importData({
             schemaName: 'lists',
             queryName: 'People',
             // reference to <input type='file' id='file'>
             file: document.getElementById('file')
         },
 });
Example, importing tsv data from a module: 
 LABKEY.Query.importData({
             schemaName: 'lists',
             queryName: 'People',
             module: 'mymodule',
             moduleResource: '/data/lists/People.tsv'
         },
 });
Parameters:
{Object} config
An object which contains the following configuration properties.
{String} config.schemaName
Name of a schema defined within the current container.
{String} config.queryName
Name of a query table associated with the chosen schema.
{File} config.file Optional
A File object or a file input element to upload to the server.
{String} config.text Optional
Text to import.
{String} config.path Optional
Path to resource under webdav tree. E.g. "/_webdav/MyProject/@files/data.tsv"
{String} config.module Optional
Module name to use when resolving a module resource.
{String} config.moduleResource Optional
A file resource within the module to import.
{String} config.importIdentity Optional
When true, auto-increment key columns may be imported from the data.
{String} config.importLookupByAlternateKey Optional
When true, lookup columns can be imported by their alternate keys instead of the primary key. For example, if a column is a lookup to a SampleSet, the imported value can be the Sample's name since names must be unique within a SampleSet.
{Function} config.success Optional
Function called when the "importData" function executes successfully. Will be called with the following arguments: An object containing success and rowCount properties.
{Function} config.failure Optional
Function called importing data fails.
{String} config.containerPath Optional
The container path in which the schema and query name are defined.
{Integer} config.timeout Optional
The maximum number of milliseconds to allow for this operation before generating a timeout error (defaults to 30000).
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"
Returns:
{Mixed} In client-side scripts, this method will return a transaction id for the async request that can be used to cancel the request (see Ext.data.Connection.abort). In server-side scripts, this method will return the JSON response object (first parameter of the success or failure callbacks.)

<static> {Mixed} LABKEY.Query.insertRows(config)
Insert rows.
Example, from the Reagent Request Tutorial and Demo: 
         // This snippet inserts data from the ReagentReqForm into a list.
         // Upon success, it moves the user to the confirmation page and
         // passes the current user's ID to that page.
         LABKEY.Query.insertRows({
             containerPath: '/home/Study/demo/guestaccess',
             schemaName: 'lists',
             queryName: 'Reagent Requests',
             rows: [{
                "Name":  ReagentReqForm.DisplayName.value,
                "Email": ReagentReqForm.Email.value,
                "UserID": ReagentReqForm.UserID.value,
                "Reagent": ReagentReqForm.Reagent.value,
                "Quantity": parseInt(ReagentReqForm.Quantity.value),
                "Date": new Date(),
                "Comments": ReagentReqForm.Comments.value,
                "Fulfilled": 'false'
             }],
             successCallback: function(data){
                 window.location =
                    '/home/Study/demo/wiki-page.view?name=confirmation&userid='
                    + LABKEY.Security.currentUser.id;
             },
         });  
Parameters:
{Object} config
An object which contains the following configuration properties.
{String} config.schemaName
Name of a schema defined within the current container. See also: How To Find schemaName, queryName & viewName.
{String} config.queryName
Name of a query table associated with the chosen schema. See also: How To Find schemaName, queryName & viewName.
{Array} config.rows
Array of record objects in which each object has a property for each field. The row data array must include all column values except for the primary key column. However, you will need to include the primary key column values if you defined them yourself instead of relying on auto-number.
{Object} config.extraContext Optional
Experimental: Optional extra context object passed into the transformation/validation script environment.
{Function} config.success
Function called when the "insertRows" function executes successfully. Will be called with the following arguments: the parsed response data (LABKEY.Query.ModifyRowsResults), the XMLHttpRequest object and (optionally) the "options" object (LABKEY.Query.ModifyRowsOptions).
{Function} config.failure Optional
Function called when execution of the "insertRows" function fails. See LABKEY.Query.selectRows for more information on the parameters passed to this function.
{String} config.containerPath Optional
The container path in which the schema and query name are defined. If not supplied, the current container path will be used.
{Integer} config.timeout Optional
The maximum number of milliseconds to allow for this operation before generating a timeout error (defaults to 30000).
{boolean} config.transacted Optional
Whether all of the inserts should be done in a single transaction, so they all succeed or all fail. Defaults to true
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"
Returns:
{Mixed} In client-side scripts, this method will return a transaction id for the async request that can be used to cancel the request (see Ext.data.Connection.abort). In server-side scripts, this method will return the JSON response object (first parameter of the success or failure callbacks.)
See:
LABKEY.Query.ModifyRowsResults
LABKEY.Query.ModifyRowsOptions

<static> LABKEY.Query.querySelectInput(config)
Load the set of queries from this container for a given schema into a standard <select> input. The config object must define which <select> input is for the schemas and which <select> input is for the queries. This function also then associates the two <select> inputs so that a selection change in the schema input will update the query input accordingly.
Parameters:
config
An object which contains the following configuration properties.
{String} config.renderTo
the id of the <select> input to load the LabKey queries into.
{String} config.schemaInputId
the id of the <select> input to load the LabKey schemas into.
{String} config.initValue
the initial value to try and set the <select> element value after it loads.

<static> LABKEY.Query.queryViewSelectInput(config)
Load the set of views for a given schema/query into a standard <select> input. The config object must define which <select> input is for the schemas and which <select> input is for the queries. This function also then associates the two <select> inputs so that a selection change in the query input will update the views input accordingly.
Parameters:
config
An object which contains the following configuration properties.
{String} config.renderTo
the id of the <select> input to load the LabKey query views into. Required.
{String} config.schemaInputId
the name of the <select> input to pull the LabKey schema from.
{String} config.queryInputId
the name of the <select> input to load the LabKey query from.
{String} config.initValue
the initial value to try and set the <select> element value after it loads. Optional.

<static> {Mixed} LABKEY.Query.saveQueryViews(config)
Creates or updates a custom view or views for a given query in a given schema. The config object matches the viewInfos parameter of the getQueryViews.successCallback.
Parameters:
config
An object that contains the following configuration parameters
{String} config.schemaName
The name of the schema.
{String} config.queryName
The name of the query.
{String} config.views
The updated view definitions.
{function} config.success
The function to call when the function finishes successfully. This function will be called with the same parameters as getQueryViews.successCallback.
{function} config.failure Optional
The function to call if this function encounters an error. This function will be called with the following parameters:
  • errorInfo: An object with a property called "exception," which contains the error message.
{String} config.containerPath Optional
A container path in which to execute this command. If not supplied, the current container will be used.
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"
Returns:
{Mixed} In client-side scripts, this method will return a transaction id for the async request that can be used to cancel the request (see Ext.data.Connection.abort). In server-side scripts, this method will return the JSON response object (first parameter of the success or failure callbacks.)

<static> {Mixed} LABKEY.Query.saveRows(config)
Save inserts, updates, and/or deletes to potentially multiple tables with a single request.
Parameters:
{Object} config
An object which contains the following configuration properties.
{Array} config.commands
An array of all of the update/insert/delete operations to be performed. Each command has the following structure:
{String} config.commands[].schemaName
Name of a schema defined within the current container. See also: How To Find schemaName, queryName & viewName.
{String} config.commands[].queryName
Name of a query table associated with the chosen schema. See also: How To Find schemaName, queryName & viewName.
{String} config.commands[].command
Name of the command to be performed. Must be one of "insert", "update", or "delete".
{Array} config.commands[].rows
An array of data for each row to be changed. See LABKEY.Query.insertRows, LABKEY.Query.updateRows, or LABKEY.Query.deleteRows for requirements of what data must be included for each row.
{Object} config.commands[].extraContext Optional
Experimental: Optional extra context object passed into the transformation/validation script environment.
{Object} config.extraContext Optional
Experimental: Optional extra context object passed into the transformation/validation script environment. The extraContext at the command-level will be merged with the extraContext at the top-level of the config.
{Function} config.success
Function called when the "saveRows" function executes successfully. Called with arguments:
  • an object with the following properties:
    • result: an array of parsed response data (LABKEY.Query.ModifyRowsResults) (one for each command in the request)
    • errorCount: an integer, with the total number of errors encountered during the operation
    • committed: a boolean, indicating if the changes were actually committed to the database
  • the XMLHttpRequest object
  • (optionally) the "options" object (LABKEY.Query.ModifyRowsOptions)
{Function} config.failure Optional
Function called if execution of the "saveRows" function fails. See LABKEY.Query.selectRows for more information on the parameters passed to this function.
{String} config.containerPath Optional
The container path in which the changes are to be performed. If not supplied, the current container path will be used.
{Integer} config.timeout Optional
The maximum number of milliseconds to allow for this operation before generating a timeout error (defaults to 30000).
{Double} config.apiVersion Optional
Version of the API. If this is 13.2 or higher, a request that fails validation will be returned as a successful response. Use the 'errorCount' and 'committed' properties in the response to tell if it committed or not. If this is 13.1 or lower (or unspecified), the failure callback will be invoked instead in the event of a validation failure.
{boolean} config.transacted Optional
Whether all of the row changes for all of the tables should be done in a single transaction, so they all succeed or all fail. Defaults to true
{boolean} config.validateOnly Optional
Whether or not the server should attempt proceed through all of the commands, but not actually commit them to the database. Useful for scenarios like giving incremental validation feedback as a user fills out a UI form, but not actually save anything until they explicitly request a save.
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"
Returns:
{Mixed} In client-side scripts, this method will return a transaction id for the async request that can be used to cancel the request (see Ext.data.Connection.abort). In server-side scripts, this method will return the JSON response object (first parameter of the success or failure callbacks.)
See:
LABKEY.Query.ModifyRowsResults
LABKEY.Query.ModifyRowsOptions

<static> LABKEY.Query.schemaSelectInput(config)
Load the set of user visible schemas from the given container into a standard <select> input element.
Parameters:
config
An object which contains the following configuration properties.
{String} config.renderTo
the id of the <select> input to load the LabKey queries into.
{String} config.initValue
the initial value to try and set the <select> element value after it loads.

<static> LABKEY.Query.selectDistinctRows(config)
Select Distinct Rows
Parameters:
{Object} config
An object which contains the following configuration properties.
{String} config.schemaName
Name of a schema defined within the current container. See also: How To Find schemaName, queryName & viewName.
{String} config.queryName
Name of a query table associated with the chosen schema. See also: How To Find schemaName, queryName & viewName.
{String} config.column
A single column for which the distinct results will be requested. This column must exist within the specified query.
{String} config.containerFilter Optional
One of the values of LABKEY.Query.containerFilter that sets the scope of this query. Defaults to containerFilter.current, and is interpreted relative to config.containerPath.
{Object} config.parameters Optional
Map of name (string)/value pairs for the values of parameters if the SQL references underlying queries that are parameterized. For example, the following passes two parameters to the query: {'Gender': 'M', 'CD4': '400'}. The parameters are written to the request URL as follows: query.param.Gender=M&query.param.CD4=400. For details on parameterized SQL queries, see Parameterized SQL Queries.
{Array} config.filterArray Optional
Array of objects created by LABKEY.Filter.create.
{String} config.viewName Optional
Name of a view to use. This is potentially important if this view contains filters on the data.
{Function} config.success
{Function} config.failure
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"

<static> {Mixed} LABKEY.Query.selectRows(config)
Select rows.
Example: 
<script type="text/javascript">
	function onFailure(errorInfo, options, responseObj)
	{
	    if (errorInfo && errorInfo.exception)
	        alert("Failure: " + errorInfo.exception);
	    else
	        alert("Failure: " + responseObj.statusText);
	}

	function onSuccess(data)
	{
	    alert("Success! " + data.rowCount + " rows returned.");
	}

	LABKEY.Query.selectRows({
            schemaName: 'lists',
            queryName: 'People',
            columns: ['Name', 'Age'],
            success: onSuccess,
            failure: onFailure
        });
</script> 
Parameters:
{Object} config
An object which contains the following configuration properties.
{String} config.schemaName
Name of a schema defined within the current container. See also: How To Find schemaName, queryName & viewName.
{String} config.queryName
Name of a query table associated with the chosen schema. See also: How To Find schemaName, queryName & viewName.
{Function} config.success
Function called when the "selectRows" function executes successfully. This function will be called with the following arguments:
  • data: If config.requiredVersion is not set, or set to "8.3", the success handler will be passed a LABKEY.Query.SelectRowsResults object. If set to "9.1" the success handler will be passed a LABKEY.Query.ExtendedSelectRowsResults object. If greater than "13.2" the success handler will be passed a LABKEY.Query.Response object.
  • responseObj: The XMLHttpResponseObject instance used to make the AJAX request
  • options: The options used for the AJAX request
{Function} config.failure Optional
Function called when execution of the "selectRows" function fails. This function will be called with the following arguments:
  • errorInfo: an object describing the error with the following fields:
    • exception: the exception message
    • exceptionClass: the Java class of the exception thrown on the server
    • stackTrace: the Java stack trace at the point when the exception occurred
  • responseObj: the XMLHttpResponseObject instance used to make the AJAX request
  • options: the options used for the AJAX request
{Array} config.filterArray Optional
Array of objects created by LABKEY.Filter.create.
{Object} config.parameters Optional
Map of name (string)/value pairs for the values of parameters if the SQL references underlying queries that are parameterized. For example, the following passes two parameters to the query: {'Gender': 'M', 'CD4': '400'}. The parameters are written to the request URL as follows: query.param.Gender=M&query.param.CD4=400. For details on parameterized SQL queries, see Parameterized SQL Queries.
{String} config.sort Optional
String description of the sort. It includes the column names listed in the URL of a sorted data region (with an optional minus prefix to indicate descending order). In the case of a multi-column sort, up to three column names can be included, separated by commas.
{String} config.viewName Optional
The name of a custom view saved on the server for the specified query.
{String} config.columns Optional
An Array of columns or a comma-delimited list of column names you wish to select from the specified query. By default, selectRows will return the set of columns defined in the default value for this query, as defined via the Customize View user interface on the server. You can override this by specifying a list of column names in this parameter, separated by commas. The names can also include references to related tables (e.g., 'RelatedPeptide/Peptide' where 'RelatedPeptide is the name of a foreign key column in the base query, and 'Peptide' is the name of a column in the related table).
{String} config.containerPath Optional
The path to the container in which the schema and query are defined, if different than the current container. If not supplied, the current container's path will be used.
{String} config.containerFilter Optional
One of the values of LABKEY.Query.containerFilter that sets the scope of this query. Defaults to containerFilter.current, and is interpreted relative to config.containerPath.
{String} config.showRows Optional
Either 'paginated' (the default) 'selected', 'unselected', 'all', or 'none'. When 'paginated', the maxRows and offset parameters can be used to page through the query's result set rows. When 'selected' or 'unselected' the set of rows selected or unselected by the user in the grid view will be returned. You can programatically get and set the selection using the LABKEY.DataRegion.setSelected APIs. Setting config.maxRows to -1 is the same as 'all' and setting config.maxRows to 0 is the same as 'none'.
{Integer} config.maxRows Optional
The maximum number of rows to return from the server (defaults to 100000). If you want to return all possible rows, set this config property to -1.
{Integer} config.offset Optional
The index of the first row to return from the server (defaults to 0). Use this along with the maxRows config property to request pages of data.
{Boolean} config.includeTotalCount Optional
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).
{Boolean} config.includeDetailsColumn Optional
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.
{Boolean} config.includeUpdateColumn Optional
Include the Update (or edit) link column in the set of columns (defaults to false). If included, the column will have the name "~~Update~~". The underlying table/query must support update links or the column will be omitted in the response.
{String} config.selectionKey Optional
Unique string used by selection APIs as a key when storing or retrieving the selected items for a grid. Not used unless config.showRows is 'selected' or 'unselected'.
{Boolean} config.ignoreFilter Optional
If true, the command will ignore any filter that may be part of the chosen view.
{Integer} config.timeout Optional
The maximum number of milliseconds to allow for this operation before generating a timeout error (defaults to 30000).
{Double} config.requiredVersion Optional
If not set, or set to "8.3", the success handler will be passed a LABKEY.Query.SelectRowsResults object. If set to "9.1" the success handler will be passed a LABKEY.Query.ExtendedSelectRowsResults object. If greater than "13.2" the success handler will be passed a LABKEY.Query.Response object. The main difference between SelectRowsResults and ExtendedSelectRowsResults is that each column in each row will be another object (not just a scalar value) with a "value" property as well as other related properties (url, mvValue, mvIndicator, etc.). In the LABKEY.Query.Response format each row will an instance of LABKEY.Query.Row. In the "16.2" format, multi-value columns will be returned as an array of values, each of which may have a value, displayValue, and url. In the "17.1" format, "formattedValue" may be included in the response as the display column's value formatted with the display column's format or folder format settings.
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"
Returns:
{Mixed} In client-side scripts, this method will return a transaction id for the async request that can be used to cancel the request (see Ext.data.Connection.abort). In server-side scripts, this method will return the JSON response object (first parameter of the success or failure callbacks.)
See:
LABKEY.Query.SelectRowsOptions
LABKEY.Query.SelectRowsResults
LABKEY.Query.ExtendedSelectRowsResults
LABKEY.Query.Response

<static> {String} LABKEY.Query.sqlDateLiteral(date)
Converts a JavaScript date into a format suitable for using in a LabKey SQL query, does not include time.
Parameters:
{Date} date
JavaScript date
Returns:
{String} a date literal formatted to be used in a LabKey query

<static> {String} LABKEY.Query.sqlDateTimeLiteral(date, withMS)
Converts a javascript date into a format suitable for using in a LabKey SQL query, includes time but not milliseconds.
Parameters:
{Date} date
JavaScript date
{Boolean} withMS
include milliseconds
Returns:
{String} a date and time literal formatted to be used in a LabKey query

<static> {string} LABKEY.Query.sqlStringLiteral(str)
Converts a JavaScript string into a format suitable for using in a LabKey SQL query.
Parameters:
{string} str
String to use in query
Returns:
{string} value formatted for use in a LabKey query. Will properly escape single quote characters.

<static> {Mixed} LABKEY.Query.updateRows(config)
Update rows.
Parameters:
{Object} config
An object which contains the following configuration properties.
{String} config.schemaName
Name of a schema defined within the current container. See also: How To Find schemaName, queryName & viewName.
{String} config.queryName
Name of a query table associated with the chosen schema. See also: How To Find schemaName, queryName & viewName.
{Array} config.rows
Array of record objects in which each object has a property for each field. The row array must include the primary key column values and values for other columns you wish to update.
{Object} config.extraContext Optional
Experimental: Optional extra context object passed into the transformation/validation script environment.
{Function} config.success
Function called when the "updateRows" function executes successfully. Will be called with arguments: the parsed response data (LABKEY.Query.ModifyRowsResults), the XMLHttpRequest object and (optionally) the "options" object (LABKEY.Query.ModifyRowsOptions).
{Function} config.failure Optional
Function called when execution of the "updateRows" function fails. See LABKEY.Query.selectRows for more information on the parameters passed to this function.
{String} config.containerPath Optional
The container path in which the schema and query name are defined. If not supplied, the current container path will be used.
{Integer} config.timeout Optional
The maximum number of milliseconds to allow for this operation before generating a timeout error (defaults to 30000).
{boolean} config.transacted Optional
Whether all of the updates should be done in a single transaction, so they all succeed or all fail. Defaults to true
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"
Returns:
{Mixed} In client-side scripts, this method will return a transaction id for the async request that can be used to cancel the request (see Ext.data.Connection.abort). In server-side scripts, this method will return the JSON response object (first parameter of the success or failure callbacks.)
See:
LABKEY.Query.ModifyRowsResults
LABKEY.Query.ModifyRowsOptions

<static> {Mixed} LABKEY.Query.validateQuery(config)
Validates the specified query by ensuring that it parses and executes without an exception.
Parameters:
config
An object that contains the following configuration parameters
{String} config.schemaName
The name of the schema.
{String} config.queryName
the name of the query.
{Boolean} config.includeAllColumns
If set to false, only the columns in the user's default view of the specific query will be tested (defaults to true).
{Boolean} config.validateQueryMetadata
If true, the query metadata and custom views will also be validated.
{function} config.success
The function to call when the function finishes successfully. This function will be called with a simple object with one property named "valid" set to true.
{function} config.failure Optional
The function to call if this function encounters an error. This function will be called with the following parameters:
  • errorInfo: An object with a property called "exception," which contains the error message. If validateQueryMetadata was used, this will also hae a property called 'errors', which is an array of objects describing each error.
{String} config.containerPath Optional
A container path in which to execute this command. If not supplied, the current container will be used.
{Object} config.scope Optional
A scope for the callback functions. Defaults to "this"
Returns:
{Mixed} In client-side scripts, this method will return a transaction id for the async request that can be used to cancel the request (see Ext.data.Connection.abort). In server-side scripts, this method will return the JSON response object (first parameter of the success or failure callbacks.)

Documentation generated by JsDoc Toolkit 2.3.2 on Wed Mar 27 2019 06:39:43 GMT-0000 (UTC)