Striim 4.0.4 documentation

Table of Contents

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

$

dollar

36

%

percent sign

37

see note below

&

ampersand

38

(

open parenthesis

40

)

close parenthesis

41

+

plus

43

,

comma

44

-

hyphen

45

:

colon

58

;

semicolon

59

<

less than

60

=

equals

61

>

greater than

62

?

question mark

63

@

at symbol

64

[

opening bracket

91

not supported for MSJet

]

closing bracket

93

not supported for MSJet

^

caret

94

_

underscore

95

see note below

{

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"

    underscore

    ab_c

    "ab\\_c"

    "ab\_c"

  • 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;