asked on January 30, 2018
Hi Team,
We are using flexmonster compressor to view data from a database view. Code:
String query = "SELECT * FROM view";
Statement statement = dataSource.getConnection().createStatement();
ResultSet resultSet = statement.executeQuery(query);
InputStream inputStream = Compressor.compressDb(resultSet);
response.setContentType("text/plain");
OutputStream outputStream = response.getOutputStream();
int length = 0;
byte[] buffer = new byte[10240];
while ((length = inputStream.read(buffer)) > 0)
{
outputStream.write(buffer, 0, length);
}
outputStream.flush();
log.info("Flex Monster loaded database view data successfully ");
The data is loaded successfully but pivot component treats all the columns as strings and doesn't show options like sum, average for the numeric columns like amount. Only options available are count and distinct count which means it is treating all the columns as strings.
Could you please help ?
⋅ January 30, 2018
If we export the same data as CSV and again import the CSV file in flexmonster, all options are properly displayed (sum, average etc) meaning it correctly detects numeric columns.
So I guess the issue may be in ocsv format or compressor itself.
⋅ January 30, 2018
Hello Ganesh,
Thank you for your question.
The compressor gets all the information about data types from the ResultSet metadata. This means that the data types in your database are not set correctly. Most of the aggregations in Flexmonster Pivot are available only for the numeric fields. Please check the datatypes of your columns in the database. If changing the datatypes is not an option please consider the idea of setting it explicitly via SQL query alias. Here is the example:
"SELECT name as 'Customer Name', birthday as 'd+Birthday',
home_town as 'Hometown', value as '-Value' FROM customers"
You can find the list of available data types in our documentation - https://www.flexmonster.com/doc/managing-data-presentation-csv/.
Please let us know if the information above was helpful.
Regards,
Dmyrto.
⋅ January 30, 2018
Hi,
Thanks for your answer.
I confirm that all the datatypes in our database are set correctly.
For the same resultset, when we directly set the csv data to outputstream without using flexmonster compression, it works as expected with proper data types available in the pivot. Code:
while (resultSet.next())
{
row = new StringBuilder();
row.append(resultSet.getString(1));
for (int i = 2; i < numberOfColumns + 1; i++)
{
row.append(COMMA_SEPARATOR).append(resultSet.getString(i));
}
row.append(System.lineSeparator());
log.debug("View Data {}", row);
outputStream.write(row.toString().getBytes());
}
So I think there is something wrong in compressor itself.
Thanks
⋅ January 30, 2018
Hello Ganesh,
Thank you for the clarification. Could you please provide us with the database dump so we can reproduce the issue on our side? As a temporary solution, you can set the data types explicitly via the SQL query alias. The data type can be set with the prefix in column alias such as '-'. Please find the example here:
"SELECT name as 'Customer Name', birthday as 'd+Birthday',
home_town as 'Hometown', value as '-Value' FROM customers"
The list of all supported data types is available in our documentation – https://www.flexmonster.com/doc/managing-data-presentation-csv/.
Waiting for the update from you.
Regards,
Dmytro.
⋅ January 30, 2018
We have tried with this sample table, it doesn't work with compressor:
create table SAMPLE
(
id NUMBER(18) not null,
value NUMBER(18,6) not null
) ;
insert into SAMPLE values (1, 15.60);
insert into SAMPLE values (2, 101.20);
insert into SAMPLE values (3, 50.80);
insert into SAMPLE values (4, 18.60);
and the code as mentioned in the 1st question.
Pivot definition:
var pivot = new Flexmonster({
container: "pivotContainer",
toolbar: true,
report: {
dataSource: {
dataSourceType: "ocsv",
filename: "name"
}},
licenseFilePath: "path"
});
⋅ January 30, 2018
Hello Ganesh,
Thank you for the example. Could you please confirm that you are using the latest version of the compressor and that the issue is reproducible there? Also, it would be nice if you send us some information about the database you are using. That will help us a lot in our further investigation.
Regards,
Dmytro
⋅ January 31, 2018
Yes, we are using the latest version (date: 15-01-2018) of it and issue is reproducible at our end. We are using oracle 11g database.
⋅ January 31, 2018
Hello, Ganesh,
Thank you for specifying that you use Oracle Database 11g. This has really helped us during the investigation.
Please download the new version of the Compressor containing the fix.
Let us know if the issue is solved!
Regards,
Tanya
⋅ February 1, 2018
Hi,
The issue has been resolved, thanks !
Is the version you provided an official release or it is a patch for this issue ?
⋅ February 1, 2018
Hi,
Thank you for the answer.
Your patch is based on the latest minor release version 2.413 and contains only a small fix for Oracle.
Also, this fix will be included in the next official minor release 2.414 (ETA Feb 12).
Hope it helps.
Regards,
Ian
⋅ February 14, 2018
Hello, Ganesh,
We would like to inform you that minor version 2.414 is released.
The fix for number type in Oracle 11g is included.
Regards,
Tanya
