Help Center/ Database and Application Migration UGO/ User Guide/ Object Migration/ Converting Syntax
Updated on 2024-11-07 GMT+08:00
View PDF
Share

Converting Syntax

Converting DATE and TIME Functions

When you convert the syntax of target database GaussDB 3.2 or earlier, the DATE and TIME functions listed in Table 1 return the transaction start time. The time you obtained in the source database is the current system time. In long transactions, the time obtained from the source database is different from that obtained from the target GaussDB database. If the converted statement contains these functions, you need to analyze whether there are long transactions and confirm the impact. If yes, manually modify the functions to ensure final accuracy.

Table 1 DATE and TIME functions

No.

Function

1

CURRENT_DATE

2

CURRENT_TIME

3

CURRENT_TIME(precision)

4

CURRENT_TIMESTAMP(precision)

5

LOCALTIME

6

LOCALTIMESTAMP

7

LOCALTIME(precision)

8

LOCALTIMESTAMP(precision)

Oracle -> GaussDB

GaussDB supports only common synonyms and does not support public synonyms. UGO will convert public synonyms in an Oracle database to common synonyms of the public schema in a GaussDB database. For details, see Table 2.

If different users in your database have public synonyms with the same name, there are synonym conflicts. You need to reconstruct the synonyms with the same name before migrating them. For details, see Table 3.

Table 2 Syntax conversion 1

Source SQL in Oracle

Converted SQL in GaussDB

CREATE PUBLIC SYNONYM u1.syn FOR u1.object1;

CREATE SYNONYM public.syn FOR u1.object;
Table 3 Syntax conversion 2

Source SQL in Oracle

Converted SQL in GaussDB

Whether Synonyms Conflict

CREATE PUBLIC SYNONYM u1.syn FOR u1.object1;

CREATE SYNONYM public.syn FOR u1.object1;

No

CREATE PUBLIC SYNONYM u2.syn FOR u1.object2;

CREATE SYNONYM public.syn FOR u2.object2;

Yes

Converting Syntax from PostgreSQL to GaussDB

Mode compatibility:

  • Syntax can be migrated from PostgreSQL to GaussDB (O-compatible mode).
  • In the GaussDB (O-compatible mode) database, an empty string is stored as null. If True is returned in the result after select '' is null; is executed, an empty string is also stored as null in the Oracle database.

Impact scope:

  • If the source database type is PostgreSQL and the target database type is GaussDB (O-compatible mode), SQL statements containing empty character strings or null need to be reconstructed.
  • The impact scope includes DML, DDL, and system functions, such as select'' is null; and select regexp_matches('test1', 'test1', '');.
  • UGO can evaluate the IS NULL syntax and empty string syntax during migration from PostgreSQL to GaussDB.

Converting Syntax from Microsoft SQL Server to MySQL

The DATETIME data type of Microsoft SQL Server supports addition and subtraction operations. However, UGO cannot accurately identify this scenario, so the DATE_ADD function is recommended in the MySQL database for reconstruction.

Parent topic: Object Migration