Special characters and capitalization

From version 9.9, PPM Process Extractor JDBC-2-PPM supports the usage of special characters and case-sensitive capitalization in schema, table, and field names in SQL databases.

SQL expressions must be in brackets so that database systems can process them if they contain special characters in schema, table, and column names and case-sensitive spelling, for example like in the following SQL statement for an SQL server database.

SELECT [column with space] FROM [USER.1].[table $%&]

Depending on the database system, the syntax may differ. For example, under Oracle and DB2, names must be set in apostrophes, and under SQL server, square brackets can be used.

To support special characters and case-sensitive spelling, the system configuration JdbcConfig.dtd contains the non-standard-sql-identifiers (true | false) attribute. If the non-standard-sql-identifiers attribute has the value true, the schema, table, and column names will be set in limiting characters suitable for the relevant database type.

<!ATTLIST databasesettings

name ID #REQUIRED

dbtype (ORACLE | DB2 | SQLSERVER | OTHER) #REQUIRED

precisionoftime (SECOND|MILLISECOND) "SECOND"

non-standard-sql-identifiers (true | false) "false"

In the database systems, limiting characters must not be part of database identifiers, such as schema, table, and column names.

Backwards compatibility

To ensure compatibility with existing configurations, the value false is assigned to the attribute non-standard-sql-identifiers in CTK when opening and saving or migrating a configuration. An existing value is not automatically overwritten, but can be changed manually in CTK.

When creating a new configuration, CTK sets this value to true by default.