Hierarchical Queries with DB2 Connect By
Reference
https://www.ibm.com/developerworks/ibmi/library/i-db2connectby/index.html
IBM i UDTF
References:
Protected: SQL, BLOB, and IFS Files
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;
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
Protected: IBM i and JSON
Protected: IBM i Talk With Watson
Protected: SQL Tips – IBM i
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.