• Sign In
  1. Messages

What are the limitations on EditableGrid's with config.sql-based Store's?

LabKey Support Forum (Inactive)

View Message

view list print
What are the limitations on EditableGrid's with config.sql-based Store's? slangley  2011-07-22 12:42
Status: Closed
 
From the documentation, I see that grids defined using Stores populated via SQL queries are read-only:

https://www.labkey.org/download/clientapi_docs/javascript-api/symbols/LABKEY.ext.Store.html

{String} config.sql
  A LabKey SQL statement to execute to fetch the data. You may specify either a queryName or sql, but not both. Note that when using sql, the store becomes read-only, as it has no way to know how to update/insert/delete the rows.

I suspect, however, that there are additional limitations:

  1. Interactive grid column filtering has no effect on the rows displayed in the grid.
  2. Exporting of the grid to Excel is not supported.

If my above suspicions are incorrect, then I have some cases which I can discuss further where those limitations do appear to be true.

Thanks.
 
 
slangley responded:  2011-07-22 15:43
Actually, #2, exporting to Excel, works if I turn off Dev Mode. It fails in Dev Mode because it throws a Assertion Error:

500: Unexpected server error
Can't set maxRows when not paginated

Home Back Folder

java.lang.AssertionError: Can't set maxRows when not paginated
       at org.labkey.api.query.QuerySettings.setMaxRows(QuerySettings.java:487)
       at org.labkey.api.query.QueryView.getExcelWriter(QueryView.java:1551)
       at org.labkey.api.query.QueryView.exportToExcel(QueryView.java:1610)
       at org.labkey.api.query.QueryView.exportToExcel(QueryView.java:1595)
       at org.labkey.query.controllers.QueryController$ExportSqlAction.export(QueryController.java:2509)
       at org.labkey.query.controllers.QueryController$ExportSqlAction.export(QueryController.java:2460)
       at org.labkey.api.action.ExportAction.getView(ExportAction.java:41)
       at org.labkey.api.action.SimpleViewAction.handleRequest(SimpleViewAction.java:61)
       ...
 
kevink responded:  2011-07-22 15:48
I've just fixed the 'Can't set maxRows when not paginated' bug for excel exports. It should show up in the 11.2 release.

I can't comment on the limitations of the interactive filters on the EditableGrid. We generally work better with the LABKEY.QueryWebPart grids, but of course those aren't editable.
 
slangley responded:  2011-07-22 16:21
Thanks.

I don't need for my grid to be editable. Can I use dynamic SQL created and submitted via JavaScript, i.e., config.sql, with a QueryWebPart grid?

If you can, I think that use is not documented.
 
kevink responded:  2011-07-22 16:52
In 11.2, you can use a 'sql' config parameter to LABKEY.QueryWebPart to execute arbitrary sql. The documentation hasn't been added yet for this config parameter.
 
Karl Lum responded:  2011-07-22 16:56
This was a new feature for 11.2 to support the SQL editor enhancements Nick worked on. I have a task to add the documentation for sprint 4.
 
slangley responded:  2011-07-28 11:05
Looking at my LabKey debug log, a query based on a table, i.e., a LabKey.ext.Store configured via a 'queryName' uses:

  QueryServiceImpl.getSelectSQL(FilteredTable over lab.expectations_change_log)

Whereas, a query based on dynamic SQL, i.e., a LabKey.ext.Store configured via a 'sql' string uses:

  QueryServiceImpl.getSelectSQL(org.labkey.query.sql.QuerySelect$1@5d453015)

The filtering behavior seems to be centered in the FilteredTable class.

So my reading of this is that EditableGrids configured to use a LabKey.ext.Store configured with a 'sql' query string are not designed to support filtering via a column filter set via the Grid's UI.