Welcome Guest! Log in

Storing variables in a database for global use in several processes

    Metadata Variables may be used to store persistent values and re-use them. They are a powerful way to define and use global variables in several processes.

    In this example, we define a "START_DATE" variable, which we can read and write among different processes.

    The principle is that:

    • the variable value is stored in a database table
    • any process can read / update the variable value

    Setting up the Metadata

    Database schema

    We choose to store the variables in the Runtime's log database schema. In our example it is the default H2 Database.

    Of course you can choose any database schema of your choice.

    The Runtime will simply create a table named "IND_SESSION_VARIABLE_VAR" into this schema (we will see the table content later in this article).

    279 01

    • Driver: org.h2.Driver
    • Url: jdbc:h2:tcp://localhost:42100/sessions/internalDb/sessionLogs
    • User: sa

    This Database metadata only needs the server and schema nodes (no table needed).

    Variable Set

    Now, we create a Variable Metadata file and we add a Connection node to it (Right click on the Set / New / Connection). 

    We named it "RuntimeLogSchema".

    And then we drag and drop the LOGS schema into this RuntimeLogSchema Connection node.

    Finally we create the variable with these properties:

    • Name: START_DATE
    • Saving Connection: Set.RuntimeLogSchema
    • Type: String
    • Default Operation: SetToSavedValue

     279 02

    Using the metadata in processes

    Init Start Date process: one shot initialization

    For future use, we need to initalize the variable so that it is registered into the database.

    So we simply drag and drop the variable into a new process, and configure the action like this:

    • VAR_OPERATION: SetValue
    • VAR_VALUE: 2001-01-01

    After execution, we can see the Variable in the Session's variables (at the bottom):

    279 03

     

    And we can also see the variable in the IND_SESSION_VARIABLE_VAR table:

    279 04

     

    A few words about this table's columns:

    • VAR_NAME is full technical name of the variable.
    • VAR_TYPE is the variable type
    • VAR_NO_LINE: when the value is a long string, it may be written over several rows (depending on the database's varchar max size). Usually there is only one line.
    • VAR_VALUE: the value
    • VAR_CONF: the configuration which applies. Note that a variable may be stored with one value per configuration in the same table.
    • VAR_DATE: when the variable was last saved
    • VAR_SESS_ID: the session that did the last save

    LoadCustomerOrders process: reading the variable

    Now, we can develop processes which rely on this persistent variable.

    This process only reads the variable value and uses it. It does not modify the variable value.

    The "START_DATE" action was created simply by drag'n'droping the Variable in the process. The default operation applies (SetToSavedValue, as we chose in the metadata definition).

    "Load data" is a subprocess where we use the variable, for example see the parameter "ReportTitle" using %{START_DATE}%, and see its value at the bottom right of the screenshot:

     279 05

     

     PublishInvoices process: updating the variable

    This process first loads the variable value, and uses it (somewhere in the subprocess).

    And finally, the "START_DATE Update" step is changing the variable value. Here is how this step is set:

    • VAR_OPERATION: SetValue
    • VAR_VALUE: %x{md:formatDate("yyyy-MM-dd")}x%    (this is a tip to produce the current date with the specified date format)

     

    279 06

     

    As you can see at the bottom left, after execution of the final step, the variable value has changed.

    And we can see this also in the table:

    279 07

     

     

    How to reference a schema in a Variable's Refresh Query

      Variables can be initialized from a query.

      You can make this query refer to metadata with the following syntax :

       

      Comments :

      - The schema will be the schema that is referenced in the Refresh Connection.

      - If the medata that holds this schema has Configurations, then this configuration will apply.

       

      This makes an easy way to load Variables with varying schema names, using Configurations.

      How to use the Runtime date/time at execution

        It is a common need to use system timestamps, and there is a variety of ways to use them, regarding:

        - which timestamp is needed : the date/time at which the current session started, or the current real date/time

        - how it is going to be used : as an insert value, as a timestamp in a filename, as a test condition...

        This article shows some examples on how to use date/times in Stambia.

        Read more: How to use the Runtime date/time at execution

        Articles

        Suggest a new Article!