Modifying Incompatible SQL Statements

Open the source code in VS Code and modify the SQL statements to solve the preceding three incompatibility problems.

Incompatibility Issue 1: DM8 does not support the replace into method. Use the merge into method instead.

How to modify: Find the ruoyi-system/src/main/resources/mapper/system/SysUserOnlineMapper.xml file and change the content of the <insert></insert> tag in the SQL segment whose ID is saveOnline to the merge into content.

       
            <insert id="saveOnline" parameterType="SysUserOnline">
         merge into sys_user_online
                   using (select #{sessionId} sessionId, #{loginName} login_name, #{deptName} dept_name, #{ipaddr} ipaddr, #{loginLocation} login_location, #{browser} browser, #{os} os,
                                                 #{status} status, #{startTimestamp} start_timestamp, #{lastAccessTime} last_access_time, #{expireTime} expire_time from dual) d
                   on sys_user_online.sessionId = d.sessionId
                   when matched then
                            update set sys_user_online.login_name = d.login_name, sys_user_online.dept_name = d.dept_name, sys_user_online.ipaddr = d.ipaddr,
                                     sys_user_online.login_location = d.login_location, sys_user_online.browser = d.browser, sys_user_online.os = d.os, sys_user_online.status = d.status,
                                     sys_user_online.start_timestamp = d.start_timestamp, sys_user_online.last_access_time = d.last_access_time, sys_user_online.expire_time = d.expire_time
                   when not matched then
                            insert (sessionId, login_name, dept_name, ipaddr, login_location, browser, os, status, start_timestamp, last_access_time, expire_time)
                                     values(d.sessionId, d.login_name, d.dept_name, d.ipaddr, d.login_location, d.browser, d.os, d.status, d.start_timestamp, d.last_access_time, d.expire_time)
</insert>

Example:

Incompatibility Issue 2: The cast function in DM8 cannot convert the BLOB type to the String type.
How to modify: Find the ruoyi-system/src/main/java/com/ruoyi/system/mapper/SysNoticeMapper.java file and replace the cast(notice_content as char) method in the SQL statements of the selectNoticeById and selectNoticeList functions with the following method:
```
UTL_I18N.RAW_TO_CHAR(SUBSTRBLB(notice_content,1,3000),'UTF8')
```
Example:

Incompatibility issue 3: DM8 does not have database tables provided by MySQL, such as information_schema.

How to modify: Find the ruoyi-generator/src/main/resources/mapper/generator/GenTableMapper.xml file and modify the SQL statement whose ID is selectDbTableList, as shown in the following figure:

       
            <select id="selectDbTableList" parameterType="GenTable" resultMap="GenTableResult">
         select so.NAME table_name, st.COMMENT$ table_comment, so.CRTDATE create_time from SYS.SYSOBJECTS so left join SYS.SYSTABLECOMMENTS st on so.NAME = st.TVNAME and st.TABLE_TYPE = 'TABLE' and st.SCHNAME = (select CURR_SCH from V$SESSIONS LIMIT 1)
         where so.TYPE$='SCHOBJ' and so.SUBTYPE$='UTAB' and so.PID = -1 and INSTR(so.NAME,'##') = 0 and so.SCHID = (select ID from SYS.SYSOBJECTS where TYPE$='SCH' and NAME= (select CURR_SCH from V$SESSIONS LIMIT 1)) and so.NAME NOT LIKE 'QRTZ_%' AND so.NAME NOT LIKE 'gen_%'
         and so.NAME NOT IN (select table_name from gen_table)
         <if test="tableName != null and tableName != ''">
                   and lower(so.NAME) like lower(concat('%', #{tableName}, '%'))
         </if>
         <if test="tableComment != null and tableComment != ''">
                   and lower(st.COMMENT$) like lower(concat('%', #{tableComment}, '%'))
         </if>
         order by so.CRTDATE desc
</select>

Example:

Parent topic: Application Migration