Skip to main content
OutSystems

Integrate with External SQL Server (Case Sensitive) using Integration Studio to import entities

Introduction

When using SQL Server as a database backend, OutSystems Platform is only certified to be installed or to integrate with SQL Server databases if they are installed as case-insensitive. This means:

The collations for the instance and the database can be different, as in the example above; but both must be Case Insensitive (CI).

A priori notes

Please bear in mind that OutSystems Platform does not formally support this integration mode at this point. This document presents alternatives which should allow you to do the integration most of the times, but constitute no guarantee of correct behavior at all times.

Use of SQL Server with the OutSystems Platform is at this point only supported in a Case Insensitive manner, so it is possible that you encounter other problems when accessing your database.

 

Integrating with Case Sensitive databases

In some situations, customers may want or need to integrate with databases Case Sensitive collations. While this is not formally possible, such integration may be possible in a limited fashion.

This article provides some hints into attaining such integration, the roadblocks that may be hit, and how to overcome them.

Bear in mind that installing the platform data model in a case sensitive instance is not possible!
Attempting to run a platform with such setup is not supported and OutSystems will not be able to assist with any problems that may exist.

 

Known limitations

The following are the known limitations in this integration scenario. In addition to the limitations, we present a workaround that allows such integration to be performed in a limited fashion:

  • Importing entities in Integration Studio.
  • Case of column names
  • Queries

 

Importing entities in Integration Studio

When trying to import entities from a case sensitive SQL Server instance, the first problem is when using Integration Studio. In the wizard, after selecting the database connection, you will be confronted with an error message similar to the one below:

Error retrieving tables: Invalid object name 'NamesDatabase.information_schema.referential_constraints'.

The above happens because Integration Studio is trying to query the system view with the lowercase name, which is invalid in a case sensitive instance.

As a workaround: to overcome the above error, you need to create a lowercase object with that name. The below script (after adaptation) can be used:

    CREATE SCHEMA [information_schema] AUTHORIZATION [<YOUR_DATABASE_USER>]
    GO

    create view information_schema.referential_constraints
    as
    select CONSTRAINT_CATALOG as constraint_catalog, 
         CONSTRAINT_SCHEMA as constraint_schema, 
         CONSTRAINT_NAME as constraint_name,
         UNIQUE_CONSTRAINT_CATALOG as unique_constraint_catalog,
         UNIQUE_CONSTRAINT_SCHEMA as unique_constraint_schema,
         UNIQUE_CONSTRAINT_NAME as unique_constraint_name,
         MATCH_OPTION as match_option,
         UPDATE_RULE as update_rule, DELETE_RULE
    from [<YOUR_DATABASE>].INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Remember to fill in the placeholders with actual information from your installation.

Case of column names

When querying the database, the platform generates the queries in a way that all column names get UPPERCASED. So if you have columns with names that are not uppercase, you will see runtime errors.

For example, if you try to import the below table:

After creating an eSpace that simply scaffolds the table, you will get a runtime error complaining of invalid table columns:

As a workaround, create views for all tables, ensuring that the column names are uppercase. If you have columns with spaces or strange characters, remove them as well.

Example for our table above:

    USE [<YOUR_DATABASE>]

    CREATE VIEW FIRSTNAMES_VIEW
    AS
    SELECT id as ID,
        lowercase as LOWERCASE,
        UPPERCASE as UPPERCASE,
        CamelCase as CAMELCASE
    FROM FirstNames 

Then, import the view instead.

 

3. Queries

Remember that your queries will also be case sensitive, as the database. So if you search for data with the wrong case, you won't find it.

In the example, John's name is written either as john, JOHN or John. If you search for JOhn, it will not find any results.

Also, in advanced queries / SQL nodes, if you use variables and mistake the case of their name, the query will not run correctly:

 

Applies to

OutSystems Platform all versions (last reviewed for 9.1.600.0).

OutSystems is currently reviewing these limitations, so these should be overcome in future versions of the OutSystems Platform.