MAX Length Exception thrown when a DB column exceeds

[imdadareeph]

Got the following exception while trying to get data from a table having json values as String.

The code works fine. but throws exception when the data size is more. However, there is no means to truncate the data in java level before putting into excel sheet . I had to improvise my query as dirty fix and then only it work

SELECT SUBSTRING(Request,1,32766) as REQ_PART1,SUBSTRING(Request,32766,62081) as REQ_PART2

Code snipeshot: Select Query:

public static String BD_LOG_TBL_COLUMNS = "Col1,Col2,Col3,Col4,SUBSTRING(Request,1,32766) as REQ_PART1,SUBSTRING(Request,32766,62081) as REQ_PART2,Response";

public static String getDbLogCreateQuery(InputBean inputBean) {
    StringBuilder queryBuilder = new StringBuilder("SELECT TOP 100 "+ BC_EMAIL_TBL_COLUMNS +" FROM ["+inputBean.getDbSchema().getSchema1()+"].[dbo].[BD_LOG_TBL] where Id in ('"+inputBean.getId()+"') order by Id desc");
    System.out.println(queryBuilder.toString());
    return queryBuilder.toString();
}

Code snipeshot:

MempoiSheet BD_LOG= MempoiSheetBuilder.aMempoiSheet()
                .withSheetName(DbTables.DBLOG.getValue())
                .withPrepStmt(conn.prepareStatement(QueryExecutor.getDBData(getDbLogCreateQuery(inputBean))))
                .build();

Build excelsheet:

        MemPOI memPOI = MempoiBuilder
            .aMemPOI()
            .withFile(fileDest)
            .withAdjustColumnWidth(true)
            .addMempoiSheet(reporttable)
            .addMempoiSheet(loggedindata)
            .addMempoiSheet(userdata)
            .addMempoiSheet(BD_LOG)
            .build();

        CompletableFuture<String> fut = memPOI.prepareMempoiReportToFile();
        String absoluteFileName = fut.get();`

Exception:

java.util.concurrent.ExecutionException: it.firegloves.mempoi.exception.MempoiException: it.firegloves.mempoi.exception.MempoiException: java.lang.reflect.InvocationTargetException
        at java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:357)
        at java.util.concurrent.CompletableFuture.get(CompletableFuture.java:1895)
        at com.emirates.ibe.util.dbreports.DbReportsRunner.fetchStateStoreData(DbReportsRunner.java:107)
        at com.emirates.ibe.util.dbreports.DbReportsRunner.main(DbReportsRunner.java:38)
Caused by: it.firegloves.mempoi.exception.MempoiException: it.firegloves.mempoi.exception.MempoiException: java.lang.reflect.InvocationTargetException
        at it.firegloves.mempoi.strategos.Strategos.generateSheet(Strategos.java:159)
        at java.util.ArrayList.forEach(ArrayList.java:1249)
        at it.firegloves.mempoi.strategos.Strategos.generateReport(Strategos.java:117)
        at it.firegloves.mempoi.strategos.Strategos.generateMempoiReport(Strategos.java:89)
        at it.firegloves.mempoi.strategos.Strategos.generateMempoiReportToFile(Strategos.java:64)
        at it.firegloves.mempoi.MemPOI.lambda$prepareMempoiReportToFile$0(MemPOI.java:45)
        at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1590)
        at java.util.concurrent.CompletableFuture$AsyncSupply.exec(CompletableFuture.java:1582)
        at java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:289)
        at java.util.concurrent.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1056)
        at java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1692)
        at java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:157)
Caused by: it.firegloves.mempoi.exception.MempoiException: java.lang.reflect.InvocationTargetException
        at it.firegloves.mempoi.strategos.DataStrategos.createDataRows(DataStrategos.java:117)
        at it.firegloves.mempoi.strategos.Strategos.createSheetData(Strategos.java:187)
        at it.firegloves.mempoi.strategos.Strategos.generateSheet(Strategos.java:144)
        ... 11 more
Caused by: java.lang.reflect.InvocationTargetException
        at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at it.firegloves.mempoi.strategos.DataStrategos.createDataRows(DataStrategos.java:105)
        ... 13 more
**Caused by: java.lang.IllegalArgumentException: The maximum length of cell contents (text) is 32,767 characters**
        at org.apache.poi.xssf.streaming.SXSSFCell.setCellValue(SXSSFCell.java:309)
        ... 17 more

[mirkoPan]

Hi imdadareeph, thanks for your report. Reading the exception, in the final part, it seems that the problem is due to a limit in the maximum length of the value of a cell supported by Apache POI. Below is the relevant portion of the exception you reported to us:

** Caused by: java.lang.IllegalArgumentException: The maximum length of cell contents (text) is 32,767 characters **
         at org.apache.poi.xssf.streaming.SXSSFCell.setCellValue (SXSSFCell.java:309)

Can you achieve your goal using only plain Apache POI? If the answer is yes, we'll check more exestensively our code base to overcome the problem