Source Code SQL Conversion

System Schema Conversion (MySQL > DM)

SQL statement conversion example:

• Original SQL statement (MySQL):

  select column_name, column_type, column_comment from information_schema.columns;

• New SQL statement after conversion (DM):

  select sc.name column_name, type$ column_type, scc.comment$ column_comment from sys.syscolumns sc left join sys.syscolumncomments scc on sc.name = scc.colname;

REPLACE INTO Syntax Conversion (MySQL > DM)

MERGE INTO can be used as a replacement for REPLACE INTO. In the DM database, specify a unique key to use the MERGE INTO syntax. Data is updated when the unique key exists and data is inserted when the unique key does not exist.

SQL statement conversion example:

• Original SQL statement (MySQL):

  replace into sys_user_role (user_id, role_id) values (3, 3);    -- user_id is the unique key. If user_id exists, data is updated. If user_id does not exist, data is inserted.

• New SQL statement after conversion (DM):

  MERGE INTO RY.SYS_USER_ROLE 
  USING (select 3 as user_id, 3 as role_id) tmp        -- Data to be updated or replaced forms a temporary table tmp using the select method.
  ON  RY.SYS_USER_ROLE.user_id = tmp.user_id           -- The user_id field in the SYS_USER_ROLE table is used as the unique key and is compared with the user_id field in the inserted data.
  WHEN matched THEN                                    -- Data is updated in the table when the user_id field to be inserted exists.
  UPDATE SET RY.SYS_USER_ROLE.role_id = tmp.role_id    -- The role_id field is updated in the table.
  WHEN NOT matched THEN                                -- Data is inserted if the user_id field to be inserted does not exist.
  INSERT VALUES ( tmp.user_id, tmp.role_id);

NEXTVAL FOR Syntax Conversion (DB2 > GoldenDB)

GoldenDB does not support the NEXTVAL FOR syntax of DB2. You can use an alternative solution to implement functions similar to sequencing.

• Method 1: If GoldenDB supports the sequencing function, create a sequence and use sequence_name.NEXTVAL to obtain the sequence value.
  1. Creates a sequence.

     CREATE SEQUENCE my_seq
       START WITH 1          -- Start value of the sequence.
       INCREMENT BY 1        -- Increment of the value each time the sequence is invoked.
       MINVALUE 1            -- Minimum value of the sequence.
       MAXVALUE 999999999    -- Maximum value of the sequence.
       CACHE 20;             -- Number of values generated in advance. These values are stored in the memory to improve performance.

  2. Query the sequence value.

     To obtain the next value of a sequence, run:

     SELECT my_seq.NEXTVAL;

• Method 2: If GoldenDB does not support the sequencing function, create an AUTO_INCREMENT table to simulate a sequence and use LAST_INSERT_ID() to obtain the automatic increment.
  1. Create a table for simulating sequences. You can use AUTO_INCREMENT to simulate the auto-increment behavior of sequences.

     CREATE TABLE seq_table (
         id INT AUTO_INCREMENT,
         PRIMARY KEY (id)
     );

  2. Insert data and obtain the auto-increment ID.

     Each time a record is inserted, the ID automatically increases. You can insert a record to obtain the next sequence value.

     INSERT INTO seq_table VALUES (NULL);

     Obtain the inserted ID, that is, the next value of the sequence.

     SELECT LAST_INSERT_ID();

SQL statement conversion example:

• Original SQL statement (DB2):

  SELECT NEXTVAL FOR employee_seq FROM SYSIBM.SYSDUMMY1;

• New SQL statement after conversion (GoldenDB):
  1. Create a table for simulating sequences.

     CREATE SEQUENCE employee_seq
       START WITH 1
       INCREMENT BY 1
       MINVALUE 1
       MAXVALUE 999999999
       CACHE 20;

  2. Query the sequence value.

     SELECT employee_seq.NEXTVAL;