Use of Salesforce APIs on IBM i – Retrieve signature file and store it on IFS

Get OAuth Token from Salesforce’s Token API

This SQL function can be used to retrieve OAuth token from Salesforce application.

CREATE OR REPLACE FUNCTION 
SALESFORCE_GET_OAUTH_TOKEN 
(                          
inCompany varchar(20)       
)                          
                           
RETURNS varchar(512)       
LANGUAGE SQL               
NO EXTERNAL ACTION         
SPECIFIC SFGETTOKEN      
READS SQL DATA                   
NOT DETERMINISTIC                
CALLED ON NULL INPUT             
                                 
SET OPTION  ALWBLK = *ALLREAD,   
ALWCPYDTA = *OPTIMIZE,           
COMMIT = *NONE,                  
DECRESULT = (31, 31, 00),        
DFTRDBCOL = *NONE,               
DYNDFTCOL = *NO,                 
DYNUSRPRF = *USER,               
SRTSEQ = *HEX,                   
DBGVIEW = *SOURCE                
                                 
BEGIN                            
  DECLARE outVAL char(512);      
  DECLARE client_id char(256) DEFAULT 'my client ID';   
  DECLARE client_secret char(32) DEFAULT 'my secret';
  DECLARE url char(512) DEFAULT 'https://test.salesforce.com/services/oauth2/token';         
  DECLARE username char(64) DEFAULT 'my user name';                                                 
  DECLARE password char(64) DEFAULT 'my password';                                                 
  DECLARE RTNCODE INTEGER DEFAULT 0;                                         
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET RTNCODE = SQLCODE;           
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET RTNCODE = SQLCODE;              
                                                                             
  BEGIN                                                                      
    SET RTNCODE = 0;                                                         
    SET outVAL = ' ';                                                        
                                                                                                                                        
    select json_val(SYSTOOLS.JSON2BSON(RESPONSEMSG),'access_token','s') as me
    INTO outVAL                                                              
    FROM TABLE(                                                              
       SYSTOOLS.HTTPPOSTCLOBVERBOSE(                                         
    url,                                                                    
    cast('<httpHeader>                                                        
      <header name="Content-Type" value="application/x-www-form-urlencoded" />
      </httpHeader>' AS CLOB(10K)),                                           
    cast('grant_type=password&client_id=' || client_id || '&client_secret='   
    || client_secret || '&username=' || username || '&password=' || password  
     as clob(10k))) )as e;                                                    
                                                                              
    IF RTNCODE = 0 THEN                                                       
      return outVAL;                                                          
    ELSE                                                                      
      return CAST(rtnCode AS CHAR(200));                                      
    END IF;                                                                   
  END;                                                                        
                                                                              
END;

Get Attachment ID from Salesforce using SQL statement in the query string

CREATE OR REPLACE FUNCTION    
SALESFORCE_GET_ATTACHMENT_ID  
(                             
inCompany varchar(2),         
inToken varchar(256),         
inParentId varchar(256)       
)                             
                              
RETURNS varchar(512)          
LANGUAGE SQL                  
NO EXTERNAL ACTION            
SPECIFIC SFGETATTID 
MODIFIES SQL DATA             
NOT DETERMINISTIC             
CALLED ON NULL INPUT          
                              
SET OPTION  ALWBLK = *ALLREAD,
ALWCPYDTA = *OPTIMIZE,        
COMMIT = *NONE,               
DECRESULT = (31, 31, 00),     
DFTRDBCOL = *NONE,                                                    
DYNDFTCOL = *NO,                                                      
DYNUSRPRF = *USER,                                                    
SRTSEQ = *HEX,                                                        
DBGVIEW = *SOURCE                                                     
                                                                      
BEGIN                                                                 
  DECLARE reqHeader XML;                                              
  DECLARE resHeader XML;                                              
  DECLARE resBody CLOB(2G);                                           
  DECLARE resCode INTEGER;                                            
  DECLARE resMessage VARCHAR(200);                                    
  DECLARE rtnCode INTEGER DEFAULT 0;                                  
  DECLARE url VARCHAR(512);                                           
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET rtnCode = SQLCODE;    
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET RTNCODE = SQLCODE;       
                                                                      
  BEGIN                                                               
                                                                      
    SET url = 'https://myApp.csXX.my.salesforce.com/'
           || 'services/data/vXX.X/query/'                                  
           || '?q=SELECT+Id+FROM+Attachment+where+Name+like+'               
           || '%27%25signature%25%27+and+ParentId+=+%27'                    
           || trim(inParentId) || '%27';                                    
                                                                            
    -- Generate request header                                              
    SET ReqHeader = (                                                       
    SELECT XMLGROUP(requestHeader.hname as "name",                          
                    requestHeader.hvalue as "value"                         
                    OPTION ROW "header"                                     
                    ROOT "httpHeader" as ATTRIBUTES)                        
    FROM                                                                    
    (VALUES                                                                 
      ('Authorization','Bearer ' || TRIM(inToken)),                         
      ('Host', 'myApp.csXX.my.salesforce.com')             
    ) requestHeader(hname,hvalue));                                         
                                                                            
    -- Call the API                                                         
    SELECT                                                                  
    json_val(SYSTOOLS.JSON2BSON(ifnull(RESPONSEMSG, '{}')),'records.Id','s')
      as me, xt.*                                          
    INTO resBody, resCode, resMessage                      
    FROM TABLE(                                            
     SYSTOOLS.HTTPGETCLOBVERBOSE(url, ReqHeader)) as er    
    CROSS JOIN                                             
    XMLTABLE('httpHeader'                                  
            PASSING                                        
            --ifnull(er.RESPONSEHTTPHEADER,cast(null as XML))
            xmlparse(document er.RESPONSEHTTPHEADER)
            COLUMNS                                        
            code INTEGER PATH '@responseCode',             
            message VARCHAR(200) PATH 'responseMessage'    
            ) as xt;                                       
                                                           
    IF resCode = '200' THEN                                
      return trim(resBody);                                
    ELSE                                                   
      return resCode;                                      
    END IF;                                                
                                                           
  END;                                                     
END;

Get Attachment Attributes

CREATE OR REPLACE PROCEDURE             
SALESFORCE_GET_ATTACHMENT_ATTR          
(                                       
IN inCompany CHAR(2),                   
IN inToken CHAR(256),                   
IN inParentId CHAR(256),                
INOUT outSuccess CHAR(1),               
INOUT outAttID CHAR(256),               
INOUT outAttName CHAR(256),             
INOUT outAttLen INTEGER,                
INOUT outMessage CHAR(512)              
)                                       
                                        
LANGUAGE SQL                            
NO EXTERNAL ACTION                      
SPECIFIC SFGETATTAT         
MODIFIES SQL DATA                       
NOT DETERMINISTIC                       
CALLED ON NULL INPUT       

SET OPTION  ALWBLK = *ALLREAD,         
ALWCPYDTA = *OPTIMIZE,                 
COMMIT = *NONE,                        
DECRESULT = (31, 31, 00),              
DFTRDBCOL = *NONE,                     
DYNDFTCOL = *NO,                       
DYNUSRPRF = *USER,                     
SRTSEQ = *HEX,                         
DBGVIEW = *SOURCE                      
                                       
BEGIN                                  
  DECLARE reqHeader XML;               
  DECLARE resHeader XML;               
  DECLARE resBody CLOB(2G);            
  DECLARE resCode INTEGER;             
  DECLARE resMessage VARCHAR(200);     
  DECLARE rtnCode INTEGER DEFAULT 0;   
  DECLARE wkResBody BLOB(5K);          
  DECLARE url VARCHAR(512);            
  DECLARE recordCount INTEGER;         
  DECLARE host VARCHAR(128) DEFAULT 'myApp.csXX.my.salesforce.com';                                                 
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET rtnCode = SQLCODE;           
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET RTNCODE = SQLCODE;              
                                                                             
  BEGIN                                                                                                                                                                                                           
                                                                             
    SET url = TRIM(host)                                                     
           || 'services/data/vXX.X/query/'                                                      
           || '?q=SELECT+Id,Name,BodyLength+FROM+Attachment+where'           
           || '+ParentId+=+%27'                                              
           || trim(inParentId) || '%27';                                     
                                                                             
    SET host = REPLACE(host, 'https://', '');     
                                                                                
    -- Generate request header                                                  
    SET ReqHeader = (                                                           
    SELECT XMLGROUP(requestHeader.hname as "name",                              
                    requestHeader.hvalue as "value"                             
                    OPTION ROW "header"                                         
                    ROOT "httpHeader" as ATTRIBUTES)                            
    FROM                                                                        
    (VALUES                                                                     
      ('Authorization','Bearer ' || TRIM(inToken)),                             
    --('Host', 'myApp.csXX.my.salesforce.com')                 
      ('Host', TRIM(host))                                                      
    ) requestHeader(hname,hvalue));                                             
                                                                                
    -- Call the API                                                             
    SELECT                                                                      
    IFNULL(er.RESPONSEMSG,'-'), ifnull(er.RESPONSEHTTPHEADER,cast(null as XML)),
      IFNULL(xt.code, 400), IFNULL(xt.message,'-')                              
    INTO resBody, resHeader, resCode, resMessage                                
    FROM TABLE(   
     SYSTOOLS.HTTPGETCLOBVERBOSE(url, ReqHeader)) as er    
    CROSS JOIN                                             
    XMLTABLE('httpHeader'                                  
            PASSING                                        
            --ifnull(er.RESPONSEHTTPHEADER,cast(null as XML))
            xmlparse(document er.RESPONSEHTTPHEADER)
            COLUMNS                                        
            code INTEGER PATH '@responseCode',             
            message VARCHAR(200) PATH 'responseMessage'    
            ) as xt;                                       
                                                           
    IF resCode = '200' THEN                                
                                                           
      SET WKRESBODY = SYSTOOLS.JSON2BSON(RESBODY);         
                                                           
      VALUES (                                             
        IFNULL(JSON_VAL(wkResBody, 'totalSize', 'i') , -1) 
      ) INTO                                               
      recordCount;                                         
                                                           
      IF recordCount > 1 THEN                                                                                                         
        VALUES (                                                       
          IFNULL(JSON_VAL(wkResBody, 'records.0.Id', 's40') ,''),      
          IFNULL(JSON_VAL(wkResBody, 'records.0.Name', 's128') ,''),   
          IFNULL(JSON_VAL(wkResBody, 'records.0.BodyLength', 'i') , -1)
        ) INTO                                                         
        outAttID, outAttName, outAttLen;                               
      ElSE                                                             
        VALUES (                                                       
          IFNULL(JSON_VAL(wkResBody, 'records.Id', 's40') ,''),        
          IFNULL(JSON_VAL(wkResBody, 'records.Name', 's128') ,''),     
          IFNULL(JSON_VAL(wkResBody, 'records.BodyLength', 'i') , -1)  
        ) INTO                                                         
        outAttID, outAttName, outAttLen;                               
      END IF;                                                          
                                                                       
    --SET outMessage = resMessage;                                     
       SET OUTATTLEN = RECORDCOUNT;                                    
      SET outMessage = resCode || ': ' || resMessage;                  
      SET outSuccess = '1';                                            
    ELSE                                                               
      SET outMessage = resCode || ': ' || resMessage;
      SET outSuccess = '0';                          
    END IF;                                          
    return resCode;                                  
  END;                                               
END;

 

Getting Attachment from Salesforce

CREATE OR REPLACE FUNCTION    
SALESFORCE_GET_ATTACHMENT     
(                             
inCompany varchar(2),         
inToken varchar(256),         
inAttId varchar(256)          
)                             
                              
RETURNS blob(10M)             
LANGUAGE SQL                  
NO EXTERNAL ACTION            
SPECIFIC SFGETATT   
MODIFIES SQL DATA             
NOT DETERMINISTIC             
CALLED ON NULL INPUT          
                              
SET OPTION  ALWBLK = *ALLREAD,
ALWCPYDTA = *OPTIMIZE,        
COMMIT = *NONE,               
DECRESULT = (31, 31, 00),   
DFTRDBCOL = *NONE,                                                    
DYNDFTCOL = *NO,                                                      
DYNUSRPRF = *USER,                                                    
SRTSEQ = *HEX,                                                        
DBGVIEW = *SOURCE                                                     
                                                                      
BEGIN                                                                 
  DECLARE reqHeader XML;                                              
  DECLARE resHeader XML;                                              
  DECLARE resBody BLOB(10M);                                          
  DECLARE resCode INTEGER;                                            
  DECLARE resMessage VARCHAR(200);                                    
  DECLARE rtnCode INTEGER DEFAULT 0;                                  
  DECLARE url VARCHAR(512);                                           
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET rtnCode = SQLCODE;    
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET RTNCODE = SQLCODE;       
                                                                      
  BEGIN                                                               
                                                                      
    SET url = 'https://myApp.csXX.my.salesforce.com/'  
           || 'services/data/vXX.X/sobjects/Attachment/'       
           || trim(inAttId) || '/body';                        
                                                               
    -- Generate request header                                 
    SET ReqHeader = (                                          
    SELECT XMLGROUP(requestHeader.hname as "name",             
                    requestHeader.hvalue as "value"            
                    OPTION ROW "header"                        
                    ROOT "httpHeader" as ATTRIBUTES)           
    FROM                                                       
    (VALUES                                                    
      ('Authorization','Bearer ' || TRIM(inToken)),            
      ('Host', 'myApp.csXX.my.salesforce.com')
    ) requestHeader(hname,hvalue));                            
                                                               
    -- Call the API                                            
    SELECT                                                     
    RESPONSEMSG, xt.*                                          
    INTO resBody, resCode, resMessage                          
    FROM TABLE(                       
     SYSTOOLS.HTTPGETBLOBVERBOSE(url, ReqHeader)) as er    
    CROSS JOIN                                             
    XMLTABLE('httpHeader'                                  
            PASSING                                        
            --ifnull(er.RESPONSEHTTPHEADER,cast(null as XML))
            xmlparse(document er.RESPONSEHTTPHEADER)
            COLUMNS                                        
            code INTEGER PATH '@responseCode',             
            message VARCHAR(200) PATH 'responseMessage'    
            ) as xt;                                       
                                                           
    IF resCode = '200' THEN                                
      return trim(resBody);                                
    ELSE                                                   
      return trim(resCode);                                
    END IF;                                                
                                                           
  END;                                                     
END;

and storing it on IFS

ctl-opt dftActGrp(*no) actGrp(*Caller) usrPrf(*Owner)
  datFmt(*iso) alwNull(*INPUTONLY) bndDir('MYBIND') 
  option(*noDebugIo:*srcStmt);                       
                                                     
dcl-pi *n;                                           
  inCmp char(2) const;                               
  inRecordId char(32) const;                           
end-pi;                                              
                                                     
dcl-s wkCmp char(2);                                 
dcl-s wkRecordId char(32);                             
dcl-s wkSignatureFile sqltype(BLOB_FILE);            
dcl-s wkAttachmentId varchar(512);                   
dcl-s wkToken varchar(256);                          
dcl-s rtAttLen int(10);                              
dcl-s rtSuccess char(1);                             
dcl-s rtFileName varchar(256);                       
dcl-s rtMessage varchar(512);                        
dcl-s wkBlob sqltype(BLOB:16000000);                                        
                                                                         
wkCmp = inCmp;                                                           
wkStopId = inStopId;                                                     
                                                                                                                                          
exec SQL                                                                 
     select SALESFORCE_GET_OAUTH_TOKEN(:inCmp)                                   
     into :wkToken                                                       
     from sysibm.sysdummy1;                                              
                                                                         
exec SQL                                                                 
     CALL SALESFORCE_GET_ATTACHMENT_ATTR(:wkCmp, :wkToken, :wkRecordId,            
                                 :rtSuccess, :wkAttachmentId, :rtFileName
                                 ,:rtAttLen,:rtMessage);                 
if rtSuccess = '1' and rtFileName <> *blanks and rtAttLen > 0;      
    wkSignatureFile_Name = %trim(DFDPATH) + '/' + %trim(rtFileName);
    wkSignatureFile_NL   = %len(%trimR(wkSignatureFile_Name));      
    wkSignatureFile_FO   = SQFOVR;                                  
                                                                    
    exec SQL                                                        
         select SF_GET_ATTACHMENT(:inCmp, :wkToken, :wkAttachmentId)
         into :wkSignatureFile                                      
         from sysibm.sysdummy1;                                     
else;                                                                      
    dsply sqlstt;                                                   
endIf;                                                              
                                                                    
return;

 

No Comments IBM i, Salesforce

ZF3 Skeleton Application Installation on IBM i

The easiest way to install ZF3 on IBM i is to use a Composer. This can be done from a command line in PASE.

Command ===> CALL QP2TERM

Once in PASE first let add some path to our session to make PHP visible to us:

$ PATH=$PATH:/usr/local/zendphp7/bin
$ LIBPATH=$LIBPATH:/usr/local/zendphp7/bin
$ export PATH
$ export LIBPATH

or you can make the changes permanent:

$ echo "export PATH=/usr/local/zendphp7/bin/:$PATH" >> ~/.profile
$ echo "export LIBPATH=/usr/local/zendphp7/bin/:$LIBPATH" >> ~/.profile

If you don’t have Composer installed you can do it now:

$ php -r "readfile('http://getcomposer.org/installer');" > composer-setup.php
$ php composer-setup.php

Now we can copy Composer to PHP library

$ mv composer.phar /usr/local/zendphp7/bin

and to clean after the install:

$ rm composer-setup.php

This installation can be ran also using PHP script; there is a nice example on GitHub from Ben Kuhl.

With Composer’s on-board we can signal the framework and the skeleton app:

$ composer.phar create-project -n -sdev zendframework/skeleton-application path/to/install

That’s it! Now you can navigate your browser to your installation directory and the Zend Framework home page should appear on your screen.

References:

MVC Skeleton Application Installation using Composer

Troubleshooting:

Increase memory in PASE:

$ export LDR_CNTRL=MAXDATA=0xB0000000@DSA
No Comments IBM i, Zend, ZF3

Protected: IBM i and JSON

This content is password protected. To view it please enter your password below:

IBM i – CRTSQLRPG v7r1 – Missing source in STRDBG

Sometimes the defaults on the system prevent browsing source on SQL RPG programs in debug. To prevent this we can add following SQL code to the beginning of your RPG program

EXEC SQL SET OPTION
      NAMING = *SQL,                          
      CLOSQLCSR = *ENDMOD,                    
      COMMIT = *NONE,          
      DATFMT = *ISO,
      COMPILEOPT = 'DBGVIEW(*LIST)';

or add ‘DBGVIEW(*LIST)’ to compiler options COMPILEOPT on command CRTSQLRPGI.

No Comments IBM i

Protected: SSL in IBM i

This content is password protected. To view it please enter your password below:

Protected: Arrays in DB2

This content is password protected. To view it please enter your password below: