Hi Devlyn,
One of our developers just ran into a similar problem with this pattern. We are on IBM I 7.1 and Plex 6.1. He is having trouble with a zoned number. Here is what he told me about the problem. This was after he applied the fix that Ari mentioned for zoned numbers.
BEGIN EMAIL
Well, it sort of fixed it. I thought I’d put it in an email to make it clearer.
If the first character after the number has the 4 upper bits as x’40’ then it works. If I change the “OTHER” part to put an ‘E’ in there it works better. However I have an example where it’s just plain a longer number. The zoned number I want is ‘02’ but what’s in the buffer is ‘0281’ or in hex F0F2F8F1000000… So you just can’t find the end by checking the upper bits.
It looks like each variable returned from the sql stmt gets a 1000-byte allocated space. It happens here:
* API Call Source code: MMA_CORE/DynamicSQLPrepare2
C Eval SqlN=1
C/exec sql
C+ DESCRIBE SQLExtract INTO :SQLDA USING ANY
C/end-exec
C Eval SqlN=SqlD
C Eval SQLABC=SqlN*%Len(SqlVar)+16
C/exec sql
C+ Describe SQLExtract Into :SQLDA Using ANY
C/end-exec
C Z-ADD 0 Element 5 0
C DoU Element=SqlD
C Eval Element=Element+1
C Eval SqlVar=SQL_VAR(Element)
C Alloc 10000 SqlData
C Alloc 1 SqlInd
C Eval SQL_VAR(Element)=SqlVar
C EndDo
It also looks like when this fails, the 1000-byte SqlData area isn’t zeroed out. Not knowing how to do that, I assigned blanks to it and since the other guy’s fix checks for x’40’, it catches it and stops at the right time. It doesn’t break other fields either, but you should probably tell me how to put zeros in the buffer. I declared a variable
D $tmp S 1024A based(tmpptr)
And then added this code after the Alloc statement:
C Eval tmpptr = sqldata
C eval $tmp = ' '
Which fills it with x’40’ bytes.
Okay, so if you tell me how to put zeros in there I’ll experiment a bit more and then you can decide what to do next.
END EMAIL
I told him how to fill it with all x'00', but I haven't heard back from him yet. I tried searching on the internet to see if I could find examples of other people using the SQLDA with dynamic fields. About the best answer I could find was in this thread http://itknowledgeexchange.techtarget.com/itanswers/embedded-sql-in-rpg-ile/. The poster named Sloopy said he had over 1700 lines of code that he used to handle the conversion of the data from the SQLDA. He didn't post all of his code, but he said he could send it if someone requested it. This thread is from 2008.
Dean Eshleman,
Everence Financial