Re: SQL function - why it doesn't work

[Scott Klement <midrange-l@xxxxxxxxxxxxxxxx>]

Hello Tomasz,

It's great to see that you are trying to use some new techniques, 
instead of simply sticking with RPG III.  It's always encouraging when 
you see that people are trying to improve their skills instead of just 
sticking with what they learned 15 years ago.

Unfortunately, sometimes when we try to mix old techniques with newer 
ones, we run into trouble.  It might be worth taking things a step 
further and using a few more RPG IV techniques in your code, replacing 
some of the old RPG III stuff you still use.

There are several problems with your code.  The one that's causing you 
the most grief is the fact that you are using the MOVEL op-code with a 
VARYING field.  MOVEL was invented perhaps 30 years before VARYING, and 
it doesn't really understand VARYING.  MOVEL will never change the 
length of a VARYING field.

On the last line of your program, you have this:

C                   Movel     TXTO          gsqTXTT

As I mentioned, MOVEL never changes the length of a VARYING field. 
gsqTXTT had a length of 0 when the program started, and MOVEL won't 
change that, it'll keep it at 0.  So nothing actually gets moved into 
gsqTXTT in this statement.  Using RPG III opcodes with VARYING fields 
will usually leave you frustrated.

The quickest fix to your program is to use EVAL instead.  Remove the 
final MOVEL and use EVAL in it's place as follows:

C                   eval      gsqTXTT = %trimr(TXTO)

That's the error that is causing you problems.  However, there are 
several other problems with your code that you may not have noticed (and 
probably wouldn't find during testing):

1) You have the sqlFinalFlg flag coded incorrectly.  It should be coded 
as "10i 0" instead of "5i 0".

2) You have specified "allow parallel" which I don't think should be 
used with code that's not threadsafe.  I suggest changing it to 
"disallow parallel"

3) Your scratchpad parameter (sqlScrPad) is coded too short.  The system 
always adds an Integer (10i 0) containing the length to the start of 
this field, so you should have coded it at 254 long.  Though, I don't 
understand why you are using a scratchpad in this program.  (You don't 
appear to use it for anything... so you'd never see the error...)

4) Your program starts by moving a varying (Varchar) field into a 
fixed-length field.  That means you lose the ability to detect the 
original length of the field! That seems silly.  If this were my code, 
I'd use VARYING throughout.  (Unfortunately, that also means replacing 
the remainder of the MOVEL and XLATE opcodes with modern replacements)

5) You repeatedly do MOVEL *BLANKS and then on the next line of code 
replace the blanked out field with something else.  This isn't really a 
bug, but it's a waste of CPU cycles.  why fill it with blanks, then 
overwrite the blanks with something else?  Why not just overwrite the 
data to begin with?

6) You set on LR in a NOMAIN subprocedure.  This isn't really a bug, but 
it doesn't do anything, since there's no RPG logic cycle to check for 
the LR indicator.  So setting it on does nothing.  (It just looks foolish)


If you take all of these things into consideration, the resultant code 
looks like this:  (I won't repeat the D-specs from what you had -- they 
just be self-explanatory...)

     D TXT             S                   like(gsqTXTF)

     C                   eval      TXT = %xlate(PO:LAT:gsqTXTF)
     C                   eval      TXT = %xlate(LOW:CAP:TXT)
     C                   eval      TXT = %xlate(SPC:BLK:TXT)
     C                   eval      gsqTXTT = TXT

Or better yet in free-format:

     D TXT             S                   like(gsqTXTF)

      /free
         TXT = %xlate(POL:LAT:gsqTXTF);
         TXT = %xlate(LOW:CAP:TXT);
         TXT = %xlate(SPC:BLK:TXT);
         gsqTXTT = TXT;
      /end-free

Good luck!


Tomasz Skorża wrote:
> Hi
>
> I created a function ZIPTXT which should "clean" text (change polish 
> national chars to lating chars, and change all special chars to space)
>
> code is:
>
> H NoMain Option(*SrcStmt : *NoDebugIO)                   
>                                                           
>                                                        
> D zipTXT          pr                                     
> D  gsqTXTF                     100    Varying            
> D  gsqTXTT                     100    Varying            
> D  sqlNull1                      5i 0                    
> D  sqlOutNull                    5i 0                    
> D  sqlState                      5                       
> D  sqlFuncNm                   517    Varying            
> D  sqlSpecName                 128    Varying            
> D  sqlMsgTxt                    70    Varying            
> D  sqlScrPad                   250                       
> D  sqlFinalFlg                   5i 0                    
>                                                              
> P zipTXT          b                   
> Export                                               
>                                                                                             
>
> D zipTXT          
> pi                                                                       
> D  gsqTXTF                     100    
> Varying                                              
> D  gsqTXTT                     100    
> Varying                                              
> D  sqlNull1                      5i 
> 0                                                      
> D  sqlOutNull                    5i 
> 0                                                      
> D  sqlState                      
> 5                                                         
> D  sqlFuncNm                   517    
> Varying                                              
> D  sqlSpecName                 128    
> Varying                                     
> D  sqlMsgTxt                    70    
> Varying                                     
> D  sqlScrPad                   
> 250                                                
> D  sqlFinalFlg                   5i 
> 0                                             
>                                                                                    
>
>  *     Tables for 
> conversion                                                      
>                                                                                    
>
> D POL             C                   
> 'ąęćśżźńółĄĘĆŚŻŹŃÓŁ'                        
> D LAT             C                   
> 'aecszznolAECSZZNOL'                        
> D LOW             C                   
> 'aąbcćdeęfghijklłmnńoóprsśtuvwxyzźż'        
> D CAP             C                   
> 'AĄBCĆDEĘFGHIJKLŁMNŃOÓPRSŚTUVWXYZŹŻ'        
> D SPC             C                   
> '~`!@#$%˘&*()_-+={}\|;:",<>?/'              
> D BLK             C                   '                            
> '              
>                                                                                    
>
> C                   Movel     *blanks       TXTI            
> 100                   
> C                   Movel     *blanks       TXTO            
> 100                   
> C                   Movel     gsqTXTF       
> TXTI                                  
>                                                                                    
>
> C     POL:LAT       XLate     TXTI          
> TXTO                                  
> C                   Movel     *blanks       
> TXTI                                  
> C                   Movel     TXTO          
> TXTI                            
> C                   Movel     *blanks       
> TXTO                            
>                                                                              
>
> C     LOW:CAP       XLate     TXTI          
> TXTO                            
> C                   Movel     *blanks       
> TXTI                            
> C                   Movel     TXTO          
> TXTI                            
> C                   Movel     *blanks       
> TXTO                            
>                                                                              
>
> C     SPC:BLK       XLate     TXTI          
> TXTO                            
>                                                                              
>
> C                   Movel     TXTO          
> gsqTXTT                         
>                                                                              
>
> C                   SetOn                                        
> LR         
> C                   Return                                         
>                                                                              
>
> P zipTXT          e                        
>
> Then I created it with command:
>
>                                                                        
> 1) CRTRPGMOD MODULE(LIB/ZIPTXT) SRCFILE(LIB/SQLSRC) 
> DBGVIEW(*LIST)             
>
> 2) CRTSRVPGM SRVPGM(LIB/ZIPTXT) 
> EXPORT(*ALL)                                      
>
> 3) CREATE FUNCTION LIB/ZIPTEXT 
> (varCHAR(100))                                     
>    RETURNS varCHAR (100) LANGUAGE RPGLE DETERMINISTIC NO 
> SQL                           
>    RETURNS NULL ON NULL INPUT NO EXTERNAL ACTION ALLOW PARALLEL 
> FINAL                  
>    CALL SCRATCHPAD 250 PARAMETER STYLE DB2SQL EXTERNAL 
> NAME                            
>    
> 'LIB/ZIPTXT(ZIPTXT)'                                                            
>
>                                     
> When I calling this function it returns blank text with 100-chars length.
>
> What is wrong? 
>