Storing Data in Delimited Strings
Overview
What
A delimited string is a way of representing data using specified sequences of characters to separate individual entries. Delimited strings can be an attractive solution for storing a dynamic set of data. Using delimited strings, you might serialize a dictionary or an array, so that you can store or share the information.
The following is an example of a simple delimited string:
!!KEY!!value!!KEY2!!value2
Why
Delimited strings are useful for data that doesn't fit into configurable, line item, or main doc attributes. Eliminate those options before using a delimited string, since it is much easier to maintain and troubleshoot data that is stored in default Oracle CPQ data structures. Delimited strings can also be used when sending information to external services such as a CGI or Web Service.
Where
When dealing with delimited strings, the data will often be parsed and manipulated within BML, preferably within Util Functions to limit code duplication. The strings may also be both read and written from within XSL scripts, for both Integration and RTF/PDF Output. The strings may be sent to external systems through URL parameters or through other Web Services, including any middleware.
Administration
Before you begin
-
Delimited Strings can run out of space.
Make sure your attribute lengths are long enough, and watch out for Data Tables and their 255 character limit.
-
Bad delimiters corrupt data.
Characters like tildes (~), pipes (|), angle brackets (<>), and ampersands (&) can clash with XML, HTML, URL parameters, and other delimited strings.
Safe Basic Separators include: ,**::_some_keyword_
-
Delimited strings can be difficult to maintain.
Make your string descriptive to human troubleshooters.
Choose your delimiters
Make your delimiters descriptive, and consider your source data to pick delimiters that won't cause conflicts. Use delimiters that don't look like your data. Combining words into your delimiters can be very effective at conveying information, while avoiding collisions. Examples:
!ROW!!VAL!15!DISC!0.5!ROW!!VAL!15!DISC!0.2
(_value_15_discount_0.5)(_value_15_discount_0.2)
Notice that the preceding string is easy to parse using string manipulation, and also descriptive to human readers.
Put delimiters into an attribute
To make it simple to manage and maintain your delimiters, store them in Oracle CPQ attributes. Then if you need to make changes, it is simply a matter of updating the attribute, and all of your code will then be in compliance.
Create the string
Once you know the format for the delimited strings, put the string into the format as early in the process as possible, in order to avoid inconsistency when dealing with the given values. If the data is coming from a Salesforce integration, for instance, then consider building the string directly in the XSL Parser, before it is even stored in CPQ. Use a Util library to create the delimited string, and use a key/value pairing system as often as possible.
It is good practice to break the delimiter out into its own variable, to make it easier to work with, as in the following example:
delim=columnDelimiter_quote;
// create a delimited string, using key/value pairs
delim_string = "totalDiscount" + delim + totalDiscount_quote
+ delim + "discountReason" + delim + discountReason_quote;
Retrieve values from the string
Use a util library to retrieve the string as often as possible. You will basically be using the BML “split” function to process the string. Don't be too trusting. The data coming in might look different than you expect. Write your code defensively, to deal with common problems.
ret = "Error parsing string in util library: " + delimited_string;
delim=delimiter_quote;
delimited_string = "discount_value"+delim+"10.0"+delim+"discount_percent"+delim+"0.5";
parsed_array = split(delim, delimited_string);
//test for length first!
if(sizeofarray(parsed_array) > 10) {
discount_value = parsed_array[1];
discount_percent = parsed_array[3];
// validate numericality of data before atoi/atof
if(isnumber(discount_value) and isnumber(discount_percent)) {
full_value = atoi(discount_value) / atoi(discount_percent);
ret = full_value;
}
}
return ret;
Notes
When retrieving values from a string, don't assume the values are formed perfectly.
REST API Delimiter Considerations
-
REST API services do not support mufti-character separators for column or row delimiters. For example, you cannot use "$^$" as separator because the separator must be a single character. Note that this behavior differs from BML functionality.
-
If more than one character is provided as separator, the results are may not be correct.
-
The backslash "\" is not used as escape character to change the meaning of the next character. For example, "\n" is interpreted as two seperate characters "\" and "n" and it will be considered as a mufti-character delimiter.
-
The line break/carriage return is the default separate for CSV files, therefore it does not need to be specified.
-
If a different delimiter is specified, the carriage returns in the CSV file will be replaced with the specified delimiter. If you want to specify a carriage return as delimiter, you can enter the carriage return as a delimiter in Postman to generate a return character.
Test your data thoroughly before parsing, and watch for number parsing errors and Null Pointer Exceptions.
Related Topics
See Also
