You are here:
Parameterized Query Support
Consumer Goods Cloud Mobile App Framework provides enhanced app security by using parameterized queries that ensure that no data other than the data that users have visibility and editability rights for is manipulated. To enable parametrized queries, data used as parameters in the data sources is inserted in the SQL statements by using parameter binding. Parameter binding is applied only for data sources with a schema version of 2.0.
Required Editions
| Available in: Lightning Experience in Professional, Unlimited, and Enterprise Editions that have Consumer Goods Cloud enabled. |
Modeled Datasources
Customizers must set the schema version correctly for all data sources. The schema version 2.0 indicates to CG Cloud Mobile App Framework that parameter binding must be used for this modeled data source. Customizers must also change the parameter declaration for data sources with schema version 2.0 as all parameters (except the ones having treatAs attribute set to sqlSnippet) need the type attribute. The internal parameter binding algorithm of CG Cloud Mobile App Framework works on the declared data source parameters.
For special data source parameters that contain SQL snippets, these rules apply:
- The parameter tag supports an additional attribute, treatAs, for data sources of schema version 2.0. The value of treatAs is set to sqlSnippet.
- CG Cloud Mobile App Framework doesn’t use parameter binding for parameters of treatAs sqlSnippet but replaces the macros in the SQL statement with the value of the parameter.
The advanced search parameters for modeled data sources are also handled inside the CG Cloud Mobile App Framework. A new attribute type is used for Parameter, a data source XML tag. The supported values for the type attribute are NULL, INTEGER, REAL, and TEXT. An additional value, List, is used for parameters in the IN conditions. All the items of the list (array or comma-separated list in a string) must be of the same type (string or numeric). This attribute is mandatory for data sources with schema version >=2.0. The parameter that has treatAs set is considered an exception.
External Datasources
For external data sources, customizers must insert ? into the returned SQL statement. CG Cloud Mobile App Framework replaces ? with parameters. You must implement the parameter binding related actions. You must also return a list of parameters in the sequence in which they must be inserted into the ? character. An external data source with schema version 2.0 doesn’t return just a string, but an object of the following form:
// in this example in sqlStmt there are two ? chars.
return {
sql : Utils.replaceSqlParameters(sqlStmt, jsonQuery),
params : [callDate, callDate]
};CG Cloud Mobile App Framework validates if the count of ? characters matches the number of parameters in the list at runtime. If they don’t match, an error is logged and an empty array [] is returned. Also, while using advanced search inside an external data source, the search parameter must be directly added to the SQL string but passed to framework as an element in the params array.
Customizers can use INSERT, UPDATE, or DELETE operations in external data sources. The operations return multiple statements in an array. The return value has the [statement, statement, …] format, where statement can be a string or an object containing a parameterized statement and the parameters ({sql:" statement including question marks", params:[parameters]}).
Framework Functions
The following functions are used for parametrized statements.
The string in the function must contain all parameters as macros. The function returns an object in the following format:
The function is used to replace Utils.convertForDB.
The function is used to replace a parameter for an IN condition. The list can be an array or a comma-separated list in a string. The function returns an object in the following format:
<Datasource name="someName" schemaVersion="2.0"> <Database platform="SQLite"> <Load><![CDATA[ // some code ]]></Load> <Update><![CDATA[ // some code ]]></Update> </Database> </Datasource><DataSource name="DsBoOrder" businessObjectClass="BoOrder" external="false" editableEntity="SdoMain" schemaVersion="2.0">
<Attributes>
<Attribute name="pKey" table="SdoMain" column="PKey" />
<Attribute name="orderId" table="SdoMain" column="Id" />
<Attribute name="commitDate" table="SdoMain" column="CommitDate" />
<Attribute name="deliveryDate" table="SdoMain" column="DeliveryDate" />
<Attribute name="phase" table="SdoMain" column="Phase" />
<Attribute name="totalValue" table="SdoMain" column="TotalValue" />
<Attribute name="totalValueReceipt" table="SdoMain" column="TotalValueReceipt" />
<Attribute name="grossTotalValue" table="SdoMain" column="GrossTotalValue" />
<Attribute name="grossTotalValueReceipt" table="SdoMain" column="GrossTotalValueReceipt" />
<Attribute name="merchandiseValue" table="SdoMain" column="MerchandiseValue" />
<Attribute name="merchandiseValueReceipt" table="SdoMain" column="MerchandiseValueReceipt" />
<Attribute name="deliveryRecipientPKey" table="SdoMain" column="DeliveryRecipientPKey" />
<Attribute name="ordererPKey" table="SdoMain" column="OrdererPKey" />
<Attribute name="customerOrderId" table="SdoMain" column="CustomerOrderID" />
<Attribute name="cancelReason" table="SdoMain" column="CancelReason" />
<Attribute name="sdoMetaPKey" table="SdoMain" column="SdoMetaPKey" />
<Attribute name="responsiblePKey" table="SdoMain" column="ResponsiblePKey" />
<Attribute name="initiatorPKey" table="SdoMain" column="InitiatorPKey" />
<Attribute name="ownerPKey" table="SdoMain" column="OwnerPKey" />
<Attribute name="clbMainPKey" table="SdoMain" column="ClbMainPKey" />
<Attribute name="currency" table="SdoMain" column="Currency" />
<Attribute name="billToCustomerPKey" table="SdoMain" column="BillToCustomerPKey" />
<Attribute name="payerCustomerPKey" table="SdoMain" column="PayerCustomerPKey" />
<Attribute name="releaseTime" table="SdoMain" column="ReleaseTime" />
<Attribute name="brokerCustomerPKey" table="SdoMain" column="BrokerCustomerPKey" />
<Attribute name="initiationDate" table="SdoMain" column="InitiationDate" />
<Attribute name="ivcRef1PKey" table="SdoMain" column="IvcRef1PKey" />
<Attribute name="ivcRef2PKey" table="SdoMain" column="IvcRef2PKey" />
<Attribute name="ivcRef3PKey" table="SdoMain" column="IvcRef3PKey" />
<Attribute name="ivcRef4PKey" table="SdoMain" column="IvcRef4PKey" />
<Attribute name="ivcRef5PKey" table="SdoMain" column="IvcRef5PKey" />
<Attribute name="salesOrg" table="SdoMain" column="SalesOrg" />
<Attribute name="actualStatePKey" table="SdoMain" column="ActualStatePKey" />
<Attribute name="nextStatePKey" table="SdoMain" column="NextStatePKey" />
<Attribute name="wfeWorkflowPKey" table="SdoMain" column="WfeWorkflowPKey" />
<Attribute name="cndCpCalculationSchemaPKey" table="SdoMain" column="CndCpCalculationSchemaPKey" />
<Attribute name="calculationStatus" table="SdoMain" column="CalculationStatus" />
<Attribute name="calculationTime" table="SdoMain" column="CalculationTime" />
<Attribute name="pricingDate" table="SdoMain" column="PricingDate" />
<Attribute name="distribChannel" table="SdoMain" column="DistribChannel" />
<Attribute name="division" table="SdoMain" column="Division" />
<Attribute name="headerDiscount" table="SdoMain" column="HeaderDiscount" />
<Attribute name="docTaType" table="SdoMain" column="DocTaType" />
<Attribute name="paymentReason" table="SdoMain" column="PaymentReason" />
<Attribute name="printStatus" table="SdoMain" column="PrintStatus" />
<DerivedAttribute name="isRoundingOn" value="0" />
<DerivedAttribute name="isRoundingToggleChanged" value="0" />
<Attribute name="invoiceId" table="SdoMain" column="InvoiceId" />
<Attribute name="asn" table="SdoMain" column="ASN" />
<Attribute name="message" table="SdoMain" column="Message" />
<Attribute name="documentType" table="SdoMain" column="DocumentType" />
<Attribute name="totalShippedQuantity" table="SdoMain" column="TotalShippedQuantity" />
<Attribute name="totalReturnedQuantity" table="SdoMain" column="TotalReturnedQuantity" />
<Attribute name="paidAmount" table="SdoMain" column="PaidAmount" />
<Attribute name="paidAmountReceipt" table="SdoMain" column="PaidAmountReceipt" />
<Attribute name="absolutePaidAmount" table="SdoMain" column="AbsolutePaidAmount" />
<Attribute name="paymentMethod" table="SdoMain" column="PaymentMethod" />
<Attribute name="debitCredit" table="SdoMain" column="DebitCredit" />
<Attribute name="tmgMainPKey" table="SdoMain" column="TmgMainPKey" />
<Attribute name="splittingOption" table="SdoMain" column="SplittingOption" />
<Attribute name="splittingRule" table="SdoMain" column="SplittingRule" />
<Attribute name="splittingParentSdoMainPKey" table="SdoMain" column="SplittingParentSdoMainPKey" />
<DerivedAttribute name="releaseGeolocationLatitude" value="' '" />
<DerivedAttribute name="releaseGeolocationLongitude" value="' '" />
<Attribute name="etpWarehousePKey" table="SdoMain" column="EtpWarehousePKey" />
<DerivedAttribute name="tourId" value="' '" />
<DerivedAttribute name="sf_mobilityRelease" value="' '" />
</Attributes>
<Entities>
<Entity name="SdoMain" alias="" idAttribute="PKey" />
</Entities>
<QueryCondition><![CDATA[
SdoMain.PKey = #pKey#
PrdProposalAuthListBpaCategoryModule.CustomerPKey = #customerPKey#
#addCond_ListingWithModules#
#addCond_productPKeys#
#groupBy_for_proposal#
]]></QueryCondition>
<OrderCriteria>
<OrderCriterion entity="SdoMain" attribute="Id" direction="ASC" />
</OrderCriteria>
<Parameters>
<Parameter name="pKey" type="TEXT"/>
<Parameter name="customerPKey" type="TEXT"/>
<Parameter name="addCond_Listing" treatAs="sqlSnippet"/>
<Parameter name="addCond_ListingWithModules" treatAs="sqlSnippet"/>
<Parameter name="addCond_productPKeys" treatAs="sqlSnippet"/>
<Parameter name="groupBy_for_proposal" type="TEXT"/>
</Parameters>
</DataSource><Datasource objectClass="BoCall">
<Database platform="SQLite">
<Load><![CDATA[
if (Utils.isDefined(jsonQuery)){
var newParams = jsonQuery;
if (Utils.isOldParamsFormat(newParams)){
newParams = Utils.convertDsParamsOldToNew(newParams);
}
}
var sqlparams=[];
var sqlStmt = "SELECT ClbMain.PKey AS pKey,ClbMain.BpaMainPKey AS bpaMainPKey,ClbMain.BeginTime AS beginTime,ClbMain.FinishTime AS finishTime,
ClbMain.CreationDateTime AS creationDateTime, ";
sqlStmt += "ClbMain.CompletedDate AS completedDate,ClbMain.ClbMetaPKey AS clbMetaPKey,ClbMain.Subject AS subject,ClbMain.ClbStatus AS clbStatus,ClbMain.DateFrom AS dateFrom, ";
sqlStmt += "ClbMain.DateThru AS dateThru,ClbMain.TimeFrom AS timeFrom,ClbMain.TimeThru AS timeThru,ClbMain.AllDay AS allDay,ClbMain.IsPrivate AS isPrivate,
ClbMain.Duration AS duration,ClbMain.Fixed AS fixed, ";
sqlStmt += "ClbMain.ReasonCode AS reasonCode,ClbMain.ResponsiblePKey AS responsiblePKey, ClbMain.InitiatorPKey AS initiatorPKey,ClbMain.OwnerPKey AS ownerPKey,
ClbMain.Note AS note,'0' AS noteInitialized, ";
sqlStmt += "ClbMain.ClbStatus AS originalClbStatus, ClbMain.Week as week, ";
sqlStmt += "'0' AS substitution, ";
sqlStmt += "' ' AS subMainPKey, ";
sqlStmt += "ClbMain.StartTimeEffective AS startTimeEffective, ";
sqlStmt += "ClbMain.StopTimeEffective AS stopTimeEffective, ";
sqlStmt += "time(ClbMain.StartTimeEffective) AS startTimeEffectiveUI, ";
sqlStmt += "time(ClbMain.StopTimeEffective) AS stopTimeEffectiveUI, ";
sqlStmt += "ClbMain.DurationEffective AS durationEffective, ";
sqlStmt += "ClbMain.TmgMainPKey AS tmgMainPKey, ";
sqlStmt += "CASE WHEN ClbMain.TmgMainPKey <> ' ' THEN TmgTour.Id ELSE ' ' END as tmgMainId, ";
sqlStmt += "CASE WHEN ClbMain.TmgMainPKey <> ' ' THEN '1' ELSE '0' END as tourRelated, ";
sqlStmt += "ClbMeta.UsrTimeEntryMetaPKey AS usrTimeEntryMetaPKey, ";
sqlStmt += "'' AS timeEntryPKey, ";
sqlStmt += "ClbMeta.CancelSalesDocuments AS cancelSalesDocuments ";
sqlStmt += "FROM ClbMain ";
sqlStmt += "LEFT JOIN ClbMeta on ClbMeta.PKey = ClbMain.ClbMetaPKey ";
sqlStmt += "LEFT JOIN TmgTour on TmgTour.PKey = ClbMain.TmgMainPKey ";
sqlStmt += "WHERE (1 = 1) ";
var index = 0;
for (index in jsonQuery.params) {
switch (jsonQuery.params[index].field)
{
case "pKey":
sqlStmt += "AND ClbMain.PKey = ? ";
sqlparams.push(jsonQuery.params[index].value);
break;
}
}
return{sql:sqlAtmt,params:sqlParams};
]]></Load>
<Update><![CDATA[
var escapedSubject = object.getSubject();
escapedSubject = escapedSubject.replace(/'/g,"''");
var escapedNote = object.getNote();
escapedNote = escapedNote.replace(/'/g,"''") ;
var sqlStmt = "UPDATE ClbMain ";
sqlStmt += "SET PKey = ?, ";
sqlStmt += "BusinessModified =?, ";
sqlStmt += "BpaMainPKey = ?, ";
sqlStmt += "ClbMetaPKey = ?, ";
sqlStmt += " Subject = ?, ";
sqlStmt += "ClbStatus = ?, ";
sqlStmt += "DateFrom = ?, ";
sqlStmt += "DateThru =?, ";
sqlStmt += "TimeFrom = ?, ";
sqlStmt += "TimeThru = ?, ";
sqlStmt += "BeginTime = ?, ";
sqlStmt += "FinishTime = ?, ";
sqlStmt += "CreationDateTime =?, ";
sqlStmt += "CompletedDate =?, ";
sqlStmt += "AllDay = ?, ";
sqlStmt += "IsPrivate = ?, ";
sqlStmt += "Duration = ?, ";
sqlStmt += "Fixed =?, ";
sqlStmt += "ReasonCode = ?, ";
sqlStmt += "Note = ?, ";
sqlStmt += "Substitution =?, ";
sqlStmt += "SubMainPKey = ?, ";
sqlStmt += ?, ";
sqlStmt += "ResponsiblePKey = ?, ";
sqlStmt += "InitiatorPKey = ?, ";
sqlStmt += "OwnerPKey = ?,";
sqlStmt += "StartTimeEffective = ?,";
sqlStmt += "StopTimeEffective = ?,";
sqlStmt += "DurationEffective= ?,";
sqlStmt += "TmgMainPKey= ? ";
sqlStmt += " WHERE PKey =?;";
var sqlparams=[object.getPKey(),Utils.createAnsiDateNow(),object.getBpaMainPKey(),object.getClbMetaPKey(),escapedSubject,object.getClbStatus(), object.getDateFrom(),object.getDateThru(),
object.getTimeFrom(),object.getTimeThru(),object.getBeginTime(),object.getFinishTime(), object.getCreationDateTime(),object.getCompletedDate(), object.getAllDay(),object.getIsPrivate(),
object.getDuration(),object.getFixed(),object.getReasonCode(),escapedNote, object.getSubstitution(),object.getSubMainPKey(), object.getWeek(),object.getResponsiblePKey(),
object.getInitiatorPKey(),object.getOwnerPKey(),object.getStartTimeEffective(),object.getStopTimeEffective(),object.getDurationEffective(),object.getDurationEffective(), object.getPKey()];
return {sql:sqlStmt,params:sqlParams};
]]></Update>
<Insert><![CDATA[
var escapedSubject = object.getSubject();
escapedSubject = escapedSubject.replace(/'/g,"''");
var escapedNote = object.getNote();
escapedNote = escapedNote.replace(/'/g,"''") ;
var sqlStmt = "INSERT INTO ClbMain (PKey, BusinessModified, BpaMainPKey, ClbMetaPKey, Subject, ClbStatus, DateFrom, DateThru, TimeFrom, TimeThru, AllDay, IsPrivate, Duration,
Fixed, ReasonCode, SalesOrg, ResponsiblePKey, InitiatorPKey, OwnerPKey, Note, BeginTime, FinishTime, CreationDateTime, CompletedDate, Substitution, SubMainPKey,
Week,StartTimeEffective,StopTimeEffective,DurationEffective,TmgMainPKey) VALUES (?, ? , ?, ?,?, ?,?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?,?,?,?,?,?,?,?,?,?)";
var sqlparams=[object.getPKey(),Utils.createAnsiDateNow(),object.getBpaMainPKey(),object.getClbMetaPKey(),escapedSubject, object.getClbStatus(),object.getDateFrom(),
object.getDateThru(),object.getTimeFrom(), object.getTimeThru(), object.getAllDay(), object.getIsPrivate(),object.getDuration(),object.getFixed(), object.getReasonCode(),
Application.getApplicationContext().get('user').getBoUserSales().getSalesOrg(),object.getLuUser().getPKey(),object.getInitiatorPKey(),object.getOwnerPKey(),escapedNote,
object.getBeginTime(),object.getFinishTime(),object.getCreationDateTime(), object.getCompletedDate(),object.getSubstitution(),object.getSubMainPKey(),object.getWeek() ,
object.getStartTimeEffective(), object.getStopTimeEffective(), object.getDurationEffective(),object.getTmgMainPKey()];Key()];
return {sql:sqlStmt,params:sqlparams};
]]></Insert>
<Delete><![CDATA[
var sqlStmt = "DELETE FROM ClbMain WHERE PKey = ?";
var sqlparams=[object.getPKey()];
return [sql:sqlStmt,params:sqlParmas};
]]></Delete>
</Database>
</Datasource>