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;