Question Re: Error handling when using SQL DDL for Smart Data Integration in Technology Q&A

Error handling when using SQL DDL for Smart Data Integration

Former Member — Wed, 04 Nov 2020 22:00:03 GMT

Hi there,

it seems to be a problem to catch errors on SDI-SQL?


CREATE or replace  PROCEDURE test  --IN NAME NVARCHAR(1000)
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER AS
BEGIN
   DECLARE s nvarchar(1000);
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	BEGIN 
		select ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE  from dummy;
	END;
	
	--select 1/0 from dummy; --This error is handled fine (if not commented out)
	s='CREATE VIRTUAL TABLE "test" at "REMOTE_SOURCE_EXCEL"."<NULL>"."<NULL>"."MyExcel.xls/worksheet1"';
	exec :s; -- Error  is NOT catched! procedure crashes
END;

CALL test;

The creation of the virtual table usually succeeds, if everything is declared properly. But what about an error condition?

If probably the remote file is not available, the error handler has no effect:

The error was NOT Catched, the procedure crashes.

Does anybody have an idea how to enable error handling here?

Thanks!

Re: Error handling when using SQL DDL for Smart Data Integration

lbreddemann — Wed, 04 Nov 2020 22:42:59 GMT

Your observation is correct - the SQL Script error handler catches SQL errors and nothing else.

Errors from remote sources are not caught by it. To create a fault-tolerant solution for your use case, I guess the easiest way is to not do it in SQL Script but in one of the programming languages that support the HANA client libraries.

Re: Error handling when using SQL DDL for Smart Data Integration

werner_daehn — Thu, 05 Nov 2020 06:32:46 GMT

Hm, but a create-virtual-table is a SQL error. So it should, shouldn't it? I would raise a ticket with SAP.