MySql - Ms Sql Server Database Portability

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"

Integrating YUI Compressor to your Maven Web Project

Size of the javascript files and css files in a website has a big affect on its performance. Especially in slow connections lot of time will be spent on downloading those files. We recently came across such situation. Our website was using jQuery and some other javascipt libraries. Some of them were more than 100kb. Sometimes it took more than 10 seconds to load a page. When we view the loading statistics using developer-tools in Chrome it showed that browser had downloaded more than 500kb of scripts and css.

One way to reduce this script size is to compress them. Our script files contained lot of comments, licencing statements, nice formattings, long meaningful variable names etc... which increases the file size. YUI Compressor is a free utility by Yahoo which can be use to compress our scripts by removing above mentioned things. It is jar file and you can use that and compress file by file.

But that is not very practicle to keep those minimized scripts in develepment since after minimizing you won't be able read them and edit them when needed. Best method is to do the minization when you create the war file. Luckly there is a maven plugin for this. Following is the plugin component you can add to your maven pom.

<plugins>

....

<plugin>

<groupId>org.apache.maven.plugins</groupId>

<artifactId>maven-war-plugin</artifactId>

<configuration>

<webResources>

<resource>

<directory>

${project.build.directory}/minimized

</directory>

<targetPath>/</targetPath>

<filtering>false</filtering>

</resource>

</webResources>

</configuration>

</plugin>

<plugin>

<groupId>net.sf.alchim</groupId>

<artifactId>yuicompressor-maven-plugin</artifactId>

<version>0.7.1</version>

<executions>

<execution>

<goals>

<goal>compress</goal>

</goals>

</execution>

</executions>

<configuration>

<webappDirectory>

${project.build.directory}/minimized

</webappDirectory>

<nosuffix>true</nosuffix>

</configuration>

</plugin>

....

</plugins>

When you run mnv clean package YUI compressor will compress the scripts and put them in target/minimized folder and those will be used to create the war file.

8 Stages of Programming a New Feature

This is a really nice comic I came across few days back. It is really nice and explains every thing what we generally going through as software engineers.....

This one was originally published at http://www.my25percent.com/2009/09/eight-stages-of-programming-new-feature.html 

Happy New Year 2010

Wish You All a Very Happy and Prosperous 

New Year!

May All Your Dreams Come True in This Wonderful Year...

Traditional SDP Vs mChoice Soltura

SDPs are commonly developed as core systems which interconnect different Telco network services and provide one unified access point for customers (application developers). This will reduce the application development cost rapidly and easy management of applications for the Telco. Even though SDP reduces the application development cost, still the most difficult part remains; which is the development of the business logic of the application. With SDP model if any content provider wants to run a mobile application, then they have to consider about two things;

1. Developing/running and maintaining the application
2. Managing the content provided by the application

These two are completely different things. For a genuine content provider 'Developing/running and maintaining' an application is a completely new thing and it will deviate his concentration from providing valuable content. In addition to that, content provider also has to bear the cost of infrastructure required to run the application. Initial cost of developing the application and buying infrastructure can be really high. This startup cost is one of the main reasons blocking many of the new comers entering to the market. 


This is where the mChoice Soltura TM come in to the scene. mChoice Soltura is not just a SDP, rather it provides infrastructure for content providers to create their own application with virtually zero cost. They don't have to worry about creating applications, maintaining them and infrastructure requirements to run those applications. They can put 100% of their attention to the creation of the content. mChoice Soltura will provide different types of applications, so the content provider can choose the application matching to their requirement and use it. mChoice Soltura provides a simple and powerful Web Based Interface for content provider to create/manage application and manage content. 


mChoice Soltura releases the burden of application creation, maintenance and infrastructure management and brings the freedom to the content providers.