labkey.insertRows: Insert new rows of data into a LabKey Server

Description

Insert new rows of data into the database.

Usage

labkey.insertRows(baseUrl, folderPath,
    schemaName, queryName, toInsert, na,
    provenanceParams=NULL, options=NULL)

Value

A list is returned with named categories of command, rowsAffected, rows, queryName, containerPath and schemaName. The schemaName, queryName and containerPath properties contain the same schema, query and folder path used in the request. The rowsAffected property indicates the number of rows affected by the API action. This will typically be the same number as passed in the request. The rows property contains a list of row objects corresponding to the rows inserted.

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
queryName: a string specifying the queryName
toInsert: a data frame containing rows of data to be inserted
na: (optional) the value to convert NA's to, defaults to NULL
provenanceParams: the provenance parameter object which contains the options to include as part of a provenance recording. This is a premium feature and requires the Provenance LabKey module to function correctly, if it is not present this parameter will be ignored.
options: (optional) a list containing options specific to the insert action of the query

Author

Valerie Obenchain

Details

A single row or multiple rows of data can be inserted. The toInsert data frame must contain values for each column in the dataset and must be created with the stringsAsFactors option set to FALSE. The names of the data in the data frame must be the column names from the LabKey Server.To insert a value of NULL, use an empty string ("") in the data frame (regardless of the database column type). Also, when inserting data into a study dataset, the sequence number must be specified.

The list of valid options for each query will vary, but some common examples include:

auditBehavior (string) : Can be used to override the audit behavior for the table the query is acting on. The set of types include: NONE, SUMMARY, and DETAILED.
auditUserComment (string) : Can be used to provide a comment from the user that will be attached to certain detailed audit log records.

Examples

Run this code

if (FALSE) {

## Insert, update and delete
## Note that users must have the necessary permissions in the database
## to be able to modify data through the use of these functions
# library(Rlabkey)

newrow <- data.frame(
	DisplayFld="Inserted from R"
	, TextFld="how its done"
	, IntFld= 98 
	, DoubleFld = 12.345
	, DateTimeFld = "03/01/2010"
	, BooleanFld= FALSE
	, LongTextFld = "Four score and seven years ago"
#	, AttachmentFld = NA    #attachment fields not supported 
	, RequiredText = "Veni, vidi, vici"
	, RequiredInt = 0
	, Category = "LOOKUP2"
	, stringsAsFactors=FALSE)

insertedRow <- labkey.insertRows("http://localhost:8080/labkey",
    folderPath="/apisamples", schemaName="lists", queryName="AllTypes",
    toInsert=newrow, options=list(auditBehavior="DETAILED",
    auditUserComment="testing audit comment for insert"))
newRowId <- insertedRow$rows[[1]]$RowId

selectedRow<-labkey.selectRows("http://localhost:8080/labkey",
    folderPath="/apisamples", schemaName="lists", queryName="AllTypes",
    colFilter=makeFilter(c("RowId", "EQUALS", newRowId)))
updaterow=data.frame(
	RowId=newRowId
	, DisplayFld="Updated from R"
	, TextFld="how to update"
	, IntFld= 777 
	, stringsAsFactors=FALSE)

updatedRow <- labkey.updateRows("http://localhost:8080/labkey",
    folderPath="/apisamples", schemaName="lists", queryName="AllTypes",
    toUpdate=updaterow, options=list(auditBehavior="DETAILED",
    auditUserComment="testing audit comment for update"))
selectedRow<-labkey.selectRows("http://localhost:8080/labkey",
    folderPath="/apisamples", schemaName="lists", queryName="AllTypes",
    colFilter=makeFilter(c("RowId", "EQUALS", newRowId)))

deleterow <- data.frame(RowId=newRowId, stringsAsFactors=FALSE)
result <- labkey.deleteRows(baseUrl="http://localhost:8080/labkey",
    folderPath="/apisamples", schemaName="lists", queryName="AllTypes",
    toDelete=deleterow)

## Example of creating a provenance run with an initial step with material inputs, a second step
## with provenance mapping to link existing samples with newly inserted samples, and a final step
## with a data output
##
mi <- data.frame(lsid=c("urn:lsid:labkey.com:Sample.251.MySamples:sample1",
        "urn:lsid:labkey.com:Sample.251.MySamples:sample2"))
p <- labkey.provenance.createProvenanceParams(name="step1", description="initial step",
        materialInputs=mi)
ra <- labkey.provenance.startRecording(baseUrl="https://labkey.org/labkey/",
        folderPath = "Provenance", provenanceParams=p)

rows <- fromJSON(txt='[{
        "name" : "sample3",
        "protein" : "p3",
        "prov:objectInputs" : [
            "urn:lsid:labkey.com:Sample.251.MySamples:sample21",
            "urn:lsid:labkey.com:Sample.251.MySamples:sample22"
        ]
    },{
        "name" : "sample4",
        "protein" : "p4",
        "prov:objectInputs" : [
            "urn:lsid:labkey.com:Sample.251.MySamples:sample21",
            "urn:lsid:labkey.com:Sample.251.MySamples:sample22"
        ]
    }
]')

labkey.insertRows(baseUrl="https://labkey.org/labkey/", folderPath = "Provenance",
    schemaName="samples", queryName="MySamples", toInsert=rows,
    provenanceParams=labkey.provenance.createProvenanceParams(name="query step",
        recordingId=ra$recordingId))
labkey.provenance.stopRecording(baseUrl="https://labkey.org/labkey/", folderPath = "Provenance",
    provenanceParams=labkey.provenance.createProvenanceParams(name="final step",
    recordingId=ra$recordingId, dataOutputs=do))
}

Run the code above in your browser using DataLab