9.5 Conversion Syntax
The file contains a series of conversion directives, one to a line, with the following formats:
9.5.1 Simple directives
Simple directives take no arguments:
-
DOUBLE_TO_SINGLE_QUOTES Change double quotes (") to single quotes (’) around literal strings.
-
MATCHES_TO_LIKE Change Informix_style ’matches’ clause to one using ’like’, and change * and ? to % and _ respectively. eg: matches ’X?Z*’ -> like ’X_Z%’
-
MATCHES_TO_REGEX Similar to ’matches-to-like’ but uses the Postgres style regular expression syntax, eg: matches ’X?Z*’ -> ~ ’^X.Z.*’
-
TABLE_ALIAS_AS Insert the word "as" before table alias names in a ’from’ clause eg: from ..., table1 t1, ... -> from ..., table1 as t1, ...
-
COLUMN_ALIAS_AS Insert the word "as" before column/expression alias names in a ’select’ clause eg: select ..., sum(amount) amt, ...-> select ..., sum(amount) as amt, ...
-
ANSI_UPDATE_SYNTAX Convert Informix-style "update ... set (..,..) = (..,..) " to the ANSI standard format "update ... set ...=..., ...=... " eg. update mytable set (col1,col2,col3) = ("01", "X", 104) where ...->update mytable set col1="01", col2="X", col3=104 where ...
-
CONSTRAINT_NAME_AFTER Move the constraint name in a constraint command to after the constraint definition, eg: ... constraint c_name unique ->... unique constraint c_name
-
CONSTRAINT_NAME_BEFORE Move the constraint name in a constraint command to before the constraint definition, eg: ... unique constraint c_name -> ... constraint c_name unique
9.5.2 Complex Directives
The following directive takes an argument ( in the rules below, replace the word "string" with the appropriate values ):
-
SUBSTRING_FUNCTION = string Change Informix-style string subscripting to a function call, Replace ’string’ with the name of the sql function. eg. where ... foo[3,5] = .... -> where ... substr(foo,3,3)
9.5.3 REPLACE directives
Search and replace is not case-sensitive. For legibility, lower case is used in the rules for search/replace strings to distinguish them from the keywords (in upper case).
You may leave the replacement string (after the = sign) blank. This will have the effect of removing the matched string from the converted output.
-
REPLACE before = after Replace any occurrence of the string ’before’ with ’after’, eg.
REPLACE rowid = oid
REPLACE current year to second = sysdate
REPLACE today = date(now())
-
REPLACE_EXPR before = after Replace only if the ’before’ text is found in an expression or where an expression is allowed, such as in a where clause or a select clause. eg.
REPLACE_EXPR sysdate = current year to second
REPLACE_EXPR today = date(now())
-
REPLACE COMMAND before = after Replace, but only if the whole SQL statement matches the ’before’ string eg.
REPLACE_COMMAND set isolation to dirty read =
The example above has the effect of completely erasing the command.
Full list of available settings :
ADD_CASCADE
ADD_SESSION_TO_TEMP_TABLE
ANSI_UPDATE_SYNTAX
CHAR_TO_DATETIME
CHAR_TO_INTERVAL
COLUMN_ALIAS_AS
CONSTRAINT_NAME_AFTER
CONSTRAINT_NAME_BEFORE
DATETIME_EXTEND_FUNCTION
DOUBLE_TO_SINGLE_QUOTES
DTYPE_ALIAS
ESQL_AFTER_DELETE
ESQL_AFTER_INSERT
ESQL_AFTER_UPDATE
ESQL_UNLOAD
ESQL_UNLOAD_FULL_PATH
FAKE_IMMEDIATE
FULL_INSERT
IGNORE_CLOSE_ERROR
IGNORE_DTYPE_VARCHAR_MIN
IGNORE_OWNER
INSERT_ALIAS
INTERVAL_EXTEND_FUNCTION
LIMIT_LINE MATCHES_TO_GLOB
MATCHES_TO_LIKE
MATCHES_TO_REGEX
MONEY_AS_DECIMAL
MONEY_AS_MONEY
NO_DECLARE_INTO
NO_FETCH_WITHOUT_INTO
NO_ORDBY_INTO_TEMP
NO_OWNER_QUOTE
NO_PUT
NO_SELECT_WITHOUT_INTO
NO_SERIAL_START_VALUE
OMIT_INDEX_CLUSTER
OMIT_INDEX_ORDER
OMIT_NO_LOG
QUOTE_OWNER
RENAME_COLUMN_AS_ALTER_TABLE
RENAME_TABLE_AS_ALTER_TABLE
REPLACE
REPLACE_COMMAND
REPLACE_EXPR
REPLACE_SQLCONST
REPLACE_SQLFUNC
SELECT_INTO_TEMP_AS_CREATE_TEMP_AS
SELECT_INTO_TEMP_AS_DECLARE_GLOBAL
SIMPLE_GRANT_SELECT
SIMPLE_GRANT_UPDATE
SQL_CURRENT_FUNCTION
STRIP_ORDER_BY_INTO_TEMP
SUBSTRING_FUNCTION
SWAP_SQLCA62
TABLE_ALIAS_AS
TEMP_AS_DECLARE_GLOBAL TEMP_AS_TEMPORARY
USE_BINDING_FOR_PUT
USE_DATABASE_STMT
USE_INDICATOR