In a JDBC query, when "Process in Subsets" is selected, the lastSubset flag is not set to true when the number of records fetched in the last iteration are equal to the subsetsize.
Article ID: KB0088717
Updated On:
| Products | Versions |
|---|---|
| TIBCO ActiveMatrix BusinessWorks | - |
| Not Applicable | - |
Description
Resolution:
In a JDBC query, when "Process in Subsets" is selected, the lastSubset flag is not set to true when the number of records fetched in the last iteration are equal to the subsetsize.
Symptoms:
========
We use JDBC query with the Advanced option of "process in subsets" and the subset size is set to 100 in the input tab. If we have exactly 1000 records, instead of executing 10 times, the loop is getting executed 11 times. On the 10th time, the lastsubset is not set to true. And after the last time ( 11th ) , there is no record and only the lastsubset= true is available. This causes a problem that the last record set has no records and throws the null pointer exception when processed and the subsequent process stops.
Resolution:
==========
In the boundary conditions:
- If subsetSize equals the total number of rows in table.
- If the total number of rows in a table can be divided by subsetSize with no remainder.
then the lastSubset in the output is false (in the given scenario, the 10th subset). This is an expected behavior. The reason is:
When an SQL query returns a resultset to the JDBC application, the cursor is positioned before the first record, not the first record. The application then checks condition like:
while (resultset.next() and loop <= subsetSize)
- Or you can also count the number of records in the subset to ensure there are some records before processing them.
The appropriate way to process subset records is:
Check lastSubset flag:
If false, you can then expect there are "subsetSize" records in this subset
If true, either count the number of available records in the subset or use an iterate group to walk through available records.
Description:
===========In a JDBC query, when "Process in Subsets" is selected, the lastSubset flag is not set to true when the number of records fetched in the last iteration are equal to the subsetsize.
Symptoms:
========
We use JDBC query with the Advanced option of "process in subsets" and the subset size is set to 100 in the input tab. If we have exactly 1000 records, instead of executing 10 times, the loop is getting executed 11 times. On the 10th time, the lastsubset is not set to true. And after the last time ( 11th ) , there is no record and only the lastsubset= true is available. This causes a problem that the last record set has no records and throws the null pointer exception when processed and the subsequent process stops.
Resolution:
==========
In the boundary conditions:
- If subsetSize equals the total number of rows in table.
- If the total number of rows in a table can be divided by subsetSize with no remainder.
then the lastSubset in the output is false (in the given scenario, the 10th subset). This is an expected behavior. The reason is:
When an SQL query returns a resultset to the JDBC application, the cursor is positioned before the first record, not the first record. The application then checks condition like:
while (resultset.next() and loop <= subsetSize)
to see if there are any more records in the result set, where the loop is initially set to 1 and incremented by 1 each time. When the application is processing the last record in the resultset, resultset.next() is still true, therefore it sets lastSubset to false.
Solution for avoiding the processing exception:
==================================
There is no "look-ahead" feature supported by the cursor of resultSet. That is, it is not possible to know in advance how many records there are in a resultSet. One will need to actually walk through it to get the answer.
- If you look at the jdbc_advance examples, one could easily avoid the processing exception by using an iterate group (on resultSet/Record variable list).
Solution for avoiding the processing exception:
==================================
There is no "look-ahead" feature supported by the cursor of resultSet. That is, it is not possible to know in advance how many records there are in a resultSet. One will need to actually walk through it to get the answer.
- If you look at the jdbc_advance examples, one could easily avoid the processing exception by using an iterate group (on resultSet/Record variable list).
- Or you can also count the number of records in the subset to ensure there are some records before processing them.
The appropriate way to process subset records is:
Check lastSubset flag:
If false, you can then expect there are "subsetSize" records in this subset
If true, either count the number of available records in the subset or use an iterate group to walk through available records.
Issue/Introduction
In a JDBC query, when "Process in Subsets" is selected, the lastSubset flag is not set to true when the number of records fetched in the last iteration are equal to the subsetsize.
Additional Information
<TIBCO_HOME>/bw/<version>\examples\activities\jdbc\jdbc_advance . Location for jdbc_advance example.
Feedback
Yes
No
Powered by
