Striim 4.1.0 documentation

Using non-default case and special characters in table identifiers

Striim supports table and column names with non-default case and/or containing special characters in Tables property values for the following databases when read by their own CDC readers, Database Reader, or Incremental Batch Reader and when written to by Database Writer:

  • MariaDB

  • MySQL

  • Oracle Database

  • Oracle GoldenGate

  • PostgreSQL

  • SQL Server

Striim also supports special characters in

  • Azure Synapse Writer

  • Snowflake Writer

Supported special characters

character

name

ASCII code

notes

space

32

!

exclamation mark

33

#

number sign

35

not supported in Azure Synapse table names

$

dollar

36

%

percent sign

37

not supported in Azure Synapse table names; also see note below

&

ampersand

38

(

open parenthesis

40

)

close parenthesis

41

+

plus

43

not supported in Azure Synapse table names

,

comma

44

-

hyphen

45

:

colon

58

;

semicolon

59

<

less than

60

=

equals

61

>

greater than

62

?

question mark

63

not supported in Azure Synapse table names

@

at symbol

64

[

opening bracket

91

not supported for MSJet

]

closing bracket

93

not supported for MSJet

^

caret

94

_

underscore

95

With most sources and targets, this is supported without escaping the name in double quotes.

{

opening brace

123

|

vertical bar

124

}

closing brace

125

~

tilde

126

Notes on using special characters

  • Identifiers containing special characters must be escaped using double quotes: for example, MySchema."My@Table"

  • The following characters must be further escaped as follows:

    character

    example identifier in database

    example escaped in TQL

    example escaped in UI

    double quote*

    ab"c

    "ab\\"c"

    "ab\"c"

    backslash

    ab\c

    "ab\\\\c"

    "ab\\c"

    percent*

    ab%c

    "ab\\%c"

    "ab\%c"

    *not supported in Azure Synapse table names

  • In multi-part table names, each identifier containing special characters must be escaped separately: for example, "My@Schema"."My@Table".

  • In three-part table names, special characters are not supported the first part: for example, MyDB."My@Schema"."My@Table".

  • When table names are not escaped, Striim will use the database's default case. For example, If an Oracle table is named MyTable, to read it you must specify the Tables property as "MyTable". If you omit the double quotes, Striim will attempt to read MYTABLE and will fail with an error that the table is not found.

  • Special characters are supported in the ColumnMap function, for example, ColumnMap("ID"="Pid").

  • When replicating data, if the source and target Tables properties use the % wildcard in double quotes (for example, "My@Schema"."%", case and special characters are preserved, provided the WAEvent output of the source is not parsed (see Parsing the data field of WAEvent) before reaching the target. If the output of the source is parsed, special characters will be lost unless stored in a field as strings. For example:

    CREATE TYPE ParsedDataType(
  TableName String,
  ...
);
CREATE STREAM OracleTypedStream OF ParsedDataType;
CREATE CQ ParseOracleCDCStream
  INSERT INTO OracleParsedStream
  SELECT META(x, "TableName").toString(),
  ...
  FROM OracleCDCStream x;
In this section: