Page 1 of 1

SQL Error when running JumpStart generated code for expanding SubFile

Posted: Fri May 06, 2016 10:24 am
by mdruk1986
Hello,
I used JumpStart to generate a page at a time SubFile with a search field. The Subfile loads and displays correctly. However, when i enter a search term it throws a SQL System error which i have attached. However, when i manually code in the search term or use a variable instead of parameter marker it works. Please see rpg code below. Pretty sure the problem is how i am handling the "SearchTerm" variable but do not see how to fix it.

Any Ideas?

RPG CODE
****************************************************                                                                          
      ** Procedure: Open_SQL                            **                                                                          
      ** Procedure Prepare, Declare and Open SQL cursor **                                                                          
      **   Parms:                                       **                                                                          
      **     None                                       **                                                                          
      **   Return:                                      **                                                                          
      **     None                                       **                                                                          
      ****************************************************                                                                          
     P Open_SQL        B                                                                                                            
     D Open_SQL        PI              N                                                                                            
     D PrevSortField...                                                                                                             
     D                                     like(Ctl_Fields.Sortfield)                                                               
                                                                                                                                    
     D Statement       S           4096A   varying                                                                                  
     D SearchTerm      S             23A   varying                                                                                  
                                                                                                                                    
      /Free                                                                                                                         
                                                                                                                                    
         EXEC SQL                                                                                                                   
           Set Option                                                                                                               
             Naming = *Sys,                                                                                                         
             Commit = *None,                                                                                                        
             UsrPrf = *User,                                                                                                        
             DynUsrPrf = *User,                                                                                                     
             Datfmt = *iso,                                                                                                         
             CloSqlCsr = *EndMod ;                                                                                                  
                                                                                                                                    
       // Original jumpstart generated command immediately below                                                                    
       //SearchTerm = '%' + %Trim(Ctl_Fields.Search) + '%' ;                                                                        
         SearchTerm = '''' + %Trim(Ctl_Fields.Search) + '%''' ;                                                                     
         Statement = 'SELECT * FROM UBAMF ' ;                                                                                       
         If CTL_Fields.Search <> *Blanks ;                                                                                          
           Statement += 'WHERE UNAME LIKE ? ' ;                                                                                     
       //  Statement += 'WHERE UNAME LIKE' + SearchTerm + ' ' ; this works                                                          
       //  Statement += 'WHERE UNAME LIKE ''SMITH%'' ';         this works                                                          
         EndIf;                                                                                                                     
                                                                                                                                    
         Statement += Order_By(PrevSortField);                                                                                      
                                                                                                                                    
           Statement += ' FOR FETCH ONLY';                                                                                          
                                                                                                                                    
         EXEC SQL                                                                                                                   
           PREPARE S1 FROM :Statement ;                                                                                             
          If SQLCODE < 0;                                                                                                           
            Return *Off;                                                                                                            
          EndIf;                                                                                                                    
                                                                                                                                    
                                                                                                                                    
         EXEC SQL                                                                                                                   
           DECLARE C1 SCROLL CURSOR FOR S1 ;                                                                                        
                                                                                                                                    
          If SQLCODE < 0;                                                                                                           
            Return *Off;                                                                                                            
          EndIf;                                                                                                                    
                                                                                                                                    
         EXEC SQL                                                                                                                   
        // OPEN C1 ;                                                                                                                
             OPEN C1 USING :SearchTerm ;                                                                                            
                                                                                                                                    
        //     :SearchTerm                                                                                                          
        //     ;                                                                                                                    
                                                                                                                                    
          If SQLCODE < 0;                                                                                                           
            Return *Off;                                                                                                            
          EndIf;                                                                                                                    
                                                                                                                                    
         Db_rrn = 1;                                                                                                                
         Ctl_Fields.EnableUp=*Off;                                                                                                  
         Ctl_Fields.EnableDown=*On;                                                                                                 
         Ctl_Fields.PageNo = 1;                                                                                                     
                                                                                                                                    
         return *on;                                                                                                                
                                                                                                                                    
                                                                                                                                    
      /End-Free                                                                                                                     
     P                 E

Re: SQL Error when running JumpStart generated code for expanding SubFile

Posted: Tue May 10, 2016 2:15 pm
by Glenn
We have reproduced the issue and are working on a solution.

Glenn