MCH1202 Decimal Data Error when calling vvout_SQLtoSS()

[gwilburn]

I'm trying to create button to download to Excel... I haven't used procedure vvout_execSqltoSS() in quite some time.
I received the error MCH1202 decimal data error.

In debug I can see the error is occurring in the WriteSS procedure within VVOUT (statement 429100).. the value in vvcol.colwidth is blanks (40404).

Am I not calling the procedure correctly?

[robert.swanson]

Are you initializing the vvsscol-based data structure in your calling program? Have a look at EXSS01, the sendCustRecsSS2. How does the Col data structure there compare to your code?

[gwilburn]

OK... I did not have the INZ keyword on the column data structure. I added that and no longer receive the hard error, but I cannot open the downloaded excel file
image.png?

here's my code (I kept it simple):

dcl-s stmt varchar(1024);
?dcl-ds colHdr likeds(vvSSCol) dim(4) inz;

gCompany = GetAppVar('companyNumber');
lEntity = GetAppVar('customerNumber');

colHdr(1).sqlName='ODCOM#';
colHdr(1).heading='COM NBR';
colHdr(2).sqlName='ODWHS#';
colHdr(2).heading='WHS NBR';
colHdr(3).sqlName='ODORD#';
colHdr(3).heading='ORDER NBR';
colHdr(4).sqlName='ODPRT#';
colHdr(4).heading='PART NBR';

stmt = 'select odcom#, odwhs#, odord#, odprt# ' +
'from oeordlod ' +
'where odcom#=' + gCompany + ' and ' +
'odent#=' + lEntity + ' and odlstc in (' + SQ + 'OP' + SQ +
', ' + SQ + 'BO' + SQ + ') order by odord#, odseq#';

vvOut.download = '1';
vvOut.file = 'Open_Order_Detail.xlsx';
vvOut_execSqltoSS(vvOut:stmt:%addr(colHdr):%elem(colHdr));

return;
?

If I edit the output with Notepad++ I see the following at the end of the file:

Cache-Control: no-cache
Content-type: application/json

 {"success" : true }  ?

If I manually remove that, I can open it in Excel.

So I added:

vvOut.contentType = 'application/vnd';

Are these parameters documented somewhere? I couldn't find them in the online doc.
I would like to:
1. Freeze the column headings (top) row.
2. Autosize the columns if possible
3. Have NULL dates download as blank instead of 1900-01-01 (alternatively, have zero suppress because my dates are actually numeric YYYYMMDD)

[robert.swanson]

Could you check what extension type XLSX is set to in file VVFILEXT? It should be *XLSX, which is a shorthand trigger in VVOUT for "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet". Sounds like maybe the XLSX record is either missing or set to application/json.

There isn't a whole lot of fancy functionality built in to the backend spreadsheet generation. All the documentation for the current iteration of vvOut_execSQLtoSS is here.

For your numeric dates you might want to format them as ISO dates and then use an appropriate date Style ID, or you could cast them as character into whatever date format you want, leaving it blank if zero.

[gwilburn]

The XLSX record in VVFILEXT is *XLSX.

I removed the code and it is working now... weird

vvOut.contentType = 'application/vnd';

I may have been setting the response ("success":"true") previously. Or maybe something was leftover in the activation group?

Thanks for the link.

[robert.swanson]

Bit of a head-scratcher as to what happened there, but glad you got it working regardless.

[gwilburn]

Something odd is occurring... The resulting excel file will sometimes open and sometimes not.

Everything is running and completing as it should. However, for some customers the Excel file won't open. Open_Order_Detail(1).xlsx says it is corrupt. However, Open_Order_Detail.xlsx is fine.

I went to the logs and retrieved the SQL statement the resulted in the corrupt XL file - ran it in Run Sql Scripts on my PC and it works.

image.png?
image.png?

[robert.swanson]

What Valence build is this phenomenon occurring on? Can you reliably replicate the problem?

[gwilburn]

We are on 6.3.20250325.1

I can reproduce this with a specific customer (at least for now).

[robert.swanson]

If it's only an issue on a certain customer, running through the same back-end code, then you likely have a data-specific problem at play -- i.e., an unrecognizable hex character inside one of the columns in your SQL statement, which is making Excel choke.

I would suggest you try adjusting your SQL statement to, one at a time, replace character columns with hard-coded blanks until you find it suddenly starts working in Excel after you remove one of the columns. Then you can inspect the contents of that specific column and look for odd characters. Sometimes you can spot the culprit record by running the statement through STRSQL in a 5250 terminal, as invalid characters are often rendered in reverse image and/or show funky symbols.

[gwilburn]

I reduced it to a single field of data... one column, order number, which is a character column.

I get a corrupt XLSX file for one customer and a valid XLSX file for another.

I'm baffled...

[robert.swanson]

Try looking at the hex values for that order number column. i.e., do something like this:

select ordnbr, hex(ordnbr) as hex_ordnbr
from xxxxxx
where custnbr=yyyyy

There must be a record for that customer with something embedded in the order number column that Excel doesn't like. Valid values for numbers 0 through 9 would be hex F0 through F9, and hex 40 for blank spaces. Assuming your order numbers are strictly numeric, any other hex values would be suspect. If your order numbers can legitimately include letters then you'd need to adjust what you're looking for (A through Z would be hex C1 through E9).

[gwilburn]

I have abandoned the direct download (leaving the code in my button program). Instead, I'm displaying the results in a grid and allowing the user to download the results from the grid. That works fine.

While i would like to know what's going on here... at this point I have a solution.?

FYI - this issue was occurring on more than just one customer - it seemed like 50% of the time I received the error.

There are only two orders (multiple lines) for this customer.
image.png?


I went a step further and checked every digit of every order using the following this SQL statement
odord# is char(8).

I received no results for the following:

select odord#, hex(odord#) as hex_ordnbr
from oeordlod
where
substr(odord#,1,1) < x'F0' or
substr(odord#,1,1) > x'F9' or
substr(odord#,2,1) < x'F0' or
substr(odord#,2,1) > x'F9' or
substr(odord#,3,1) < x'F0' or
substr(odord#,3,1) > x'F9' or
substr(odord#,4,1) < x'F0' or
substr(odord#,4,1) > x'F9' or
substr(odord#,5,1) < x'F0' or
substr(odord#,5,1) > x'F9' or
substr(odord#,6,1) < x'F0' or
substr(odord#,6,1) > x'F9' or
substr(odord#,7,1) < x'F0' or
substr(odord#,7,1) > x'F9' or
substr(odord#,8,1) < x'F0' or
substr(odord#,8,1) > x'F9'

?

[robert.swanson]

Kind of a head-scratcher there, and I'd like to understand the root cause too...

If you don't mind, could you email to support a save file of your underlying physical file(s) (ideally with just the handful of records necessary to replicate the problem) and the RPG code you're using to create the file? Then we can do a deep dive on the cause and (hopefully) get this sorted out.

[gwilburn]

Done.
I also included the code from my button program.

Thanks