Application Development Blog Posts
Learn and share on deeper, cross technology development topics such as integration and connectivity, automation, cloud extensibility, developing at scale, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member182090
Active Participant


Last update:22-Dec-2017

NB: earlier, SAP Advanced SQL Migration was called ‘Exodus’. it was renamed in Q4 2017. This blog post will be updated soon.

 

In the previous episode, we discussed conversion between SQL dialects from a more philosophical angle.

I would now like to look at some more concrete examples of what the Exodus DBMS migration tool can do.

When converting from one SQL dialect to another, the necessary first step is to compensate for syntactic differences.

So let's start with a pretty simple Oracle PL/SQL statement:

     DBMS_OUTPUT.PUT_LINE('hello world!');


 

Exodus will convert this statement to the following T-SQL code in ASE:

 

     /* ORIGSQL: DBMS_OUTPUT.PUT_LINE('hello world!'); */

     PRINT 'hello world!

In case we'd be migrating to SAP IQ or to SQL Anywhere, the resulting Watcom SQL would be as follows:

 

     /* ORIGSQL: DBMS_OUTPUT.PUT_LINE('hello world!'); */

     MESSAGE 'hello world! TO CLIENT;

 

Note how the original code is added as a comment - so in case something went wrong in the conversion, it will be easier for you to figure out what the correct result should have been.

Since not everyone likes their code cluttered with these ORIGSQL comments, they can be switched off through an Exodus configuration setting. For brevity I'll be omitting these comments in the examples from now.


 

 

Let's try something more interesting. The following PL/SQL prints the number of rows in a table:

     DECLARE cnt NUMBER;

[...]

     select Count(*) into cnt from mytable;

     DBMS_OUTPUT.PUT_LINE('#rows in table: '||cnt);

 

This is converted to the following ASE T-SQL code:

 

     DECLARE @cnt INT


     DECLARE @DBMTK_TMPVAR_STRING_1 VARCHAR(16384)

[...]


     SELECT

        @cnt = COUNT(*)

     FROM

        mytable

     SET @DBMTK_TMPVAR_STRING_1 = '#rows in table: '||CONVERT(VARCHAR(100),@cnt)

     PRINT @DBMTK_TMPVAR_STRING_1

 

There are various things worth noting here:

 

  • First, note how the PL/SQL SELECT-INTO statement is converted to the corresponding ASE syntax which selects a value into a variable (ASE's own SELECT-INTO has entirely different semantics).
    In case we'd convert to
    IQ or SQL Anywhere, the SELECT-INTO would be retained as Watcom SQL supports this syntax too.

  • Second, the original expression in the PUT_LINE() statement is actually an expression where a string is concatenated with an integer. In ASE, it is not allowed to specify expressions as arguments to the PRINT statement, so Exodus takes the expression out and assigns it to a help variable which is then specified as the argument to PRINT.
    (NB: for converting stored procedure calls to ASE, the same approach is used)

  • Third, Exodus knows that the cnt variable is an integer and it converts it to a string before concatenating it. Unlike PL/SQL, ASE's T-SQL does not support automatic conversion to string datatypes in such expressions, so leaving this expression unchanged would have cuased a syntax error in ASE.
    And just in case you'd ask: if the concatenated variable had been declared as a string itself, Exodus would not generate the CONVERT() call.

  • Lastly, as the converted SELECT-INTO statement shows, Exodus tries to format the generated SQL code nicely and in a standarized manner. If the formatting of the input SQL code is messy, the result will look better.


Finally, the following could well occur in a PL/SQL application:

     DBMS_OUTPUT.PUT_LINE(INITCAP('hello world!'));

For those not familiar with PL/SQL, the INITCAP() built-in function capitalizes the first letter of every word in a string. Since none of the SAP databases provide such a built-in function, Exodus comes with a library of so-called "run-time components" which are SQL stored procedures or SQL functions that implement such functionality.

Exodus converts the above statement to ASE as follows:

     SET @DBMTK_TMPVAR_STRING_1 = dbo.sp_f_dbmtk_capitalize_word('hello world!')

     PRINT @DBMTK_TMPVAR_STRING_1

The SQL function sp_f_dbmtk_capitalize_word() gives the same result as PL/SQL's INITCAP().


When executed, the result is as expected:

     Hello World!

 

Obviously, much of this is pretty basic stuff. In fact, Exodus goes far beyond these elementary conversion requirements.


More examples in my next blog. Watch this space!

  • SAP Managed Tags: