Executed prepared statement does not return output values under HANA SPS10 when HANA warning occurs

[balazspete]

I've encountered an issue where a prepared statement for a stored procedure does not seem to return the procedure's outputs (including the scalar output object and any other output tables). This happens when a warning is being returned from HANA under SPS10.

The exact case I'm having an issue is where HANA SPS10 returns a warning in case a DDL statement is present within a dynamic SQL operation.

The prepared statement is correctly prepared for call TEST_PROCEDURE(?, ?, ?), and it is being created for the procedure below..

create PROCEDURE TEST_PROCEDURE(
  in value       nvarchar(100),
  out reference  nclob,
  out error      table(message nvarchar(100))
)
  LANGUAGE SQLSCRIPT
  SQL SECURITY INVOKER
AS
BEGIN
  exec 'create table MYTABLE(id int)';
  reference := '';
  error = select 'error' as MESSAGE from dummy;
END;

The prepared statement is executed using the following code

input = {VALUE: 'abc'};
preparedStatement.exec(input, function(error, parameters, errorTable) {
  console.log(arguments);
});

When executing this, HANA 10 returns an error object in the first parameter of the callback, but there are no output parameters present. Executing the procedure in HANA Studio correctly returns the outputs.

{ '0':
   { [Error: Not recommended feature: DDL statement is used in Dynamic SQL (current dynamic_sql_ddl_error_level = 1)]
     message: 'Not recommended feature: DDL statement is used in Dynamic SQL (current dynamic_sql_ddl_error_level = 1)',
     code: 1347,
     sqlState: 'HY000',
     level: 0,
     position: 0 } }

When running the code against an SPS09 system, everything works fine.

If you remove the dynamic SQL from the procedure, the output values are being returned on both SPS 9 and 10.

create PROCEDURE TEST_PROCEDURE(
  in value       nvarchar(100),
  out reference  nclob,
  out error      table(message nvarchar(100))
)
  LANGUAGE SQLSCRIPT
  SQL SECURITY INVOKER
AS
BEGIN
  reference := '';
  error = select 'error' as MESSAGE from dummy;
END;

Arguments of the callback:

{ '0': null,
  '1': { REFERENCE: <Buffer > },
  '2':
   [ { Code: '1',
       Messagetype: 'error',
       Message: 'error',
       Description: 'error' } ] }

I am wondering if this is an issue with the library or if I'm missing a configuration that I can set for the client?

Thanks,
Balazs

[pradyutsarma]

Hi Holger,

Any updates on this?

I am facing the same issue when a stored procedure that I am trying to execute throws the same error/warning. The procedure has a 'CREATE USER...' DDL which throws the error.

{
"message":"Not recommended feature: DDL statement is used in Dynamic SQL 
(current dynamic_sql_ddl_error_level = 1)",
"code":1347,"sqlState":"HY000","level":0,"position":0
}

The statement within the procedure is executed successfully and the user gets created. However, the OUT parameters are not returned, probably due to the error (which seems to be more like a warning) object. Executing the procedures in HANA studio works fine.

Thanks and Regards,
Pradyut.