User Defined Transform - per Record

See my per Collection post. The difference is that one does not need to loop through the collection but can work on the record directly.

User Defined Transformation - thoughts

• It is easy to overlook that there are several substitution parameter configurations so that some substitutions are carried out others not.
• Rather an un-sight: Destruction of used variables are used in all examples seen so far. The reason has not yet become clear to me.

User Defined Transform - development (mock and logging)

Intro

DS editor for Python is quite a pain. It is easier and probably faster with respect to run cycles to use below mock and integrate it as follows.

Integration

Import mock if not running in DS and prepare environment

u"""Import libraries
--------------------------------------------------------------------------------

When run outside SAP DS dataflow, a mock interface gets imported to provide
development/debug functionality outside SAP DS.
"""

try:
    Properties # outside SAP DS this is only present when the mock has been imported
except NameError:
    from sap import Collection, DataManager
    RUNS_IN_SAP = False
except:
    raise
else:
    RUNS_IN_SAP = True

import sys
import logging

Use mock if not running in DS

u"""Main
--------------------------------------------------------------------------------

    - Sets logging level depending on the environment
    - Calls a function to create a mock collection if not run within SAP DS
    - Calls function to do the actual work
"""
if __name__ == '__main__':
    RUNS_IN_DEV = (load_substitution_param(r'[$$Env]', 'dev') == 'dev')
    # We need to set the stream to stdout explicitly, otherwise we won't
    # see logging output in DS!
    if RUNS_IN_DEV:
        logging.basicConfig(stream=sys.stdout, level=logging.DEBUG)
    else:
        logging.basicConfig(stream=sys.stdout, level=logging.INFO)
    logging.debug('This UDT started')
    if not RUNS_IN_SAP and Collection.Size() == 0:
        prepare_collection_or_record()
    logging.debug('Size of collection: ' + str(Collection.Size()))
    do_the_actual_work()

Helper functions

# Adapted from http://lukasmartinelli.ch/python/2015/10/22/sap-bods-python-tricks.html
# Developed against Python 2.7.15

# Copy needed functions to your code as long as this module cannot get imported
# from within SAP DS

def sap_timestamp(timestamp):
    return time.strftime('%Y.%m.%d %H:%M:%S', timestamp)


def load_substitution_param(substitution, default_value):
    if substitution.startswith('[$$'):
        return default_value
    else:
        return substitution

Mock

Be aware that you will break the mock if you change the names as those must be identical to the one of DS! Actually the mock would work but it fails to run in DS. From this point of view the mock is no mock for DS anylonger and broken as that.

"""
This module enables local testing of python code
used for User Defined Transforms in SAP BODS
Adapted from `SAP BODS Python Tricks`_
.. _SAP BODS Python Tricks: http://lukasmartinelli.ch/python/2015/10/22/sap-bods-python-tricks.html
Developed against Python 2.7.15
"""

class FIDataCollection:
    records = []

    def AddRecord(self, record):
        """
        Add a new record(returned by DataManager.NewDataRecord()) to the
        collection.
        For every NewDataRecord(), you can call AddRecord() only once.
        After you call AddRecord(), do not call DeleteDataRecord().
        """
        self.records.append(record)

    def DeleteRecord(self, record):
        """Removes the specified record from collection."""
        self.records.remove(record)

    def GetRecord(self, record, index):
        """Get a record at a given index from the collection."""
        if index < 1:
            raise ValueError("Indizes in SAP BODS start at 1 and not 0!")
        for key, value in self.records[index-1].values.iteritems():
            record.SetField(key, value)

    def Size(self):
        """
        Counts the number of records in the collection.
        Returns number of records in a collection.
        """
        return len(self.records)

    def Truncate(self):
        """Removes all the records from collection."""
        self.records = []


class FIDataManager:
    def NewDataRecord(self, ownership=1):
        """
        Creates a new record object. Do not use this method in a loop,
        otherwise the Python expression may experience a
        memory leak. Depending on the expression, you'll probably want to
        place this method at the beginning of the expression.
        Returns a new object of type FlDataRecord.
        """
        return FIDataRecord()

    def DeleteDataRecord(self, record):
        """
        Deletes the memory allocated to the record object.
        Do not call DeleteDataRecord() after calling AddRecord().
        """
        del record


class FIProperties:
    values = {}

    def GetProperty(self, property_name):
        """Returns the value of given property."""
        if not isinstance(property_name, unicode):
            raise ValueError("You must use unicode for property name")
        return self.values[property_name]


class FIDataRecord:
    values = {}

    def SetField(self, field_name, value):
        """Stores a value in the specified field."""
        if not isinstance(field_name, unicode):
            raise ValueError("You must use unicode for field name")
        if not isinstance(value, unicode):
            raise ValueError("You must use unicode for setting the value")
        self.values[field_name] = value

    def GetField(self, field_name):
        """Get a field from record."""
        if not isinstance(field_name, unicode):
            raise ValueError("You must use unicode for field name")
        return self.values[field_name]


Collection = FIDataCollection()
DataManager = FIDataManager()
Properties = FIProperties()
record = DataManager.NewDataRecord(1)

User Defined Transform - per Collection

DS seems to be very picky. The only working solution found, is

record = DataManager.NewDataRecord()
i = 0
while i < Collection.Size():
    Collection.GetRecord(record, i + 1)
    record.SetField(u'SECURE_ID',
                    unicode(hashlib.sha512(uuid.uuid4().hex).hexdigest()))

Not working with raising AttributeError,name are the following to snippets. (In contrast, those work in the mock.)

record = DataManager.NewDataRecord()
for record in Collection.records:
    record.SetField(u'SECURE_ID', # raise AttributeError,name
                    unicode(hashlib.sha512(uuid.uuid4().hex).hexdigest()))

record = DataManager.NewDataRecord()
num_o_records = Collection.Size()
for i in range(1, num_o_records + 1):
    Collection.GetRecord(record, i + 1)
    record.SetField(u'SECURE_ID', # raise AttributeError,name
                    unicode(hashlib.sha512(uuid.uuid4().hex).hexdigest()))

regex_replace: access violation

Following use of regex_replace results in an obscure access violation error message (see also regex_replace: access violation).

return regex_replace(
    workflow_name(),
    '^WF_(.+)(_\[a-zA-Z0-9\]+){2}$',
    '$1', # replace everything with first group
    null
);

This error is triggered by the use of the fourth parameter (legal) with null value. This function apparently has not been programmed in robust manner. It stands to reason that other DS functions behave the same way.
Solution: Either use the empty string '' instead of null value or omit the fourth parameter all together.

Designer: Display environment one is connected to

Is there an easy way to display and preferably permanently display the environment one is in? To me this is a security feature. I am thinking along colouring like many DB tools allow.

Nope, there is none. I double checked with SAP support. The data store pane displays the repository one is connected to. If you need to know the server you can look in the environment section of Designer in the options. Or start the switch object library dialogue.

Designer: Overview on checked out objects

Like the status command of subversion but graphically? There is none. I double checked with SAP support.

Designer: User defined transformation optiones pane empty

Looks like

Reason is unknown but the workaround (proposed in KBA 2520817) is to run Designer as administrator. I had been talking to SAP support hinting me to the workaround and KBA. They also sayd I should expect problems now and then when running Designer under an unprivileged user.

[Update]
After having run Designer as administrator the option pane is now filled even though I do not run Designer as admin...
Is the need to run Designer as administrator in order to initialise some things? World of wonder...

Push down: Why is there no full push down done?

Following has to be met by the data flow to be able to push down completely.

• Join sources of the same data store (or with database link between them)
• Load operation must not contain triggers
• Only query transforms used, no other transforms
• No use of un-pushable functions and operators in expressions, see SAP Note 2212730:
• ...
• But at least to_date is UN-pushable when thrown against timestamp database column, see Data services: to_date inhibits push down
• No use of un-pushable transforms , see SAP Note 2212730:
• ...

Stored procedure: Why can't I import overloaded stored procedures?

Overloading just not supported.

Stored procedure: How can I call a stored procedure with boolean parameter?

You cannot!!!
@#!"*#!!, you need to create one or more wrapping stored procedures handling the boolean parameter.

Custom functions: Why can't I create recursive function calls?

This is only possible when directly calling the calling function, i. e. the function calls itself. Indirect calls, i. e. over intermediary functions, are inhibited.

On NULLs and empty strings

• NULL on any side of = and <> makes the result FALSE. In comparisons this has to be taken into account, e. g. with IS NULL checks or NVL function calls.
• Emtpy strings and NULLs are not the same. In comparisons this has to be taken into account, e. g. with IS NULL checks or NVL function calls.

Trailing blanks in varchars

In comparisons, transforms and functions trailing blanks are ignored!

Target: How can I prevent duplicate rows?

You can try the "Auto correct load" option.

Table: Why do I not see template tables on the database?

The will be created only when the data flow has run successfully once.

Table: How can I use template tables in expressions, functions or transform options?

You have to convert them to normal tables be importing them.

Target: Can I commit several targets within a single transaction.

Select the target's "Include in transaction" option. This only works for targets of the same datastore. You control the load order with the "Transaction order" option. Tables with the same order are loaded simultanously.

Work flow: Call depth

It is infinite.

Work flow: Is recursive call possible?

Yes