|
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:
|
