Support for ResultSet.TYPE_FORWARD_ONLY in Aspose.Cells for Java

[sbbliss]

When will ResultSet.TYPE_FORWARD_ONLY be supported by the WorkbookDesigner.setDataSource?

My company is considering switching from a competitor's product and 99% of our ResultSets are generated by Oracle stored procedures with the ResultSet returned as SYS_REFCURSOR. This only allows the ResultSet to have a scroll type of TYPE_FORWARD_ONLY.

I already know of several workarounds, but those are never as desirable as a properly supported feature. I'm just looking for what the future plans are for this feature.

Thanks,

[amjad.sahi]

Hi,

Thanks for your inquiry.

We will look into your requested feature and get back to you soon.

Thank you.

[nausherwan.aslam]

Hi,

Thank you for considering Aspose.

Please try the attached latest fix of Aspose.Cells. Now we allow setting ResultSet with type TYPE_FORWARD_ONLY as data source for WorkbookDesigner. However, one thing to be noted: If smart markers that take this ResultSet as datasource lay in multiple rows, only those markers in the first row can be filled with data because the ResultSet can only be used once. In such situation, you can try the newly added API:

void com.aspose.cells.WorkbookDesigner.setDataSource (String dataSourceName, ResultSet rs, boolean cacheForForwardOnly)

Sets data source of a ResultSet object and binds it to a data source name.

Parameters:

dataSourceName Data source name.

rs The ResultSet object.

cacheForForwardOnly whether caches data from ResultSet into memory when ResultSet’s type is TYPE_FORWARD_ONLY. If ResultSet’s type is not TYPE_FORWARD_ONLY, this parameter will be ignored. Otherwise: If cache flag is true, all data will be fetched from the ResultSet and cached in memory before processing smart markers. If cache flag is false, no data will be cached and the ResultSet can be used only once. In such situation if there are multiple rows that contain markers that take this ResultSet as data source, only the markers in the first row can be filled with data.

For this solution, please note that it will be a memory consumed operation to cache all data when the dataset is large in the ResultSet

Thank You & Best Regards,

[sbbliss]

Thank you for your quick response.

However, this approach is not consistent with the qualiy I expect from the rest of the product

1. The extra boolean parameter does not add any benefits. ResultSet.getType() can tell the WorkbookDesigner logic if its dealing with a TYPE_FORWARD_ONLY. It should not be necessary to provide a boolean parameter to identify the resultset.

2. Passing in False for the parameter does not provide any benefits with any kind of resultset. If it was beneficial to limit the number of records retrieved the API should provide an int recordLimit parameter to do that. Otherwise its just counter-intuitive.

3. It sounds like the logic is caching the entire data from the ResultSet. This is a less than ideal for a production web environment with hundreds to thousands of simultaneous users. Most of the current reports we would port to the Aspose.Cells API are created with at least 10 and sometimes as many as a 100 ResultSets. Caching each ResultSet would be extremely memory intensive. Not a good thing.

One of the workarounds that I have created is our own WorkbookDesigner-like class that works with any type of ResultSet. I've attached an extremely simplified version of the code along with sample input and output files.

Take a look at the process method (summarized below):

boolean hasMoreData=false;
if(ResultSet.next()) {
do {
//retrieve the current record of data from the worksheet
.... your code here....

//check if there is another record.
hasMoreData = ResultSet.next();

//if there is another record, insert the
//next row in Sheet
if(hasMoreData) {
...code to insert row, copy formatting, etc ...
}

//write resultset data to current row (not new one)
... getCell().setValue(value)

//if we have more data, save the newly inserted,
//and blank row, as the row we will work on next...
if(hasMoreData) {
... make row inserted above the current row..
}

}while(hasMoreData);

This kind of logic won't depend on knowing how many records there are in advance, which appears to be an important part of the Aspose.Cells logic.

[nausherwan.aslam]

Hi,

Thank you for the feedback and detailed response.

We will look into your requirements and suggestions and get back to you soon.

Thank You & Best Regards,

[nausherwan.aslam]

Hi,

Thank you for considering Aspose.

sbbliss:

1. The extra boolean parameter does not add any benefits. ResultSet.getType() can tell the WorkbookDesigner logic if its dealing with a TYPE_FORWARD_ONLY. It should not be necessary to provide a boolean parameter to identify the resultset.

This parameter, as per the javadoc of this api, is used only for TYPE_FORWARD_ONLY. For other type of ResultSet, it will be ignored. If all the markers that takes this ResultSet as data source in one same Row in the template excel file, you need not cache the ResultSet even if the ResultSet’s type is TYPE_FORWARD_ONLY. Just as your sample template file “ResultSetExampleInput.xls”, for this file you actually need not cache the ResultSet even if it is TYPE_FORWARD_ONLY and it is the main purpose for this parameter.

sbbliss:

2. Passing in False for the parameter does not provide any benefits with any kind of resultset. If it was beneficial to limit the number of records retrieved the API should provide an int recordLimit parameter to do that. Otherwise its just counter-intuitive.

Thank you for this suggestion, it is better for an integer parameter to specify whether cache and the cache records limit than only a boolean flag to denote cache or not. So now we have changed the api to setDataSource(String dataSourceName, ResultSet rs, int cacheLimit) in the attached latest fix, please try it.

sbbliss:

3. It sounds like the logic is caching the entire data from the ResultSet. This is a less than ideal for a production web environment with hundreds to thousands of simultaneous users. Most of the current reports we would port to the Aspose.Cells API are created with at least 10 and sometimes as many as a 100 ResultSets. Caching each ResultSet would be extremely memory intensive. Not a good thing.

As we have said in reply to your first question, for your logic and the template file with smart markers, you do not need to cache the ResultSet even if it is TYPE_FORWARD_ONLY. If those markers that take this ResultSet as data source are present in multiple rows, the logic is too complex to use this ResultSet without cache and process all markers at one time. And for such situation, we have to cache all data from this ResultSet.

sbbliss:

One of the workarounds that I have created is our own WorkbookDesigner-like class that works with any type of ResultSet. I’ve attached an extremely simplified version of the code along with sample input and output files.

Take a look at the process method (summarized below):

boolean hasMoreData=false;
if(ResultSet.next()) {
do {
//retrieve the current record of data from the worksheet
… your code here…

//check if there is another record.
hasMoreData = ResultSet.next();

//if there is another record, insert the
//next row in Sheet
if(hasMoreData) {
…code to insert row, copy formatting, etc …
}

//write resultset data to current row (not new one)
… getCell().setValue(value)

//if we have more data, save the newly inserted,
//and blank row, as the row we will work on next…
if(hasMoreData) {
… make row inserted above the current row…
}

}while(hasMoreData);

This kind of logic won’t depend on knowing how many records there are in advance, which appears to be an important part of the Aspose.Cells logic.

Thank You & Best Regards,

[aspose.notifier]

The issues you have found earlier (filed as 11009) have been fixed in this update.

---

This message was posted using Notification2Forum from Downloads module by aspose.notifier.

[sbbliss]

Awesome! I just downloaded the update and I’ll test it out today. Thank you for work on this issue.