Sandarenu's Blog

Few weeks back I was given a task to migrate a Java application using MySQL as its database to MS SQL Server. It was actually not a full migration, rather application should be able work with both MySQL and MS SQL Server. Since we had used Hibernate in our application I thought it will be a easy thing. But when I dig deep in to the application and started migration I was proved to be wrong. I encounter lot of issues and hence thought of documenting them so any one can get some help. Major headache was the difference in some direct SQL commands used in the application. Following are the list of issues I encounter and how I solved them.


1. JDBC Driver Issue with the Microsoft JDBC Driver
    First thought of using JDBC driver provided by Microsoft for MS SQL Server 2005.  But when I ran the test cases I got few issues with it.
    1). Gave execption "org.hibernate.MappingException: No Dialect mapping for JDBC type: -9 "
        After some investigation found out that it was due to NVARCHAR datatype. There is another opensource driver called jtds. Which is more actively developed as well. With that driver I did not get that MappingException.

2. Data type differences
    For queries like "SELECT count(id) From myTable", MySql JDBC driver returns datatype java.math.BigInteger and with MsSQL jtds JDBC driver we get datatype java.lang.Integer. So I had to convert it to string first and then convert to long
    String countStr = summaryRow[1] == null? "0": String.valueOf(summaryRow[1]);
    long count = Long.parseLong(countStr);

3. Nullable Unique columns not supported in MsSql Server. In Sql Server NULL is taken as a value and hence can't have multiple rows with null, but in MySql we can have Nullable unique columns. So when table schema is generated through Hibernate we have to execute additional update query to perform a workaround for this issue. Here we add new column called 'my-table_id_add' to the table.

declare unique_key_list_my-table cursor for
    select OBJECT_NAME(OBJECT_ID)
    FROM sys.objects
    WHERE type_desc LIKE '%CONSTRAINT' and OBJECT_NAME(OBJECT_ID) LIKE 'UQ__my-table%'

OPEN unique_key_list_my-table
    FETCH NEXT FROM unique_key_list_my-table INTO @keyName
    set @RowNum = 0
    WHILE @@FETCH_STATUS = 0
    BEGIN
      set @RowNum = @RowNum + 1
      print cast(@RowNum as char(1)) + ' ' + @keyName
      if not @keyName is null
      begin
           select @sql = 'ALTER TABLE [my-table] DROP CONSTRAINT [' + @keyName + ']'
           execute sp_executesql @sql
      end
        FETCH NEXT FROM unique_key_list_my-table INTO @keyName
    END
CLOSE unique_key_list_my-table
DEALLOCATE unique_key_list_my-table

ALTER TABLE my-table ADD my-table_id_add AS (CASE WHEN my-table_id IS NULL THEN CAST(id AS VARCHAR(30)) ELSE my-table_id END);
ALTER TABLE my-table ADD CONSTRAINT UQ__my-table_my-table_id UNIQUE(my-table_id_add);


4. TIMESTAMP in MS Sql server is not functionally same as MySQL. Have to use GETDATE() function for that.
    e.g: Create table timestamp_test DATETIME default(GETDATE())

5. In Ms Sql Server we can't use 'user' as table/field name, but that is possible in MySql.

6. LIMIT is not supported in Ms Sql Server and there is no easy way if we want go select some range of data. In MySql we use "SELECT * FROM my-table LIMIT 10, 20" to select records from 10 to 20, but in Ms Sql Server we have to use something like "SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY id) as row FROM my-table ) a WHERE row > 10 and row <= 20"

• Labels:
• MsSqlServer
• MySQL
• Programming
• SQL


• Leave A Comment