CODEWITHSUNDEEP
sqlodbchana
H Ferguson
H Ferguson

Reputation: 23

Trying to get HANA Create function to work in ODBC (warning in hdbsql becomes error in ODBC)

I have the following function that we have working in SQL Server, in PostGreSQL, and a version of this working in Oracle (not with a SELECT INTO but using a ref cursor). Our function in HANA will compile with warnings on the hdbsql command line, but when executed within an ODBC call, it throws an exception. The database I'm using for testing is (unfortunately) a Version 1 db.

CREATE OR REPLACE FUNCTION GETWFFIELD
(
    IN TABLENAME VARCHAR,
    IN FIELDNAME VARCHAR,
    IN LANGCODE VARCHAR,
    IN WORKID DECIMAL,
    IN SUBWORKID DECIMAL,
    IN TASKID DECIMAL,
    IN DEFVAL VARCHAR
) RETURNS SQLResults NVARCHAR(2000)

AS
BEGIN
        
  IF TASKID IS NULL THEN 
    SELECT TOP 1 Value
      INTO SQLResults DEFAULT DEFVAL
    FROM WML
    WHERE TableName=TABLENAME
      AND ColName=FIELDNAME
      AND Key1=WORKID 
      AND Key2=SUBWORKID
      AND LangCode=LANGCODE;    
  ELSE 
    SELECT TOP 1 Value
      INTO SQLResults DEFAULT DEFVAL
    FROM WML
    WHERE TableName=TABLENAME
      AND ColName=FIELDNAME
      AND Key1=WORKID 
      AND Key2=SUBWORKID
      AND Key3=TASKID
      AND LangCode=LANGCODE;
  END IF; 
    
END;

I would have used a cursor but unlike Oracle, I couldn't find an example in HANA as to how to call one SQL or another plus that seems to be Dynamic SQL which HANA forbids in functions (but the above is allowed). My issue with the above is that when I execute it in our GUI, it executes in ODBC not in a SQL console, and the warning becomes an error message:

E675217410:ODBC reported error.:[SAP AG][LIBODBCHDB SO][HDBODBC] General error; > 1347 Not recommended feature: Using SELECT INTO in Scalar UDF

Really there are two elements to this that I need:

  1. the ability to have a slightly different query in HANA when the value TASKID is null or not null (i.e. if it's null, it's not part of the WHERE clause), and
  2. that the query return the passed in default if no results are found.

Is there a way to get this to run in an ODBC call? Thanks in advance

Upvotes: 0

Views: 509

Answers (1)

astentx
astentx

Reputation: 6751

Do not use dynamic SQL until there's no really dynamic identifiers or dynamic code. You write this query one time, but then such dynamic SQL will waste resources with every execution, so it is worth to invest more development time to have a single or copied static SQL.

What about answers:

  1. Your statement can be reduced to single statement with ... and ( (:taskid is not null and Key3=:taskid) or :taskid is null) and no dynamic SQL. If you really need to have a dynamic SQL to return something, then you are limited to only scalar output for HANA 2.0 and no parameter binding at all (neither in, nor out) for HANA 1.0 (do not know if this works fine in 2.0).
  2. This can be achieved with aggregation function on your value since aggregation function always return a value (null if no rows were passed where clause), If you want to have a single value with TOP (maybe you need a first row in some order), then you can wrap it into a subquery and then do a dummy aggregation on one row.
SELECT coalesce(max(Value), 'Your default value')
  INTO SQLResults
FROM WML
WHERE ...

Or you may use exception handlers, but they are too bulky and with some not visually obvious error codes.

do begin
  declare lv_str varchar(100);
  begin
    DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 1299 /*no data found*/
      lv_str := 'Default value';
    
    select 10
      into lv_str
    from dummy
    where 1 = 0;
  end;
  
  select :lv_str as res from dummy;
end;


|   | RES           |
+---+---------------+
| 1 | Default value |

What about the code: it is better to use colon in front of the variable not to mix it with table columns: if you'll have a typo, your query will evaluate, for example, LangCode=LANGCODE as always true for non-nulls and make the debug process hard.

Upvotes: 0

Related Questions