Rlabkey (version 3.3.0)

labkey.executeSql: Retrieve data from a LabKey Server using SQL commands

Description

Use Sql commands to specify data to be imported into R. Prior to import, data can be manipulated through standard SQL commands supported in LabKey SQL.

Usage

labkey.executeSql(baseUrl, folderPath, schemaName, sql,
    maxRows = NULL, rowOffset = NULL, colSort=NULL,
    showHidden = FALSE, colNameOpt='caption',
    containerFilter=NULL, parameters=NULL)

Value

The requested data are returned in a data frame with stringsAsFactors set to FALSE. Column names are set as determined by the colNameOpt parameter.

Arguments

baseUrl

a string specifying the baseUrlfor the labkey server

folderPath

a string specifying the folderPath

schemaName

a string specifying the schemaName for the query

sql

a string containing the sql commands to be executed

maxRows

(optional) an integer specifying the maximum number of rows to return. If no value is specified, all rows are returned.

rowOffset

(optional) an integer specifying which row of data should be the first row in the retrieval. If no value is specified, rows will begin at the start of the result set.

colSort

(optional) a string including the name of the column to sort preceeded by a “+” or “-” to indicate sort direction

showHidden

(optional) a logical value indicating whether or not to return data columns that would normally be hidden from user view. Defaults to FALSE if no value provided.

colNameOpt

(optional) controls the name source for the columns of the output dataframe, with valid values of 'caption', 'fieldname', and 'rname' See labkey.selectRows for more details.

containerFilter

(optional) Specifies the containers to include in the scope of selectRows request. A value of NULL is equivalent to "Current". Valid values are

• "Current": Include the current folder only

• "CurrentAndSubfolders": Include the current folder and all subfolders

• "CurrentPlusProject": Include the current folder and the project that contains it

• "CurrentAndParents": Include the current folder and its parent folders

• "CurrentPlusProjectAndShared": Include the current folder plus its project plus any shared folders

• "AllFolders": Include all folders for which the user has read permission

parameters

(optional) List of name/value pairs for the parameters if the SQL references underlying queries that are parameterized. For example, parameters=c("X=1", "Y=2").

Author

Valerie Obenchain

Details

A full dataset or any portion of a dataset can be imported into an R data frame using the labkey.executeSql function. Function arguments are components of the url that identify the location of the data and the SQL actions that should be taken on the data prior to import.

See labkey.selectRows for a discussion of the valid options and defaults for colNameOpt.

See Also

labkey.selectRows, makeFilter, labkey.insertRows, labkey.importRows, labkey.updateRows,
labkey.deleteRows, getRows

Examples

if (FALSE) {

## Example of an expicit join and use of group by and aggregates
# library(Rlabkey)

sql<- "SELECT AllTypesCategories.Category AS Category, 
    SUM(AllTypes.IntFld) AS SumOfIntFld,
    AVG(AllTypes.DoubleFld) AS AvgOfDoubleFld
    FROM AllTypes LEFT JOIN AllTypesCategories
    ON (AllTypes.Category = AllTypesCategories.TextKey)
    WHERE AllTypes.Category IS NOT NULL
    GROUP BY AllTypesCategories.Category"

sqlResults <- labkey.executeSql(
    baseUrl="http://localhost:8080/labkey",
    folderPath="/apisamples",
    schemaName="lists",
    sql = sql)

sqlResults

}