Valence 5.1 NAB Nitro Query Download to Excel not working

[Ian_Spottiswood]

A customer of ours has a simple Nitro Query built over 2 tables, returning ~200 rows of 8 columns, which is a mix of character and graphic data (the graphic data is being Cast as character in the SQL Select statement though). Data is being returned to the GUI grid successfully (and quickly), but when clicking the Download button, nothing happens.

We can see a vvcall.pgm to action "getDataExcel" in pgm "vvdatasrc" (<F12> Developer Tools - Network), but this times out after ~30 seconds and nothing happens in the GUI (no "waiting to download" message, etc.) The CGI joblog shows a temp directory being created each time, but this is empty.
If I untick the QSHELL_REMOTE setting in Portal Admin Hidden Settings, the vvcall.pgm fails much more quickly (we can see it turn red in Dev Tools after a few seconds).

Customer has a "no DNS setup" on a hosted IBM i - could this be contributing to the problem?

Cheers,
Ian

[robert.swanson]

Hi Ian,

After the temporary directory is created for the Excel file, the next step is to unzip an Excel template file. I suspect that unzip is failing -- do you see anything in the Errors app (or in file VVERRLOG) reflecting that? The message would likely say "Error opening XLSX template" or something like that.

Depending on which specific build of 5.1 they're running, the back-end could be trying to use a jar utility (specifically, /QOpenSys/usr/bin/jar), which may not exist on their system. We've made multiple changes to that download process since 5.1 to make it function in a more failsafe manner on any system.

Perhaps you could have them download Valence 6.3 and try the same app to confirm it's working there? There have been myriad changes made to Valence over the seven years since 5.1 was released!

[Ian_Spottiswood]

Hi Rob,

I've checked the IFS folder /QOpenSys/usr/bin/jar, and it looks like that jar file is there, so that's good :)

Looking at VVERRLOG though, we can see 2 persistent error messages, as a result of clicking the Download button:

"Timeout encountered during processing of remote QSHELL command" - "No response was found in data queue VV074576O within timeout value of 60 seconds. If job VRMT074576, which is responsible for processing remote calls and populating the data queue, is no longer running then you must restart the IWMPLIV server instance before any more remote commands can be processed."

VVCALL[VVCALL]-->VVDATASRC[VVDATASRC]-->VVDATASRC[GETDATAEXCEL]-->VVSRVPGM{VVOUT}[VVOUT_EXECSQLTOSS]-->VVSRVPGM{VVOUT}[VVOUT_SSSQLROWS]-->VVSRVPGM{VVOUT}[VVOUT_SSOPENWORKSHEET]-->VVSRVPGM{VVOUT}[VVOUT_SSOPENWORKBOOK]-->VVSRVPGM{VVOUT}[VVOUT_XLSXOPENWORKBOOK]-->VVSRVPGM{VVUTILITY}[VVUTILITY_QSHELL]-->VVRMTDB[VVRMTDB]-->VVRMTDB[PROCESSDIRECTIVE]

and

"Error opening Temp XLSX workspace" - "Failed to open XLSX workspace"

VVCALL[VVCALL]-->VVDATASRC[VVDATASRC]-->VVDATASRC[GETDATAEXCEL]-->VVSRVPGM{VVOUT}[VVOUT_EXECSQLTOSS]-->VVSRVPGM{VVOUT}[VVOUT_SSSQLROWS]-->VVSRVPGM{VVOUT}[VVOUT_SSOPENWORKSHEET]-->VVSRVPGM{VVOUT}[VVOUT_SSOPENWORKBOOK]-->VVSRVPGM{VVOUT}[VVOUT_XLSXOPENWORKBOOK]


These errors only occur once (after the first time the Download button is pressed), and even after logging off and back on and restarting the HTTP Server, I can't get those error messages to reoccur when pressing the Download button.

We can still see the messages below in the CGI joblog when the Download button is pressed, but still nothing happens,

Directory created.
Command ended normally with exit status 0.
Command ended normally with exit status 0.
18 object links removed. 0 object links not removed.


We've not tried Valence 6.3, as we have other customers on Valence 5.1 where the Download function works ok, so we need to get it fixed for them at that level, although I appreciate that improvements/changes have been made between those versions :)

Please see VVSETTINGS data in the download link below, just in case you can see anything obvious in those settings that we may have missed:

https://indigo-customer-download-files.s3.eu-west-2.amazonaws.com/customer/MAPG+-+Map+Group/MAPG+VVSETTINGS.xlsx

Cheers,
Ian

[robert.swanson]

Just to confirm, this system is unable to successfully perform a download on any Nitro app, correct? It's not just this specific one that's posing a problem?

In more current versions of Valence, that "Failed to open XLSX workspace" would include the specific IFS path it's attempting to access, which would be helpful to know.

I'm still inclined to think the problem is in the qshell unzip function. Do you know what specific version of Valence 5.1 they're running?


Not to beat a dead horse, but temporarily installing Valence 6.3 there and trying this same download would either (1) provide more details behind the issue if the problem persists, or (2) prove there is logic in more recent releases that perhaps can be incorporated into their version of Valence 5.1.

If doing the 6.3 test is not possible, I'm afraid the only effective option we have for addressing the problem in that older release would be to get connected to their system, perhaps through an online web meeting with someone, and then step through the back-end Excel download process there to pinpoint precisely where it's failing. This of course is assuming we would have command line and debug access to this particular system. If you want to go that route, please email support at cnxcorp.com and we'll set up a call for sometime next week.

[melvig]

Ian has now fixed this by changing Portal Admin Settings, specifically unchecking the 'Ignore XLS suffix on spreadsheet names (use XLSX instead)' - the download button now works.

Thanks for your previous assistance in trying to get this working ;)