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;
Leave a Reply