ODAG Unable to pass filtered value with single quote

[mborsadw]

I have an ODAG selection app and I am selecting values which have a single quote in it. If you try to generate the application using ODAG with a value containing a single quote, the application is generated without data.

Is there a workaround for this issue?

[treysmithdev]

You can change the delimiter using the following syntax:

$(odso_ORIGIN){"quote": "|", "delimiter": ";"}

Also, if you plan to utilize this in an Inline Table load, you will need to adjust the settings. From the help documentation:

Bind data containing quotation marks

If the data in any of the fields used for bind variables contain either single or double quotation marks, you need to change the default behavior of the INLINE load statement to not interpret quotation marks as value delimiters. Change the delimiter using the format spec to a character that never occurs in the values of the field being bound, as is shown with the '|' character in the following example:

OdagBinding:

LOAD * INLINE [

VAL

$(odso_ORIGIN){"quote": "", "delimiter": ""}

]

(ansi, txt, delimiter is '|', embedded labels);

[mborsadw]

Neither of these options work. Here is the loadscript for both the selection and the detail app. 

Selection app:

[Flight]:
Load * inline [
Origin, Destination, Time
Dallas, Atlanta, 2
Dallas, Chicago, 1.5
Dallas, San Franci'sco, 3
Dallas, New York, 3
Austin, Dallas, 1
Austin, New York, 3.5
];

Store [Flight] into [lib://qvd/Flights.qvd] (qvd);

Detail app:

// DO NOT ALTER THIS SUBROUTINE
SUB ExtendQVDWhere(Name, ValVarName)
LET T = Name & '_COLNAME';
LET ColName = $(T);
LET Values = $(ValVarName);
IF (len(Values) > 0) THEN
IF len(WHERE_PART) > 0 THEN
LET WHERE_PART = '$(WHERE_PART) AND mixmatch([$(ColName)],$(Values) )';
ELSE
LET WHERE_PART = ' WHERE mixmatch([$(ColName)],$(Values))';
ENDIF
ENDIF
END SUB;

// DO NOT ALTER THIS SUBROUTINE
SUB BuildValueList(VarName, TableName, ColName, QuoteChrNum)
IF ($(QuoteChrNum) = 0) THEN
LET LOADEXPR = 'Concat($(ColName),' & chr(39) & ',' & chr(39) & ') AS CombinedData';
ELSE
LET CHREXPR = ' chr(' & '$(QuoteChrNum)' & ') ';
LET LOADEXPR = 'Concat( $(CHREXPR) & $(ColName) & $(CHREXPR)' & ',' & chr(39) & ',' & chr(39) & ') AS CombinedData';
ENDIF
_TempTable:
LOAD $(LOADEXPR) Resident $(TableName);
Let vNoOfRows = NoOfRows('_TempTable');
IF $(vNoOfRows)> 0 THEN
LET $(VarName) = Peek('CombinedData',0,'_TempTable');
ENDIF
drop table _TempTable;
drop table '$(TableName)';
END SUB;

SET DESTINATION='';
OdagBinding:
LOAD * INLINE [
VAL
$(odso_Destination){"quote": "", "delimiter": ""}
]
(ansi, txt, delimiter is '|', embedded labels);

SET DESTINATION_COLNAME='Destination';
CALL BuildValueList('DESTINATION', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values

SET WHERE_PART = '';

FOR EACH fldname IN 'DESTINATION'
LET vallist = $(fldname);
IF (IsNull(vallist)) THEN
LET vallist = '';
ENDIF
IF (len(vallist) > 0) THEN
CALL ExtendQVDWhere('$(fldname)','vallist');
ENDIF
NEXT fldname

TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART);

LET FLIGHTS_QVD='[lib://qvd/Flights.qvd] (qvd)';
LOAD *
FROM $(FLIGHTS_QVD)
$(WHERE_PART);