Thursday 21 August 2008

Reading a CSV attachment in a server agent on Domino running on an iSeries

Oh Gentle readers here is the solution to a bit of a quandary that hit me the other day.

We have and agent that runs on a windows machine that reads a CSV sent out as an email by another system (non-domino) to a Domino mail-in database. A Server "On arrival of New Mail" agent runs , detaches the file, opens it , reads it and acts on the data inside the file on a record by record basis.

Now this all works a treat on a Windows server, but when we tried to move it to a iSeries the agent just grunted rolled over and went back to sleep, no errors but no data sourced actions performed either.

Now an iSeries is a funny if very reliable beast and what was happening was this. The email arrived in the mail box, the agent triggered and the file was saved to the what is known in the iSeries world as the IFS to the same folder as in Winders world. However the file was saved with the Coded Character Set Id (CCSID) that the AMGR jobs were running with when the save took place. For our iSeries this was 37. I could read the file from the IFS from a 5250 emulation screen. I could even drag and drop it from the iSeries to my PC and it read perfectly but the data that was being returned from the file was garbage. Initally I though it might be pure EBCDIC but not putting it through and EBCDIC convertor returned more of the same sort of garbage.

My chum Julian Robichaux wrote a nifty class that reads CSV files line by line and returns a nice easy to use arrays which needless to say I had used to read in the now detached CSV file. After some twittering and being lead down an interesting tangent by Kerr Raniney about reading the CSV directly from the MIME entity. (an idea I may investigate at a later date .. Thanks Kerr) I noticed in the help a section on what not to do in OS400 and was lead from there to the startling discovery that it was the OPEN statement that was at fault, When it changed it from
Open csvFileName For Input As csvFile
Open csvFileName For Input As csvFile CHARSET='UTF-8"

then everything started to work again!

What was happening according to the help file was that in the absence of an UTF8 or UTF16 BOM
on the file itself OR the CHARSET setting on the OPEN command the iSeries opens the file with the
CCSID tha tis on the file, in this case 37 so the iSeries was basically taking ASCII thinking it was
EBCDIC and forcing it into CCSID=37. The result was basically crap data. However setting the
CHARSET= parameter did the trick!

For all you iSeries Gnomes like me out there this is a fact worth remembering if you want to do
anything "smart" with attachments in agents.

Disqus for Domi-No-Yes-Maybe