The
SAP Advanced SQL Migration tool has supported SQL dialect conversion from Microsoft SQL Server to SAP Hana since Hana support was first released, and the results are usually pretty good. However, there is a non-trivial aspects to this conversion which could be addressed only recently.
This is about the error-handling control flow which works differently in SQL Server compared to Hana. This may look like a benign, boring little detail, but it can actually be quite complex in practice when migrating SQL Server-based applications to Hana.
Here's the issue.
By default, SQL Server does not use exception handling like Hana SQLScript (and other SQL dialects like Oracle, DB2, Teradata, Netezza).
Instead, when an error occurs, SQL Server usually just proceeds to the next statement. It is up to the SQL programmer to explicitly check the value of the
@@ERROR variable after each statement, and when found to be non-zero, take appropriate action. Instead, in Hana SQLScript, control passes to the exception handler when an error (or, more precise: an exception) occurs. After executing the statements in the exception handler, execution jumps to the end of the procedure (formally, the end of the block, but let's keep it simple for now) and control returns to the caller.
Consider the following SQL statements. The issue here is that the second INSERT statement will cause a duplicate key error.
-- SQL Server syntax:
1> CREATE PROCEDURE MyProc AS
2> BEGIN
3> CREATE TABLE MyTable (a INT UNIQUE, c VARCHAR(20)
4> INSERT INTO MyTable VALUES(100, 'first row')
5> INSERT INTO MyTable VALUES(100, 'duplicate key!')
6> INSERT INTO MyTable VALUES(101, 'next row')
7> SELECT COUNT(*) FROM MyTable
8> END
In SQL Server, when the duplicate key error occurs (on line 5), an error message is printed but execution then happily continues with the next INSERT statement (on line 6) and the final SELECT.
In Hana however, when the duplicate key error occurs, control passes to the exception handler, and the next INSERT statement at line 6 would not be executed anymore.
This difference in behaviour means that it can be difficult to migrate SQL Server code to Hana since a sequence of statements that was valid in SQL Server, cannot be executed just as easily in Hana.
Until recently, Hana provided no practical workaround for this problem.
Sure, it would have been possible to wrap each and every Hana SQLScript statement in its own BEGIN-END block, but that would create very ugly SQLScript code, and probably also lead to code maintenance issues in the future.
Fortunately, Hana 2 SP04 offers new options. SP04 supports a new type of exception handler, namely a CONTINUE handler. Prior to SP04, Hana provided only EXIT handlers. The difference is that an EXIT handler will leave the block after handling the exception, and not execute any subsequent statements in that block, thus leading to the control flow problem described above. Instead, the new CONTINUE handler in SP04 will resume execution after the statement where the exception occurred, which is essentially what happens in SQL Server.
CONTINUE handlers were added in SP04 primarily to support migrations from DB2 and Teradata, whose SQL dialects support CONTINUE handlers as well as EXIT handlers. But CONTINUE handlers also allow us to achieve the same control flow in Hana as in SQL Server.
However, CONTINUE handlers are only half of the story.
As mentioned, in SQL Server you are supposed to explicitly check
@@ERROR after each statement. If it contains a nonzero value, this means an error has occurred and you can take action.
-- SQL server syntax:
INSERT INTO MyTable VALUES(100, 'duplicate key!')
IF @@ERROR <> 0
BEGIN
PRINT 'Oops! INSERT error!'
[...take some action...]
END
INSERT INTO MyTable VALUES(101, 'next row')
[...etc...]
Now, checking for the occurrence of an error in Hana is a much more tricky than it first appears. If you care to try, you will find these issues:
- The Hana error code is in ::SQL_ERROR_CODE, but this variable can be accessed only inside the exception handler. Therefore, using a statement like IF ::SQL_ERROR_CODE <> 0 directly after the INSERT will raise a syntax error.
- More problematic are the semantics of @@ERROR in SQL Server: once you have referenced @@ERROR, its value is immediately reset to 0. A subsequent DML statement will also reset the value of @@ERROR.
Since Hana does not behave the same as SQL Server on this point, we had to find a trick to make this work. That trick uses session context variables, as shown below.
Please take a minute to stare at this Hana code:
-- Hana syntax:
1> CREATE OR REPLACE PROCEDURE MyProc()
2> AS
3> BEGIN
4> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
5> -- CONTINUE handler added for control flow like SQL Server
6> BEGIN
7> -- save current error code in session context variable:
8> SET SESSION 'mssql_at_at_error' = ::SQL_ERROR_CODE;
9> END;
10>
11> [... preceding statements...]
12>
13> -- wipe out any status from previous statement:
14> CALL sapdbmtk.sp_dbmtk_clear_at_at_error();
15> INSERT INTO MyTable VALUES(100, 'duplicate key!');
16>
17> -- retrieve ::SQL_ERROR_CODE that was saved in the CONTINUE handler:
18> IF sp_f_dbmtk_get_at_at_error() <> 0 THEN /* ORIG: IF @@ERROR <> 0 */
19> CALL SQLSCRIPT_PRINT:PRINT_LINE('Oops! INSERT error!');
20> [...take some action...]
21> END IF;
22>
23> INSERT INTO MyTable VALUES(101, 'next row');
24> [...etc...]
25> END;
1> CREATE FUNCTION sp_f_dbmtk_get_at_at_error()
2> RETURNS v INT
3> AS
4> BEGIN
5> SELECT SESSION_CONTEXT('mssql_at_at_error') INTO v FROM DUMMY;
6> SET SESSION 'mssql_at_at_error' = 0;
7> RETURN :v;
8> END;
What's happening here:
- When an error occurs during the INSERT (line 15), control passes to the CONTINUE handler (line 4)
- Inside the exception handler, the error code is copied into a session context variable (line 😎
- When reaching the end of the exception handler, control then passes back and execution continues after the INSERT, on line 17
- On line 17, the function sp_f_dbmtk_get_at_at_error() is called (this function is provided by the SAP Advanced SQL Migration tool). This function returns the value of the session context variable that was just set in the exception handler, so this sequence of steps has the effect of testing, in Hana, whether ::SQL_ERROR_CODE is <> 0
- What is also happening inside sp_f_dbmtk_get_at_at_error(), is that the session context variable is immediately reset to 0, since this is how @@ERROR behaves in SQL Server: any reference to @@ERROR rests it to zero.
- Lastly, on line 14, we explicitly clear the value of the session context variable which holds the error code. This is to ensure that we do not carry over an error code that was set earlier, but which, for some reason, was not tested for in the SQL Server code, and thus, did not get cleared in the converted Hana code.
All of this may look nice and clever, but of course the first question to ask is whether you want your SQL Server code converted to Hana in this way, or whether you prefer to use the default Hana EXIT handler mechanism.
There is no 'best' option here, as it rather depends on what is more important in your migration project. If you have, say, 2000 SQL Server stored procedures totalling 150,000 lines of SQL code, and you have an existing testing framework for those procedures, then using the CONTINUE handler mechanism above could dramatically simplify and shorten the process of testing the converted procedures. If, on the other hand, you prefer your SQL objects in Hana to be as simple as possible, you may want to avoid all of the added code shown above and just use the standard EXIT handler mechanism in Hana, at the expense of potentially having to make some changes to ensure the same outcome in Hana as in SQL Server.
Therefore, this conversion is user-configurable in the SAP Advanced SQL Migration tool. By default, the CONTINUE handler approach is used and all the additional code shown above will automatically be generated by the migration tool.
Obviously, Hana 2 SP04 must have been selected as the target DBMS to utilize the CONTINUE handler approach.
It is worth pointing out that Microsoft SQL Server also supports an exception handling mechanism through the
BEGIN TRY...END TRY BEGIN CATCH...END CATCH syntax. With that syntax, SQL Server will use exception handler control flow, and this syntax was already converted to Hana EXIT handlers for Hana 2 SP03. Only when the TRY-CATCH syntax is not used in SQL Server (which is the default), will the CONTINUE handler conversion be applied.
PS:
The observant reader might object that we did not discuss how transaction management (e.g. COMMIT/ROLLBACK) plays a role, since SQL Server and Hana provide two very different transaction models. Indeed, ensuring the COMMIT/ROLLBACK sequence is the same in Hana as it was in SQL Server is a complex challenge by itself and I'll try to discuss that in another blog post at some point. For now, suffice to say that the SAP Advanced SQL Migration tool takes care of this and ensures the transactional result in Hana is the same as in SQL Server.
