Profound Logic Forums

Posted: **Wed May 28, 2014 5:06 pm**

We are wanting to use an RPGsp program in conjunction with the Dynamic Auto-Complete URL. We would eventually like to grab multiple choices from multiple files using embedded SQL but I just wanted to start simple and just use one file.

Do I create the output in the RPGsp basically like a dropdown?:

[new Option("thetext", "thevalue"),new Option("thetext","thevalue")]

Do I grab everything from the file using the SQL or do I limit it?

I've succeeded in completely confusing myself on this one! :-)

We've used the Database-Driven property with no problems but wanted to get a little more advanced with the choices.

Thanks in advance!

Posted: **Thu May 29, 2014 1:09 am**

Eric,

The output would be quite different from a dropdown... there are no "options" in the case of an auto-complete.

It's impotrant to understand that with an autocomplete, your program will be called each time the user hits a key in the textbox, and you will be expected to return the results that fit the string (called 'query') that they've typed so far. So if the user starts typing 'abc', then your RPGsp program will be called 3 times, first with 'a', second with 'ab', and third with 'abc'. For each call, you will provide the records that match what the string they typed.

Don't send the entire file -- if you do that, it'll look like the entire file matched what they typed so far! (Not to mention that it probably won't perform very well.)

So...
1) You'll need to use RPGspIn('query') to get the string that they've typed (so far).
2) You'll need to format a JSON document to send back in response, it should contain the results of the query.
3) or, if there's an error, you'll send back a JSON document with an error.

The JSON document for an error should look like this:

Code: Select all

{
  "success": false,
  "errorId": "CPF9898",         <-- put whatever msgid you want here
  "errorText": "Put your error message here",
  "errorText2": "Put 2nd level error text here"
}

The JSON document for a successful request should look like this:

Code: Select all

{
  "success": true,
  "response": {
    "results": [{
      "fieldname1": "field1 data for first choice",
      "fieldname2": "field2 data for first choice"
    },
    {
      "fieldname1": "field1 data for 2nd choice",
      "fieldname2": "field2 data for 2nd choice"
    },
    {
      "fieldname1": "field1 data for 3rd choice",
      "fieldname2": "field2 data for 3rd choice"
    }]
  }
}

Hope that makes sense. The data is JSON, so if there are any special characters in your strings, you should escape it according to JSON rules.

Also, The [] and {} characters vary a lot on different EBCDIC code pages, so I would recommend that you use Unicode to encode these in your RPG program, and use %CHAR() to convert them to EBCDIC at runtime, that way they'll always get the proper EBCDIC value for the EBCDIC code page that's being used to run the program.

Not sure if I might be missing any details. Maybe I should code an example...?

Posted: **Thu May 29, 2014 9:20 am**

Thanks Scott.

Would love an example if you have the time!

Posted: **Thu May 29, 2014 11:42 am**

Okay, here's a quick example. Obviously, the SQL statement and field names will be totally different on your box... so I didn't try to make that too realistic, I just threw it together quick.

Code: Select all

<SCRIPT language=RPGLE>
     H bnddir('PROFOUNDUI')                                                                                                         
     H option(*srcstmt: *nodebugio: *noshowcpy)                                                                                     
                                                                                                                                    
      /copy qrpgleinc,puisyncr                                                                                                      
                                                                                                                                    
     D LBRACE          C                   u'007b'                                                                                  
     D RBRACE          C                   u'007d'                                                                                  
     D RSQB            C                   u'005d'                                                                                  
     D LSQB            C                   u'005b'                                                                                  
                                                                                                                                    
     D errMsg          s            256a   varying                                                                                  
     D query           s             50a   varying                                                                                  
     D first           s              1n                                                                                            
                                                                                                                                    
     D C1              ds                  qualified                                                                                
     D  Keyval                       10a                                                                                            
     D  Desc                         30a                                                                                            
                                                                                                                                    
                                                                                                                                    
       RPGspSetCntType('text/plain');                                                                                               
       RPGspSetHeader('Expires: Mon, 01, Jan 1996 01:01:01 GMT');                                                                   
       RPGspSetHeader('Cache-control: no-cache');                                                                                   
       RPGspSetHeader('Cache-control: no-store');                                                                                   
                                                                                                                                    
       errMsg = SyncJob();                                                                                                          
       if %len(errMsg)>0 and errMsg<>*blanks;                                                                                       
          RPGspOut( %char(LBRACE)                                                                                                   
                  + '"success": false,'                                                                                             
                  + '"errorId": "CPF9897",'                                                                                         
                  + '"errorText": "SyncJob failed. See second level text.",'                                                        
                  + '"errorText2": "' + RPGspReplace(errMsg: '"': '\"') + '"'                                                       
                  + %char(RBRACE) );                                                                                                
          RPGspDone();                                                                                                              
          ResetJob();                                                                                                               
          *inlr = *on;                                                                                                              
          Return;                                                                                                                   
       endif;                                                                                                                       
                                                                                                                                    
       query = '%' + RPGspIn('query') + '%';                                                                                        
                                                                                                                                    
       exec SQL declare C1 cursor for                                                                                               
          select keyval, desc                                                                                                       
            from SKTEST/DBDRIVER                                                                                                    
           where KEYVAL like :query;                                                                                                
                                                                                                                                    
       exec SQL open C1;                                                                                                            
       exec SQL fetch next from C1 into :C1;                                                                                        
                                                                                                                                    
       if %subst(Sqlstt:1:2)<>'00'                                                                                                  
          and %subst(Sqlstt:1:2)<>'01'                                                                                              
          and %subst(sqlstt:1:2)<>'02';                                                                                             
          RPGspOut( %char(LBRACE)                                                                                                   
                  + '"success": false,'                                                                                             
                  + '"errorId": "CPF9897",'                                                                                         
                  + '"errorText": "SQL state ' + sqlstt + ' during query",'                                                         
                  + '"errorText2": "SQL state ' + sqlstt + ' during query"'                                                         
                  + %char(RBRACE) );                                                                                                
          RPGspDone();                                                                                                              
          ResetJob();                                                                                                               
          *inlr = *on;                                                                                                              
          Return;                                                                                                                   
       endif;                                                                                                                       
                                                                                                                                    
       RPGspOut( %char(LBRACE)                                                                                                      
               + '"success": true, '                                                                                                
               + '"response": '                                                                                                     
               + %char(LBRACE) + '"results": '                                                                                      
               + %char(LSQB) );                                                                                                     
       first = *on;                                                                                                                 
                                                                                                                                    
       dow sqlstt='00000' or %subst(sqlstt:1:2)='01';                                                                               
          if not first;                                                                                                             
            RPGspOut(',');                                                                                                          
          endif;                                                                                                                    
          first = *off;                                                                                                             
          RPGspOut( %char(LBRACE)                                                                                                   
                  + '"KEYVAL": "' + RPGspReplace(%trim(C1.KeyVal): '"': '\"')                                                       
                  + '", '                                                                                                           
                  + '"DESC": "' + RPGspReplace(%trim(C1.Desc): '"': '\"')                                                           
                  + '"'                                                                                                             
                  + %char(RBRACE) );                                                                                                
          exec SQL fetch next from C1 into :C1;                                                                                     
       enddo;                                                                                                                       
                                                                                                                                    
       exec SQL close C1;                                                                                                           
                                                                                                                                    
       RPGspOut( %char(RSQB)                                                                                                        
               + %char(RBRACE)                                                                                                      
               + %char(RBRACE) );                                                                                                   
                                                                                                                                    
       RPGspDone();                                                                                                                 
       ResetJob();                                                                                                                  
       *inlr = *on;                                                                                                                 
       return;                                                                                                                      
</SCRIPT>

Posted: **Thu May 29, 2014 11:49 am**

Thanks Scott!!! I'll review this and see if I can implement it into what we are doing.

Posted: **Thu May 29, 2014 12:29 pm**

I have the RPGsp coded and compiled. How would the URL look when calling the program? The variable that I am keying data into is WPRT. something like this?:

js: "/icsui/icsprtcpl.pgm?" + get('WPRT')

Thanks!!!!!!!

Posted: **Thu May 29, 2014 12:36 pm**

I'm assuming that this is for the auto-complete feature of a textbox widget. If not, please ignore everything I've said so far :-)

There's a "choices url" property on the textbox. What you do is point that to your RPGsp program. I don't see why there'd be a need to retrieve the contents of the field or build the path name dynamically with a script? You'd only do that if the name of your program varies, so you need to calculate the path name at run-time.

Just set the choices URL to: /icsui/icsprtcpl.pgm (if that's the correct URL?). No need for js:, get(), or calculating anything on the fly.

This assumes, of course, that you've set up your Apache config with an appropriate ScriptAlias? But, as it sounds like you've done this previously for a dropdown, I'm assuming you have taken care of that part.

Posted: **Thu May 29, 2014 3:19 pm**

Scott,

When I tried using my RPGsp program, I'm not getting anything. Here is my RPGsp code:

Code: Select all

<SCRIPT language=RPGLE>
     H bnddir('PROFOUNDUI')                                                                                                         
     H option(*srcstmt: *nodebugio: *noshowcpy)                                                                                     
                                                                                                                                    
      /copy profoundui/qrpgleinc,puisyncr                                                                                           
                                                                                                                                    
     D LBRACE          C                   u'007b'                                                                                  
     D RBRACE          C                   u'007d'                                                                                  
     D RSQB            C                   u'005d'                                                                                  
     D LSQB            C                   u'005b'                                                                                  
                                                                                                                                    
     D errMsg          s            256a   varying                                                                                  
     D query           s             50a   varying                                                                                  
     D first           s              1n                                                                                            
     D count           s              5  0                                                                                          
                                                                                                                                    
     D C1              ds                  qualified                                                                                
     D  PrtNbr                       15a                                                                                            
     D  Desc                         30a                                                                                            
                                                                                                                                    
                                                                                                                                    
       RPGspSetCntType('text/plain');                                                                                               
       RPGspSetHeader('Expires: Mon, 01, Jan 1996 01:01:01 GMT');                                                                   
       RPGspSetHeader('Cache-control: no-cache');                                                                                   
       RPGspSetHeader('Cache-control: no-store');                                                                                   
                                                                                                                                    
       errMsg = SyncJob();                                                                                                          
       if %len(errMsg)>0 and errMsg<>*blanks;                                                                                       
          RPGspOut( %char(LBRACE)                                                                                                   
                  + '"success": false,'                                                                                             
                  + '"errorId": "CPF9897",'                                                                                         
                  + '"errorText": "SyncJob failed. See second level text.",'                                                        
                  + '"errorText2": "' + RPGspReplace(errMsg: '"': '\"') + '"'                                                       
                  + %char(RBRACE) );                                                                                                
          RPGspDone();                                                                                                              
          ResetJob();                                                                                                               
          *inlr = *on;                                                                                                              
          Return;                                                                                                                   
       endif;                                                                                                                       
                                                                                                                                    
       query = '%' + RPGspIn('query') + '%';                                                                                        
                                                                                                                                    
       exec SQL declare icmstcsr cursor for                                                                                         
          select imprt#, imdsc                                                                                                      
            from ICMST                                                                                                              
           where IMPRT# like :query;                                                                                                
                                                                                                                                    
       exec SQL open icmstcsr;                                                                                                      
       exec SQL fetch next from icmstcsr into :C1;                                                                                  
                                                                                                                                    
       if %subst(Sqlstt:1:2)<>'00'                                                                                                  
          and %subst(Sqlstt:1:2)<>'01'                                                                                              
          and %subst(sqlstt:1:2)<>'02';                                                                                             
          RPGspOut( %char(LBRACE)                                                                                                   
                  + '"success": false,'                                                                                             
                  + '"errorId": "CPF9897",'                                                                                         
                  + '"errorText": "SQL state ' + sqlstt + ' during query",'                                                         
                  + '"errorText2": "SQL state ' + sqlstt + ' during query"'                                                         
                  + %char(RBRACE) );                                                                                                
          RPGspDone();                                                                                                              
          ResetJob();                                                                                                               
          *inlr = *on;                                                                                                              
          Return;                                                                                                                   
       endif;                                                                                                                       
                                                                                                                                    
       RPGspOut( %char(LBRACE)                                                                                                      
               + '"success": true, '                                                                                                
               + '"response": '                                                                                                     
               + %char(LBRACE) + '"results": '                                                                                      
               + %char(LSQB) );                                                                                                     
       first = *on;                                                                                                                 
       clear count;                                                                                                                 
                                                                                                                                    
       dow sqlstt='00000' or %subst(sqlstt:1:2)='01';                                                                               
          if not first;                                                                                                             
            RPGspOut(',');                                                                                                          
          endif;                                                                                                                    
          first = *off;                                                                                                             
          RPGspOut( %char(LBRACE)                                                                                                   
                  + '"PRTNBR": "' + RPGspReplace(%trim(C1.PrtNbr): '"': '\"')                                                       
                  + '", '                                                                                                           
                  + '"DESC": "' + RPGspReplace(%trim(C1.Desc): '"': '\"')                                                           
                  + '"'                                                                                                             
                  + %char(RBRACE) );                                                                                                
                                                                                                                                    
          count += 1;                                                                                                               
                                                                                                                                    
          if count >= 10;                                                                                                           
            leave;                                                                                                                  
          endif;                                                                                                                    
                                                                                                                                    
          exec SQL fetch next from icmstcsr into :C1;                                                                               
       enddo;                                                                                                                       
                                                                                                                                    
       exec SQL close icmstcsr;                                                                                                     
                                                                                                                                    
       RPGspOut( %char(RSQB)                                                                                                        
               + %char(RBRACE)                                                                                                      
               + %char(RBRACE) );                                                                                                   
                                                                                                                                    
       RPGspDone();                                                                                                                 
       ResetJob();                                                                                                                  
       *inlr = *on;                                                                                                                 
       return;                                                                                                                      
</SCRIPT>

I have also attached my JSON capture (CTRL-F9). The screen format is SCR02 and the field is WPRT. Don't get any errors just no auto-complete list.

Thanks!!!!!

Posted: **Thu May 29, 2014 3:55 pm**

Eric,

Can you use your browser's developer tools to check what is sent back from the server? Personally, I use Firebug (though, there are similar tools in other browsers like Chrome and IE). in the 'Net' panel it shows each request... find your program's name in there, and see what is being received back from it.

thanks

Posted: **Thu May 29, 2014 4:31 pm**

Looks like I'm getting a 500 internal server error. The request URL is showing this:

http://ourhostname:8080/icsui/icsprtcpl ... A2506A39A5

Suggestions?

Thanks for the help!!!!!

Profound Logic Forums

Auto-Complete and RPGsp

Auto-Complete and RPGsp

Re: Auto-Complete and RPGsp

Re: Auto-Complete and RPGsp

Re: Auto-Complete and RPGsp

Re: Auto-Complete and RPGsp

Re: Auto-Complete and RPGsp

Re: Auto-Complete and RPGsp

Re: Auto-Complete and RPGsp

Re: Auto-Complete and RPGsp

Re: Auto-Complete and RPGsp