MySql - Ms Sql Server Database Portability
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"
