# Datasources, Tables and InlineViews

reviewed: 2 March 2025

Datasources

A CaseMaster application can connect to multiple databases (called datasources for technical-historical reasons). Each application must have at least one datasource known as the primary datasource. The primary datasource must point to a relation database that has a number of essential CaseMaster specific tables (such as user, userLogin, session, sequence and others).

You will that many CaseMaster applications only have the primary datasource.

The set-up of datasources is typically found in one of the environment-specific configuration include files (see here). The following is an example from a random incDevelopment.cms file:

resource main
    <
        dataSources: <@configuration/dataSources
            primary: <@configuration/dataSource/sqlite
                type: dataSourceType.primary
                connectionString: $formatString( 'Data Source={0}', fileSystem.buildPath( app.runtimeDir(), '..', 'data', 'database', 'cm2.0Training.db' ) )
            >
        >
    >
end-resource
  • This specific example uses the SQLite RDBMS
  • The connection string is a standard database connection string; see for examples here
  • There can only be one entry with type primary; any other datasource would have the type alternative (of which there can be multiple)
  • The datasource type channel is reserved for a future CaseMaster version

The following is an example of an incDevelopment.cms file with a primary and alternative datasource.

resource main
    <
        dataSources: <@configuration/dataSources
            primary: <@configuration/dataSource/MsSql
                type: dataSourceType.primary
                connectionString: 'Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=eurofins;Data Source=bdispa'
            >
            ptv: <@configuration/dataSource/MsSql
                type: dataSourceType.primary,
                connectionString: 'Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=eurofins.ptv;Data Source=bdispa'
                formatSql:false()
            >
        >
    >
end-resource

Advanced Settings

There are many low-level tags in the <@configuration\dataSource> qualifier (and the related qualifiers for various different RDBMS-es). Most are very low-level and can be left to their default values. Some may help on specific (older) versions of an RDBMS, in high-volume / high-concurrent environments, dealing with legacy systems or with an otherwise unsupported RDBMS that can be accessed using ODBC.

Tag Usage
type The type of datasource; dataSourceType.primary, dataSourceType.Alternative or dataSourceType.Channel; each application must have on (and only one) datasource of type primary. Alternative is used for other RDBMS datasources and channel for non-RDBMS datasources
class The .Net class that is used to handle all database interactions; highly advanced feature allowing to add support for specialized RDBMS-es / datasources
connectionString The DSN connection string used to conect to the database
connectionMode The connection mode, dataSourceConnectionMode.Default or dataSourceConnectionMode.ODBC; using dataSourceConnectionMode.ODBC will force CaseMaster to use the .Net OdbcConnection object rather than a specialised connection object for the given database type (available for MSSQL, SQLite and Postgress)
database The type of database; see next paragraph
schemaOwner See paragraph on schemas
schema See paragraph on schemas
schemaMode See paragraph on schemas
maxObjectNameLength The maximum length allowed for object names
maxAliasNameLength The maximum length allowed for alias names
timezoneOffset The number of hours added to times and timestamps; can be useful when the database server operates in a different timezone that the application server. CaseMaster will take this into consideration when writing and reading time and timestamp values and when referring to times and timestamps in where clauses
dateFormat The date format in Casemaster syntax (see format() function)
timeFormat The time format in Casemaster syntax (see format() function)
timeStampFormat The timestamp format in Casemaster syntax (see format() function)
sequenceMode Either dataSourceSequenceMode.Internal or dataSourceSequenceMode.Sequence; specifies how automatics are generated; default is internal meaning that CaseMaster handles it (using the sequence table); sequence means it is handled by the RDBMS (only available for MSSQL, DB2, Oracle and Postgress)
timeout Time (in seconds) to wait to open a connection
retainOpenConnection Keep the primary database connection open between transactions (defaults to false as ytypically handled optimally by OLEDB layer )
nullTranslations See paragraph on null translations
objectIdentifierCasing Use to force object names to either upper (textCase.Upper) or lower case (textCase.Lower); defaults to textCase.Mixed
useSqlInsert Always use SQL to insert records (instead of recordsets); see also here on the forceSQLInsert tag
useSqlUpdate Always use SQL to update records (instead of recordsets); see also here on the forceSQLUpdate tag
allUnicodeStrings Force that all strings are unicode strings
caseInsensitiveCompares Force that all compares are case insensitive regardless of collation settings in RDBMS
formatSql Does SQL needs to be formatted when included in trace files. See here
readNoLock See paragraph on readNoLock
jsonValueFunction See paragraph on JSON values

Schemas

When CaseMaster generates SQL, it can take schemas into consideration. For example:

-- No schema
select name from client

-- Schema and schema owner
select name from sandbox.dbo.client

If you have a schemaOwner and / or a schema, the schemaMode dictates how this is being used.

  • dataSourceSchemaMode.Concat: simply concatenate schema, schema-owner and table name
  • dataSourceSchemaMode.Qualified: seperate schema, schema-owner and table name with dots and, where relevant, embed object names (e.g. using square brackets for SQL Server)

Possible use:

resource main
    <
        dataSources: <@configuration/dataSources
            primary: <@configuration/dataSource/sqlite
                type: dataSourceType.primary
                connectionString: $formatString( 'Data Source={0}', fileSystem.buildPath( app.runtimeDir(), '..', 'data', 'database', 'cm2.0Training.db' ) )
                schemaMode: dataSourceSchemaMode.Qualified
                schema: 'sandbox'
                schemaOwber: 'dbo'
            >
        >
    >
end-resource

Null Translations

Null translations can be useful when dealing with (legacy) database that represent null values with specific values. The following example shows how this can be used:

resource main
    <
        dataSources: <@configuration/dataSources
            primary: <@configuration/dataSource/sqlite
                type: dataSourceType.primary
                connectionString: $formatString( 'Data Source={0}', fileSystem.buildPath( app.runtimeDir(), '..', 'data', 'database', 'cm2.0Training.db' ) )
                nullTranslations: <
                    string: '-'
                    date: #1jan1900#
                >
            >
        >
    >
end-resource

In this example, there are no null values in string or datetime columns. However, a string value of - and a date value of 1JAN1900 must be considered null.

CaseMaster will taker this into consideration when writing and reading data and when referring to null values in where clauses.

You can use the following CaseMaster basic datatypes for null translations:

  • string
  • double
  • long
  • boolean
  • date
  • timestamp
  • time

ReadNoLock

The readNoLock tag is used to specify whether reads are done without claiming a read-lock. The tag can be set to one of 4 values:

  • dataSourceReadNoLockMode.Never: Never add a hint to avoid a read lock even when the RDBMS supports it
  • dataSourceReadNoLockMode.Always: Always add a hint to avoid a read lock (when the RDBMS supports it)
  • dataSourceReadNoLockMode.BySize: Only add a hint to avoid a read lock (even when the RDBMS supports it) for large and medium sized entities (see here)
  • dataSourceReadNoLockMode.ByTag: Only add read-n-lock hint when BO has the tag readNoLock

For SQL server we recommend to use readNoLock: dataSourceReadNoLockMode.Always.

JSON Values

CaseMaster supports native JSON as database values. This feature is used internally for multi-language string values (e.g; allowing multi-language values as part of reference data; see here).

JSON is natively supported for the following RDBMS-types:

  • SQL Server (after 2012)
  • MySQL
  • Oracle
  • SQLite
  • Postgress
  • DB2

You are able to use the jsonValueFunction tag to specify a scalar function (available in our RDBMS) to extract a value from the JSON held in a column. This function must take two parameters: a value and a JSON path.

A rudimentary version of a SQL Server scalar function that can be used to add multi-language JSON support to older SQL Server versions would be:

ALTER FUNCTION [dbo].[jsonValue]
(
    @value nvarchar(max),
    @path nvarchar(10)
)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @language nvarchar(5);
    DECLARE @start int;
    DECLARE @end int;
    DECLARE @return nvarchar(MAX);
    DECLARE @tmp nvarchar(MAX);

    -- Imagine value 
    -- { "EN": "Text EN", "AR" :"Text AR" }
    -- and path @EN
    -- need to return "Text EN
    SET @return = ''
    SET @language = '"' + SUBSTRING( @path, 2, 2) + '":'

    SET @value = REPLACE( @value, ': null', ': ""')

    IF CHARINDEX( @language, @value ) = 0 AND @language<>'EN'
    BEGIN
        SET @language = '"EN":'
    END

    -- { "EN": "Text", "AR" :"Text" }
    SET @tmp = RTRIM(LTRIM( SUBSTRING( @value, CHARINDEX( @language, @value ) + 5, 9999 ) ))
    -- "Text", "AR" :"Text" }
    SET @tmp = SUBSTRING( @tmp, 2, 9999 )
    -- Text", "AR" :"Text" }
    SET @end = CHARINDEX( '"', @tmp ) - 1

    return SUBSTRING( @tmp, 1, @end )

END

Database Types

CaseMaster supports the following databases:

  • Microsoft SQL Server (all versions, including Express and Developers Editions, for 2016, 2017, 2019, 2022)
  • Postgres (all versions from 9 to 16 although 9, 10 and 11 are no longer supported by The PostgreSQL Global Development Group)
  • MySQL (all supported version of the Standard- and Classic Edition)
  • SQLite (all sub-versions of version 3)
  • Oracle (19, 21 and 23)
  • DB2 (all versions of the Community Edition,Starter Edition, Standard Edition and Advanced Server Edition)
  • Access (Actually JET, ACE 14 and beyond)
  • ODBC (General purpose, SQL adheres to minimal subset of standard SQL2016)

The CaseMaster values are:

  • database.MsSql
  • database.PostgreSQL
  • database.MySQL
  • database.SQLite
  • database.Oracle
  • database.DB2
  • database.Access
  • database.ODBC

The <@BO> Datasource Tag

Each entity is linked to a datasource. By default this is the primary datasource.

You can use the datasource tag to link an entity explicitly to a datasource. For example:

resource main
    <@bo
        table: 'ETPT_TOUR_HEADER'
        datasource: 'ptv'
        primaryKey: 'etpt_exph_reference'
        attributes: <
            etpt_exph_reference:<@bo/attribute
                label: 'ETPT_EXPH_REFERENCE'
                column: 'ETPT_EXPH_REFERENCE'
                optional: true()
                dataType: dataType.decimal
                length: 17
            >
    // More stuff here

Table, Inlineview or Memory Only

An entity can be linked to a table through the table tag of the <@BO> qualifier. In the previous paragraph we have seen that we can use the datasource tag to specify in which database this table resides.

inlineview Is an alternative to the table tag and can be used to link the entity to a query rather than a table. The following shows an example:

resource main
    <@bo
        inlineView: 'select * from IMPP_IMPORT_PROTOCOL where status=8'
        datasource: 'ptv'
        primaryKey: 'impp_imph_reference'
        attributes: <
            impp_imph_reference:<@bo/attribute
                label: 'IMPP_IMPH_REFERENCE'
                column: 'IMPP_IMPH_REFERENCE'
    // More stuff here

The inlineView feature is sometimes easier than creating a view and referring to that view in the table tag.

A third scenario (other than table and inlineView) is to have neither. This modus is known as memory only. The entity is simply not designed to process data that resided in a database. This can be useful in scenarios such as:

  • Prompt the user for parameter for print instruction (number of copies, which printer, etc); n o need to store these values but simply use them to fulfill a print request and forget about the parameters again
  • A BO that is used to hold the details for a web service
  • Etc

<End of document>