note: when I try to delete data in my test table using the deleterows function in javascript... it won't work unless I specify 'lsid' as the "key" which makes sense to some extent as the table definition shows 'lsid' as the primary key for this table. Problem is I don't know what are the values for this field and I can't seem to figure out how to do a global delete which is ultimately what I would like to do. When I use the example shown below it claims success but it does not change the table..

Using anything else gives the following error "No lsid, and no KeyManagement"

W

js sample:

<p><button onclick="onSubmit()">Click me to delete a record</button></p>

<script type="text/javascript">

function onSubmit(){
LABKEY.Query.deleteRows({
    requiredVersion: 16.3,
    schemaName: 'study',
    queryName: 'test',
    rows: [{'lsid':'1'}],
    success: onSuccess
});

function onSuccess(results) {
                  alert('Success!');
                 }
}
</script>

Hi Wayne,

To do an updateRows properly, you need to identify the Primary Key for the table you're working against. When it comes to a dataset, the Primary Key is usually the LSID as you observed.

Take a look at my basic Javascript updateRows function that I used in a wiki to change the Race of one of the records:

<script type="text/javascript">

LABKEY.Query.updateRows({
        schemaName: 'study',
        queryName: 'Dataset001',
        rows: [{"lsid": "urn:lsid:labkey.com:Study.Data-122:5003.1001.20171030.0000",
            "Race": "Native"
            }],
        success: function(data) {
            alert("Record is updated!");
            }
        })
</script>

As you can see, it uses the lsid to identify the specific row since it's the primary key for the table, then updates the specific field.

I managed to do the same exact thing using R:

> library(Rlabkey)
Loading required package: RCurl
Loading required package: bitops
Loading required package: rjson

> updaterow=data.frame(lsid="urn:lsid:labkey.com:Study.Data-122:5003.1001.20171030.0000", Race="Asian")
> updatedRow <- labkey.updateRows(baseUrl="http://localhost:8080/labkey", folderPath="/WayneTest", schemaName="study", queryName="Dataset001", toUpdate=updaterow)

> updatedRow
$rowsAffected
[1] 1

$queryName
[1] "Dataset001"

$schemaName
[1] "study"

$containerPath
[1] "/WayneTest"

$rows
$rows[[1]]
$rows[[1]]$date
[1] "2017/10/30 00:00:00"

$rows[[1]]$`Saliva Dt`
[1] "2015/12/31 00:00:00"

$rows[[1]]$dsrowid
[1] 12

$rows[[1]]$WGS
NULL

$rows[[1]]$CreatedBy
[1] 1005

$rows[[1]]$WES
NULL

$rows[[1]]$QCState
NULL

$rows[[1]]$CIDR
[1] "1"

$rows[[1]]$Created
[1] "2017/11/04 20:41:02"

$rows[[1]]$lsid
[1] "urn:lsid:labkey.com:Study.Data-122:5003.1001.20171030.0000"

$rows[[1]]$Ethnicity
[1] "non-hispanic"

$rows[[1]]$sourcelsid
NULL

$rows[[1]]$Race
[1] "Asian"

$rows[[1]]$Birthyr
[1] 2001

$rows[[1]]$`Buccal Dt`
[1] "2015/12/20 00:00:00"

$rows[[1]]$SampleID
[1] "1001"

$command
[1] "update"

==========

Can you provide more information on what you mean by "global delete"? Do you mean deleting multiple rows? What did you mean when you say that you "don't know what are the values for this field" with regard to the LSID? Are you talking about obtaining the LSID from the dataset or are you talking about the LSID format, such as "urn:lsid:labkey.com:Study.Data-122:5003.1001.20171030.0000"?

Regards,

Jon

The project is using a script that requires the ability to rewrite a table or f data rather than editing rows. Partly because f effIciency concerns and partly because the developer o f the script prefers to Update the entire table with a new array of data each time the script is run. Don’t see any way to delete all the data in a table and write a new array to table. Thought we could use deleterows and updaterows function but my colleague could not get it working.
We did try to determine the LSID for indIvidual rows to edit datasets but couldn’t call that up in a table hence the question. However we didn’t spend much time on that.

W

Hi Wayne,

The only way to update a table is either via individual rows or in batches when it comes to the API. Even if you choose to try and truncate the data from the table, it basically would do the same thing, deleting individual rows and then inserting new ones.

If you're looking to try and delete data in a table and write new data to it, I would suggest looking into using an ETL since can do a truncation, which would remove data from a table and then re-insert new rows on a table within LabKey.

https://www.labkey.org/Documentation/wiki-page.view?name=etlModule

Regards,

Jon

Hey Jon,

One more on this...
in lieu of the etlmodule, can we perhaps use the executesql function? Also, what do you mean when you say batch process.. just looping the single process or is there another way to do in one step?

Thanks,

W

Hi Wayne,

Unfortunately, the executeSql function within the LabKey Javascript API is limited to doing LabKey SQL, which means you're only allowed to do SELECT statements. INSERT, UPDATE, and DELETE SQL commands are not allowed and you would have no other option, but to use the insertRows(), updateRows(), and deleteRows() options.

The batch process I'm referring to is using the saveRows() function instead where you can bundle update, insert, and delete requests to multiple tables in a single request.

https://www.labkey.org/download/clientapi_docs/javascript-api/symbols/LABKEY.Query.html#.saveRows

However, regardless of whichever option you use, you would have to find a way to collect all of those Primary Keys and then apply the appropriate function to update or delete them if you are choosing to write a script to handle it in lieu of an ETL.

Regards,

Jon