Review of Adventnet SwisSQL SQLOne Console, SQL Query Converter
Last updated: February 5th '05 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

Featured Book Discusses the SQL2003 SQL language implementation, by the 5 major database platforms, with examples: Microsoft SQL Server, Oracle, DB2, MYSQL, PostgreSQL

Review of SwisSQL SQLOne Console, SQL Query Converter Recently, SwisSQL approached me for a review of their product that can convert SQL statements from one dialect to another. They told me that "SwisSQL SQLOne Console" is a GUI application that can convert SQL statements from one RDBMS implementation to another and the dialects supported include: Microsoft SQL Server Sybase Oracle IBM DB2 Informix MySQL PostgreSQL MySQL and last but not the least, ANSI SQL This sounded too good to be true, so I decided to review the product. I went ahead and downloaded the fully functional version (with the limitation of 50 SQL query conversions in the 30 day evaluation period) of SwisSQL - SQLOne Console (Visual Basic) 1.9.4 from their website. Before downloading the product, you are required to fill-in a form with some basic details like your name, email, phone, company etc. The product itself is downloadable as a .Zip file. Installation can be started by extracting the contents of the .Zip file and running the setup.exe file. Installation is quite simple, but you do get some dialog boxes about replacing some of your existing files that are more recent than the ones in the installation package. Always choose to keep your existing and recent files. One of the requirements of this application is that, you should have .Net framework 1.1 installed on your system. My installation went fairly smooth. But when I started the application from the Start menu, I got an error message about the environmental variable 'path' not containing the path to the .Net frame work. I fixed it by appending the .Net framework path to the path environmental variable, and the application started fine. It turned out to be a very simple, single screen, standard application. You can see this for yourself in the below image:

The application window consists of four main areas:

• The top left hand side frame consists of a free text area where you can type in your SQL queries that you want to convert to other dialects of SQL.
  
  
• The top right hand side tabbed dialog box lets you switch from one dialect of SQL to another. That is, if you type in a T-SQL query into the top left text box, and click convert, the equivalent MySQL, ANSI SQL, Sybase, PL/SQL, DB2, Informix and PostgreSQL queries can be seen in this tabbed dialog box. Good thing is, by clicking the 'Convert' button only once, your SQL query is converted into all supported dialects in one go.
  
  
• The bottom left hand side list box can be used to cache your frequently converted SQL statements. This is a handy feature, as you can store your frequently used SQL commands in this list box, and move them in to the top left text box for conversion, on demand, by pressing the 'up' and 'down' buttons.



• The bottom right hand side grid shows you the results of query. This application can connect to the supported data sources, and you can run your converted queries against the data source, and check your results conveniently.

Before we actually start using the product, let's see where this product can be useful. This automated conversion tool can save huge amounts of man hours, when migrating a database application from one RDBMS platform to another. For example, think about migrating an Oracle database application to Microsoft SQL Server. It is going to be a humongous task migrating the SQL queries, as the PL/SQL and T-SQL implementations are vastly different. Another scenario where I think such a utility can add great value is, if you are an Independent Software Vendor (ISV), that supports different database backends. In this case, an automated SQL conversion utility can cut down the development time vastly.

I started off my review with a simple and very common test, that retrieves the current time from the SQL Server, and the SQL Query is: SELECT GETDATE(). When I clicked on the "Convert" button, the tool came up with the following queries for each of the supported database dialects. You'll be amazed to see how a simple query like this one can be so different, in different database management systems. Here are the results:

MySQL: SELECT (CURRENT_TIMESTAMP)

ANSI SQL: SELECT (CURRENT_TIMESTAMP)

Sybase: SELECT (CURRENT_TIMESTAMP)

Oracle: SELECT (SYSDATE) FROM SYS.DUAL

DB2: SELECT (CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1 FETCH FIRST 1 ROW ONLY

Informix: SELECT FIRST 1 (CURRENT) FROM SYSTABLES

PostgreSQL: SELECT (CURRENT_TIMESTAMP)

Next I decided to find out how this tool translates the Oracle's rownum functionality (that let's Oracle users access individual rows from a resultset using a row number) to other databases. I converted the following query:

SELECT *
FROM
(
SELECT *
FROM Employees
ORDER BY EmployeeID DESC
)
WHERE ROWNUM < 2

I got the following results for various database platforms:

SQL Server:

SELECT TOP 1 *
FROM
(
SELECT *
FROM Employees
ORDER BY EmployeeID DESC
) AdventNet_ALIAS1

DB2:

SELECT *
FROM
(
SELECT *
FROM Employees
ORDER BY EmployeeID DESC
) AdventNet_ALIAS1
FETCH FIRST 1 ROWS ONLY

Informix:

SELECT FIRST 1 *
FROM
(
SELECT *
FROM Employees
ORDER BY EmployeeID DESC
) AdventNet_ALIAS1

PostgreSQL:

SELECT *
FROM (SELECT *
FROM Employees
ORDER BY EmployeeID DESC
) AdventNet_ALIAS1
LIMIT 1

Next, I decided to test how the SQL Server's proprietary implementation of IDENTITY values can be translated to other database platforms. I converted the following SQL Server CREATE TABLE statement:

CREATE TABLE Orders
(
OrderID int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
CustomerID int NOT NULL,
OrderDate datetime NOT NULL,
OrderAmount money NOT NULL
)

..and got the following results:

Oracle:

CREATE SEQUENCE Orders_OrderID_SEQ
START WITH 1
INCREMENT BY 1

CREATE TABLE Orders
(
OrderID int PRIMARY KEY ,
CustomerID int NOT NULL ,
OrderDate DATE NOT NULL ,
OrderAmount DECIMAL (19, 4) NOT NULL
)

DB2:

CREATE TABLE Orders
(
OrderID int GENERATED BY DEFAULT AS IDENTITY(START WITH 1 INCREMENT BY 1) PRIMARY KEY NOT NULL,
CustomerID int NOT NULL ,
OrderDate TIMESTAMP NOT NULL ,
OrderAmount DECIMAL (19, 4) NOT NULL
)

MySQL:

CREATE TABLE Orders
(
OrderID int AUTO_INCREMENT PRIMARY KEY NOT NULL,
CustomerID int NOT NULL ,
OrderDate datetime NOT NULL ,
OrderAmount DECIMAL (19, 4) NOT NULL
)

Then I checked how Oracle's DUAL table functionality can be implemented in various other RDBMSes, and here are the results, when I converted the following Oracle's PL/SQL: SELECT 'Operation Succeeded' FROM Dual

SQL Server:

SELECT 'Operation Succeeded'

DB2:

SELECT 'Operation Succeeded'
FROM SYSIBM.SYSDUMMY1
FETCH FIRST 1 ROWS ONLY

Informix:

SELECT FIRST 1 'Operation Succeeded'
FROM SYSTABLES

So far so good. When I tried to convert Oracle's proprietary START WITH...CONNECT BY (for processing hierarchical data) syntax to SQL Server's T-SQL, the tool couldn't do it. It is understandable, as there's no equivalent syntax in T-SQL, but the same can be achieved by using a multi-step procedural code and SQLOne console didn't go that length. But it did convert the query successfully into Informix and DB2.

Next I tried to convert another commonly used Oracle function DECODE() into other platforms. I converted the following query:

SELECT DBMS_Code,
decode
(
DBMS_Code, 'Ora', 'Oracle',
 'Syb', 'Sybase',
 'mssql', 'Microsoft SQL Server'
)
FROM DBMS_Table

...and I got the correct result for SQL Server, as shown below:

SELECT
DBMS_Code,
CASE DBMS_Code
 WHEN 'Ora' THEN 'Oracle'
 WHEN 'Syb' THEN 'Sybase'
 WHEN 'mssql' THEN 'Microsoft SQL Server'
END
FROM DBMS_Table

Here's how the application looks like in action: