Case-sensitive database technologies

Overview

When configuring Redpoint Interaction (RPI) to connect to a case-sensitive database, either for a migration or for the first time, there are some considerations to keep in mind.

When configuring RPI, you can choose to qualify queries (object references wrapped in quotes), or leave them unqualified. This is an important setting when you are deploying a case-sensitive database, and we recommend not qualifying queries when connected to a case-sensitive database. Leaving your queries as unqualified means that your database controls the casing, which is ideal for case-sensitive databases.

Currently, only unqualified objects are allowed when working with case-sensitive databases.

To determine whether queries will be qualified, you’ll use the Disable Database Object Qualification (DisableDatabaseObjectQualification) setting.

This page discusses the setting in further detail and reviews some of the implications of qualifying queries vs not qualifying queries, as well as additional considerations for migrating from a non-case-sensitive database to a case-sensitive database.

System configuration setting

The Disable Database Object Qualification setting (DisableDatabaseObjectQualification) controls if database objects in queries are qualified. To be qualified, a database object must be wrapped in quotations.

The default configuration for this setting is False, which means that the queries are qualified and have quotes around object references. If you set the configuration to True, then the query will not be qualified, meaning the references will not be wrapped in quotes. This setting is an on/off switch; either your objects are qualified or they are not, without exception.

• False (default): when set to False, the objects, like schemas, table names etc., all have to exist in the database exactly as they're referenced within RPI.

• True: when set to True, regardless of how the query is configured, the database default casing setting is used to reference database objects.

Our recommendation is to not qualify queries (setting = True), and let the database determine the correct casing of the tables in the database. This will also cause the tables that RPI creates (Offer History, web tables, etc.), to be generated in the same case as the rest of the database.

When object qualification is turned off (DisableDatabaseObjectQualification is set to True), all queries sent to the database are interpreted (converted) to a default case (upper or lower), depending on the database engine. For example, if a query includes an unquoted table reference in mixed case, and the database's default casing is upper case, then the table reference will be executed in upper case.

In this configuration, every schema/table/field must be created with the default casing, otherwise it will not work with RPI.

Configuring the DisableDatabaseObjectQualification setting

To configure the DisableDatabaseObjectQualification setting:

1. From the RPI home page, go to the quick access menu and select Configuration.

2. On the left-side menu, go to the Environment section and choose System Configuration.

3. In the Database group, select DisableDatabaseObjectQualification.

4. In the Selected Configuration Details section, select the Value checkbox to mark this setting as True (not qualify queries).

Example of a qualified query (setting False)

SELECT COUNT(*)
FROM "DBO"."INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY" a1
WHERE a1."CITY" IN ('BOSTON')

Example of a unqualified query (setting True)

SELECT COUNT (*)
FROM DBO.INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY a1
WHERE a1.CITY IN ('BOSTON')

Migration considerations

If you’re new to RPI, and you’re performing your initial connection to a case-sensitive database, we recommend not qualifying queries (DisableDatabaseObjectQualification is set to True). However, if you are migrating from a non case-sensitive database to a case-sensitive database, there are some additional considerations.

Though it goes against our recommendation, if you do qualify the queries, then you'll need to ensure that all the RPI references to objects, including Offer History, match the way that RPI constructs the queries.

The most important thing to consider when migrating is around maintaining the casing of the Offer History tables as you already have it, or converting to the default casing of the database technology you're migrating to.

Keeping the casing of the tables in the existing database the same in the new database will facilitate the least amount of change, and should result in the smoothest transition to the new database technology. This may not align with the corporate initiatives or project requirements, and you may need to consider changing the casing.

Unqualified configuration considerations

When objects are not qualified (DisableDatabaseObjectQualification is set to true), queries will be converted based on the database configuration. This will cause the tables created by RPI, including Offer History, to be generated in the default casing of the database.

With this approach, the expectation is that all tables created in the case-sensitive database will follow the database default configuration, and only objects that adhere to that configuration will be accessible to RPI. For example, you cannot add a mixed case table to the database/schema and access it from RPI.

If you take this approach, you won’t be able to change back to qualified queries at a later date without unknown results or potential risks/failures.

Case sensitive databases and mixed case Offer History queries

When RPI makes queries against the Offer History (OH) tables, as a part of executing audiences and interactions, it makes mixed case references to some of the OH tables. If you don't let RPI create the tables with the mixed case definition, or create them exactly as RPI would, then you’ll get errors with some queries. Below are some examples of the naming conventions for the tables that will have mixed case references.

You can create the Offer History tables and configure them to match the default casing of the database, then turn on the Disable Database Object Qualification setting, which turns off qualification. This will cause the database to ignore the mixed case references made by RPI and assume the default casing of the database for all objects.

These are some examples of the Offer History table references that are made by RPI where the casing cannot be influenced. You can set the names and casing of the Offer History and Offer History Metadata table in the Audience Definition, but you cannot set the casing for some of the other tables which have mixed case suffixes.

• OFFERHISTORY_SandBox

• OFFERHISTORY_Details

• OFFERHISTORY_States

• OFFERHISTORY_META_SandBox

If you are planning to change the casing of the Offer History tables, there are some things to keep in mind:

• If you are going to change the casing of the tables and fields to match the new database naming convention, review the above section detailing the configuration of the database object qualification setting to make sure you have it set appropriately for your business needs.

• RPI creates the Offer History table and field names in mixed case, and any query references that it makes using them are in mixed case.

  • If you plan to go with a fixed-case approach, then you'll need to set the Disable Database Object Qualification setting to True for RPI to reference the specifically-cased database objects. You'll also need to create the Offer History tables in the correct casing (what the DB expects) when you migrate the data to the new database.

  • If you go this route, then you'll never be able to introduce mixed case tables or fields into RPI, as it won't be able to access the tables. This is because the queries won't be qualified, and the objects will be treated based on the default casing of the database (DB) you're using.

Currently, you're not able to control the query casing references for the Offer History tables that are not configured in the Audience Definitions. An example is Offer History Details. If you define the Offer History table as OFFER_HISTORY in the Audience Definition, RPI will still reference the detail table with this casing in the query as OFFER_HISTORY_Details.