@Hermann Stamm-WilbrandtHi Hermann, Yes you are correct.
We have request as Non xml example as below:
0000000000000000000000000000000000000000I2GT IAS EXTRACT2018112920181128201811302.2
721000539951851170082400401M20200209D0496C0000000000000007E0000000020181129
721000549951851170082400401M20200209D0496C0000000000000007E0000000020181129
Requirement is: Datapower has to read the input data, the card number present at each line, those card number need to be replaced with corresponding PCIID values from Database.
Development: I have used Gateway script to read the incoming request, and base don regular expression I am extracting the card number, now I need to run DB query to get the corresponding values for that cardnumber and replace in file with that value.
I am facing challenge now to read the result of the DB query which I have executed via xslt.tranform in Gateway script, I am able to execute the SQL stmt in XSLT and storing the value in context variable and then I am trying to read that value in Gateway script but i could see in the logs it says undefined (mpgw (Test_PCI): cardPICID:undefined) , could you please help me how to read the result in Gateway script
My Gaeway Script is as below:
var sm = require('service-metadata');
var transform = require('transform');
var ctx = session.name('Ctx') || session.createContext('Ctx');
function uuidv4() {
return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
var r = Math.random() * 16 | 0, v = c == 'x' ? r : (r & 0x3 | 0x8);
return v.toString(16);
});
}
session.input.readAsBuffers (function (error, bufs) {
if (error) { session.reject(error); return; }
else
{
var regex = /000[5-6][1-3][0-9]{14}/gm;
var str = bufs.toString();
var fistMatch = str.search(regex);
var matchRes = str.match(regex);
var splitRes = str.split(regex);
var i = 0;
var result = "";
if (fistMatch != '0') {
for (i = 0; i < matchRes.length; i++) {
//DB Query to execute to get the cardPCIID, and to if there is no card pcid in the table pass the CARD value as PCID
var CRD_NBR = matchRes[i].substr(3,16);
ctx.setVariable('cardnumber', CRD_NBR);
var options = {
"location": "local:///KKM_DBqueryPCIID.xsl"
};
transform.xslt(options, function(error, nodelist) {
if (error) {
ctx.setVar('error', error);
}
else{
return(nodelist);
}
});
var cardID = ctx.getVariable('requestPCIID');
//var cardPICID = ctx.getVar('var://context/ctx/requestPCIID');
result += splitRes[i] + cardPICID;
}
}
else {
for (i = 0; i < matchRes.length; i++) {
result += splitRes[i] + matchRes[i].substr(0,6) + "******" + matchRes[i].substr(13,19) + uuidv4(matchRes[i]);
}
}
result += splitRes[splitRes.length - 1];
//session.output.write(result);
}
});
SQL - KKM_DBqueryPCIID.xsl:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:dp="http://www.datapower.com/extensions"
xmlns:dpconfig="http://www.datapower.com/param/config"
extension-element-prefixes="dp"
exclude-result-prefixes="dp dpconfig">
<xsl:template match="/">
<xsl:variable name="CRDNBR" select="dp:variable('var://context/Ctx/cardnumber')" />
<!--<xsl:variable name="myQuery" select="'SELECT * from PCI.PCICardMapping WHERE cardNumber = '5399518511700824'"/>-->
<xsl:variable name="myQuery">
SELECT "pciCardID" FROM "PCI"."PCICardMapping" WHERE "cardNumber" = <xsl:text>'</xsl:text><xsl:value-of select="$CRDNBR"/><xsl:text>'</xsl:text>
</xsl:variable>
<xsl:variable name="result">
<xsl:copy-of select="dp:sql-execute('pcidpdatasource',$myQuery)"/>
</xsl:variable>
<xsl:variable name="cardPCIID">
<xsl:value-of select="$result/sql/row/column[name='pciCardID']/value"/>
</xsl:variable>
<dp:set-variable name="'var://context/ctx/requestPCIID'" value="string($cardPCIID)"/>
<xsl:value-of select="$cardPCIID"/>
</xsl:template>
</xsl:stylesheet>
------------------------------
Krishna Naik
------------------------------
Original Message:
Sent: Sun March 08, 2020 03:40 AM
From: Krishna Naik
Subject: DB Query to execute in Gateway Script
Hi Hermann,
Yes I am using Gateway script, is there any way we can run DB query via Gateway script.
I need to user only the carnumber value from the result of DB query instead of complete nodeset, could you please help.
------------------------------
Krishna Naik
------------------------------
Original Message:
Sent: Sat March 07, 2020 10:08 PM
From: Hermann Stamm-Wilbrandt
Subject: DB Query to execute in Gateway Script
Hi,
while it is technically possible to do information extraction from XML via string operations on XML.stringify(nodelist), it is a bad idea.
I would use the other function in transform module, transform.xpath() to extract the information:
https://www.ibm.com/support/knowledgecenter/SS9H2Y_7.7.0/com.ibm.dp.doc/transform_js.html
In addition, seeing GatewayScript using tansform.xslt/( nd transform.xpath(), I would query deciion to use GatewayScript action.
My rule of thumb on DataPower processing:
For processing JSON or binary data, use GatewayScript., for processing XML data, use XSLT.
------------------------------
Hermann Stamm-Wilbrandt
Compiler Level 3 support & Fixpack team lead
IBM DataPower Gateways (⬚ᵈᵃᵗᵃ / ⣏⠆⡮⡆⢹⠁⡮⡆⡯⠂⢎⠆⡧⡇⣟⡃⡿⡃)