Rlabkey (version 2.1.121)

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, showHidden = FALSE, colNameOpt='caption')

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.

showHidden

(optional) a logical value indicating whether or not to return data columns that would normally be hidden from user veiw. If no value is specified, the hidden columns are not returned.

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.

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.

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.updateRows, labkey.deleteRows, getRows

Examples

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