12.1p serverside exportData HeaderSpan related exception

[Blama]

Hi Isomorphic,

I'm doing small changes to my exportData code (using ListGrid.exportData()) and suddenly I'm getting this exception.
I'm pretty sure I can work around it, but I don't think it should happen at all nevertheless.

Can you have a look if anything is wrong in this area? I'm using v12.1p_2022-08-10.

Best regards
Blama

...truncated (sql.log.maxLength: 10000) exceeded.;
com.iso.dat.DSResponse [https-openssl-nio-443-exec-6] DSResponse: List with 193 items;
com.iso.dat.DSResponse [https-openssl-nio-443-exec-6] DSResponse: List with 193 items;
com.iso.rpc.ExcelDataExport [https-openssl-nio-443-exec-6] headerSpan definition names an unknown field 'Land' - ignoring;
com.iso.rpc.ExcelDataExport [https-openssl-nio-443-exec-6] headerSpan definition names an unknown field 'Land AP' - ignoring;
java.lang.IllegalArgumentException: Invalid column index (-1). Allowable column range for EXCEL2007 is (0..16383) or ('A'..'XFD')
    at org.apache.poi.xssf.streaming.SXSSFRow.checkBounds(SXSSFRow.java:169)
    at org.apache.poi.xssf.streaming.SXSSFRow.createCell(SXSSFRow.java:156)
    at org.apache.poi.xssf.streaming.SXSSFRow.createCell(SXSSFRow.java:140)
    at org.apache.poi.xssf.streaming.SXSSFRow.createCell(SXSSFRow.java:37)
    at com.isomorphic.rpc.ExcelDataExport.outputHeaderSpans(ExcelDataExport.java:1430)
    at com.isomorphic.rpc.ExcelDataExport.outputHeaders(ExcelDataExport.java:1108)
    at com.isomorphic.rpc.ExcelDataExport.getExportObject(ExcelDataExport.java:497)
    at com.isomorphic.rpc.DataExport.exportResultSet(DataExport.java:664)
    at com.isomorphic.rpc.DataExport.exportResultSet(DataExport.java:463)
    at com.isomorphic.rpc.RPCManager.completeResponse(RPCManager.java:1327)
    at com.isomorphic.rpc.RPCManager.send(RPCManager.java:760)
    at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:183)

[Isomorphic]

We definitely want to fix this. Can you give us some clues as to how to reproduce it? Will a modified sample reproduce it, for example?

[Blama]

Hi Isomorphic,

yes, I managed to create a testcase. It's related to a field being in headerSpans, but not in exportFields.
Please see this modified testcase (v12.1p_2023-01-11).

1) Testcase as it is below: Exception, export document only has a few headers, no data
2) Testcase with exportFields: ["countryName", "capital", "independence", "population", "continent"]: OK, but strange field order ("Country, Continent, Capital" instead of "Country, Capital, Continent"
3) no exportFields: Export as expected

Also, in general, I'm not sure it's a good idea to generate and deliver a broken export. If an exception occurs, could you not return an error message? I know this might be related to the export request being special (which is also the cause that it does not show a value in the Developer Console RPC-Tab Status-column(?)), but if there is something you can do, I think this would be better. Again exportData()-requests could be entirely serverside and look successful while in reality they are broken (like here).

I found this issue because I had to fiddle with exportFields. And I have to fiddle with exportFields, as they are not automagically correct - see this thread.

Best regards
Blama

export.js:

isc.ListGrid.create({
    ID: "countryList",
    width:500, height:250, top:70, alternateRecordStyles:true,
    dataSource: worldDSExport,
    autoFetchData: true,
    fields:[
        {name:"countryName", title:"Country"},
        {name:"capital", title:"Capital"},
        {name:"continent", title:"Continent"},
        {name:"independence", title:"Nationhood", width:100},
        {name:"population", title:"Population"}
    ],
    showFilterEditor: true,
    headerSpans: [
        {
            fields: ["countryName", "capital", "continent"],
            title: "Contry information"
        }
    ]
});

isc.DynamicForm.create({
    ID: "exportForm",
    width:300,
    fields: [
        { name: "exportType", title: "Export Type", type:"select", width:"*",
            defaultToFirstOption: true,
            valueMap: {
                "ooxml" : "OOXML (Excel2007)",
                "csv" : "CSV" ,
                "xml" : "XML",
                "json" : "JSON",
                "xls" : "XLS (Excel97)"
            }
        },
        { name: "showInWindow", title: "Show in Window", type: "boolean", align:"left" }
    ]
});

isc.Button.create({
   ID: "exportButton",
   title: "Export",
   left: 320,
   click: function () {
       var exportAs = exportForm.getField("exportType").getValue();
       var showInWindow = exportForm.getField("showInWindow").getValue();
       if (exportAs == "json") {
           // JSON exports are server-side only, so use the OperationBinding on the DataSource
           countryList.exportData({ operationId: "customJSONExport",
               exportDisplay: showInWindow ? "window" : "download"});
       } else {
           // exportAs is not JSON, so we can set that with requestProperties
           countryList.exportData({ exportAs: exportAs,
               exportDisplay: showInWindow ? "window" : "download", exportFields:["countryName", "capital", "independence", "population"] //, "continent"]
           });
       }
   }
});

[Isomorphic]

This is all fixed now and available for download in latest nightly builds. Specifically exportFields presence and order breaking the export in different ways. Let us know please how it worked for you. Regarding error reporting we'll follow up later.

[Isomorphic]

Apologies, for the 12.1 version these fixes will be available for download since Feb 2 (tomorrow).

[Blama]

Hi Isomorphic,

the exception is gone now, thanks (v12.1p_2023-02-02).

The only thing remaining is an unexpected order for the call with

exportDisplay: showInWindow ? "window" : "download", exportFields:["countryName", "capital", "independence", "population", "continent"]

The fields in the export are in this order then (continent not last, as I'd expect):

Country, Continent, Capital, ...

But I think this is minor.

Thank you & Best regards
Blama

[Isomorphic]

This is not how things are supposed to work, thanks for telling us. We want to keep the headerSpans and still respect the order of exportFields and it was expected to work as that already. Anyway, this is fixed now and will be available for download in nightly builds since Feb 7 (tomorrow). Let us know please how it worked for you.

[Blama]

Hi Isomorphic,

using v12.1p_2023-02-07 the issue from #6 seems to be fixed, but playing around with it I noticed that this call still results in an empty document (=most likely serverside exception).

Best regards
Blama

isc.ListGrid.create({
    ID: "countryList",
    width:500, height:250, top:70, alternateRecordStyles:true,
    dataSource: worldDSExport,
    autoFetchData: true,
    fields:[
        {name:"countryName", title:"Country"},
        {name:"capital", title:"Capital"},
        {name:"continent", title:"Continent"},
        {name:"independence", title:"Nationhood", width:100},
        {name:"population", title:"Population"}
    ],
    showFilterEditor: true,
    headerSpans: [
        {
            fields: ["countryName", "capital", "continent"],
            title: "Country information"
        },
        {
            fields: ["independence", "population"],
            title: "Other info"
        }
    ]
});

isc.DynamicForm.create({
    ID: "exportForm",
    width:300,
    fields: [
        { name: "exportType", title: "Export Type", type:"select", width:"*",
            defaultToFirstOption: true,
            valueMap: {
                "ooxml" : "OOXML (Excel2007)",
                "csv" : "CSV" ,
                "xml" : "XML",
                "json" : "JSON",
                "xls" : "XLS (Excel97)"
            }
        },
        { name: "showInWindow", title: "Show in Window", type: "boolean", align:"left" }
    ]
});

isc.Button.create({
   ID: "exportButton",
   title: "Export",
   left: 320,
   click: function () {
       var exportAs = exportForm.getField("exportType").getValue();
       var showInWindow = exportForm.getField("showInWindow").getValue();
       if (exportAs == "json") {
           // JSON exports are server-side only, so use the OperationBinding on the DataSource
           countryList.exportData({ operationId: "customJSONExport",
               exportDisplay: showInWindow ? "window" : "download"});
       } else {
           // exportAs is not JSON, so we can set that with requestProperties
           countryList.exportData({ exportAs: exportAs,
              exportDisplay: showInWindow ? "window" : "download", exportFields:["population", "continent", "capital", "countryName", "independence"]
           });
       }
   }
});

[Isomorphic]

This is fixed and will be available for download in nightly builds since Feb 22 (tomorrow). Let us know please how it worked for you.

[Blama]

Hi Isomorphic

the testcases in here from #8, #6 and #3 are looking good using v12.1p_2023-02-22.
I did play around a bit and did not notice anything else not working so far.

Thank you & Best regards
Blama

[Blama]

Hi Isomorphic,

actually I found a (wrong) configuration of headerSpans (listing a headerSpan twice), that results in an almost empty document (only the wrong headerSpan and no data in the document).
But this could be a "garbage in, garbage out"-type of situation and therefore be ignored (online sample using v12.1p_2023-02-22)

Best regards
Blama

isc.ListGrid.create({
    ID: "countryList",
    width:500, height:250, top:70, alternateRecordStyles:true,
    dataSource: worldDSExport,
    autoFetchData: true,
    fields:[
        {name:"countryName", title:"Country"},
        {name:"capital", title:"Capital"},
        {name:"continent", title:"Continent"},
        {name:"independence", title:"Nationhood", width:100},
        {name:"population", title:"Population"}
    ],
    showFilterEditor: true,
    headerSpans: [
        {
            fields: ["countryName", "capital", "continent"],
            title: "Country information"
        },
        {
            fields: ["independence", "population"],
            title: "Other info"
        },
        {
            fields: ["independence", "population"],
            title: "Other info2 - this is wrong"
        }
    ]
});

isc.DynamicForm.create({
    ID: "exportForm",
    width:300,
    fields: [
        { name: "exportType", title: "Export Type", type:"select", width:"*",
            defaultToFirstOption: true,
            valueMap: {
                "ooxml" : "OOXML (Excel2007)",
                "csv" : "CSV" ,
                "xml" : "XML",
                "json" : "JSON",
                "xls" : "XLS (Excel97)"
            }
        },
        { name: "showInWindow", title: "Show in Window", type: "boolean", align:"left" }
    ]
});

isc.Button.create({
   ID: "exportButton",
   title: "Export",
   left: 320,
   click: function () {
       var exportAs = exportForm.getField("exportType").getValue();
       var showInWindow = exportForm.getField("showInWindow").getValue();
       if (exportAs == "json") {
           // JSON exports are server-side only, so use the OperationBinding on the DataSource
           countryList.exportData({ operationId: "customJSONExport",
               exportDisplay: showInWindow ? "window" : "download"});
       } else {
           // exportAs is not JSON, so we can set that with requestProperties
           countryList.exportData({ exportAs: exportAs,
              exportDisplay: showInWindow ? "window" : "download", exportFields:["population", "continent", "capital", "countryName", "independence"]
           });
       }
   }
});

[Blama]

Hi Isomorphic,

I stumbled upon this again and this is still open using v12.1p_2023-04-24.

You did not update here if you want to fix this or not (because of "garbage in garbage out"), so I'm bumping it.

Best regards
Blama