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;