PAL - SAP DBTech JDBC: [423]: liveCache error: [423] SYSTEM.AFL_WRAPPER_GENERATOR:

[former_member195727]

Hi,

when trying to exectute the following script (sample from the help document):

SET

SCHEMA AGS_SYSTEM;

DROP

TYPE PAL_AD_RESULT_T;

CREATE

TYPE PAL_AD_RESULT_T AS TABLE(

"ID"

INT,

"V000"

DOUBLE,

"V001"

DOUBLE

)

;

DROP

TYPE PAL_AD_DATA_T;

CREATE

TYPE PAL_AD_DATA_T AS TABLE(

"ID"

INT,

"V000"

DOUBLE,

"V001"

DOUBLE,

primary

key("ID")

)

;

DROP

TYPE PAL_CONTROL_T;

CREATE

TYPE PAL_CONTROL_T AS TABLE(

"NAME"

VARCHAR (50),

"INTARGS"

INTEGER,

"DOUBLEARGS"

DOUBLE,

"STRINGARGS"

VARCHAR (100)

)

;

-- create procedure

DROP

TABLE PDATA;

CREATE

COLUMN TABLE PDATA(

"ID"

INT,

"TYPENAME"

VARCHAR(100),

"DIRECTION"

VARCHAR(100) );

INSERT

INTO PDATA VALUES (1, 'DM_PAL.PAL_AD_DATA_T', 'in');

INSERT

INTO PDATA VALUES (2, 'DM_PAL.PAL_CONTROL_T', 'in');

INSERT

INTO PDATA VALUES (3, 'DM_PAL.PAL_AD_RESULT_T', 'out');

GRANT

SELECT ON AGS_SYSTEM.PDATA to SYSTEM;

call

SYSTEM.afl_wrapper_generator('PAL_ANOMALY_DETECTION', 'AFLPAL', 'ANOMALYDETECTION', PDATA);

DROP

TABLE PAL_AD_DATA_TAB;

CREATE

COLUMN TABLE PAL_AD_DATA_TAB (

"ID"

INT,

"V000"

DOUBLE,

"V001"

DOUBLE,

primary

key("ID")

)

;

INSERT

INTO PAL_AD_DATA_TAB VALUES (0 , 0.5, 0.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (1 , 1.5, 0.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (2 , 1.5, 1.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (3 , 0.5, 1.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (4 , 1.1, 1.2);

INSERT

INTO PAL_AD_DATA_TAB VALUES (5 , 0.5, 15.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (6 , 1.5, 15.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (7 , 1.5, 16.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (8 , 0.5, 16.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (9 , 1.2, 16.1);

INSERT

INTO PAL_AD_DATA_TAB VALUES (10, 15.5, 15.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (11, 16.5, 15.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (12, 16.5, 16.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (13, 15.5, 16.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (14, 15.6, 16.2);

INSERT

INTO PAL_AD_DATA_TAB VALUES (15, 15.5, 0.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (16, 16.5, 0.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (17, 16.5, 1.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (18, 15.5, 1.5);

INSERT

INTO PAL_AD_DATA_TAB VALUES (19, 15.7, 1.6);

INSERT

INTO PAL_AD_DATA_TAB VALUES (20,-1.0, -1.0);

DROP

TABLE PAL_CONTROL_TAB;

CREATE

COLUMN TABLE PAL_CONTROL_TAB (

"NAME"

VARCHAR (50),

"INTARGS"

INTEGER,

"DOUBLEARGS"

DOUBLE,

"STRINGARGS"

VARCHAR (100)

)

;

INSERT

INTO PAL_CONTROL_TAB VALUES ('THREAD_NUMBER',2,null,null);

INSERT

INTO PAL_CONTROL_TAB VALUES ('GROUP_NUMBER',4,null,null);

INSERT

INTO PAL_CONTROL_TAB VALUES ('INIT_TYPE',4,null,null);

INSERT

INTO PAL_CONTROL_TAB VALUES ('DISTANCE_LEVEL',2,null,null);

INSERT

INTO PAL_CONTROL_TAB VALUES ('MAX_ITERATION',100,null,null);

DROP

TABLE PAL_AD_RESULT_TAB;

CREATE

COLUMN TABLE PAL_AD_RESULT_TAB (

"ID"

INT,

"V000"

DOUBLE,

"V001"

DOUBLE

)

;

CALL

_SYS_AFL.PAL_ANOMALY_DETECTION(PAL_AD_DATA_TAB, PAL_CONTROL_TAB, PAL_AD_RESULT_TAB) with overview;

select

* from PAL_AD_RESULT_TAB;

I am getting an error:

Could not execute 'call SYSTEM.afl_wrapper_generator('PAL_ANOMALY_DETECTION', 'AFLPAL', 'ANOMALYDETECTION', PDATA)' in 86 ms 466 µs . SAP DBTech JDBC: [423]: liveCache error:  [423] SYSTEM.AFL_WRAPPER_GENERATOR: line 34 col 1 (at pos 1472): liveCache error exception: liveCache error: registration finished with errors, see indexserver trace

Anybody any idea?

Thanks, Arno

[former_member184871 · Accepted solution]

Hi  Arno

This problem has already been discussed

I request you to please refer to the following links:

PAL:  Troubleshooting | SAP HANA

PAL -- Getting Started | SAP HANA

PAL -- Key concepts | SAP HANA

PAL:  Using the new AFL_wrapper_eraser | SAP HANA

SAP HANA Academy | SAP HANA

Hope it helps

Regards

Mayuresh

[former_member195727 · Accepted solution]

Thanks a lot. The info helped to resolve the problem: The schema of the parameter tables needed to be corrected. Now it works

[Former Member · Accepted solution]

Arno:

What corrections did you make to the schema of the parameter table to resolve?

I tried all the solutions mentioned in this community but still have the problem.

thanks

[rindia · Accepted solution]

Dear Chak Kolli,

Have you tried something like this:

Please use schema name and drop all the tables you created.

SET SCHEMA your_schema_name;

DROP TYPE PAL_DT_IRIS_DATA;

CREATE TYPE PAL_DT_IRIS_DATA AS TABLE (

     SEPAL_LENGTH DOUBLE,

     SEPAL_WIDTH DOUBLE,

     PETAL_LENGTH DOUBLE,

     PETAL_WIDTH DOUBLE,

    CLASS VARCHAR(20));

.

.

.

INSERT INTO PAL_DT_IRIS_SIGNATURE VALUES (

1, 'your_schema_name.PAL_DT_IRIS_DATA', 'in');

GRANT SELECT ON your_schema_name. PAL_DT_IRIS_SIGNATURE to SYSTEM;

CALL SYSTEM.AFL_WRAPPER_GENERATOR ('PAL_DT_IRIS', 'AFLPAL', 'CREATEDT', PAL_DT_IRIS_SIGNATURE);

If still you are getting error then please send the error message screenshot for speedy response but create a new discussion thread.

Regards

Raj

[Former Member · Accepted solution]

Raj:

Thanks. I re did everything and it worked. It took a while to get thru this error.

[former_member211055 · Accepted solution]

Hello,

Right now I am using the HANA server which I got from Amazon web services.  I don't have the ability to get this sql file (AFL_WRAPPER_GENERATOR.sql) from the linux server.  If any one can post this sql, it will be great.

Can I get the copy of the code for AFL_WRAPPER_GENERATOR?

Thanks

Sreedhar

[former_member184871 · Accepted solution]

Hi Sreedhar Badhe

Here goes the AFL_WRAPPER_GENERATOR code.

DROP TYPE AFL_PARAMETER_TABLE_TYPE_T;
CREATE TYPE AFL_PARAMETER_TABLE_TYPE_T AS TABLE("ID" INT, "TYPENAME" VARCHAR(1000), "DIRECTION" VARCHAR(100));
DROP PROCEDURE afl_wrapper_generator;
CREATE PROCEDURE afl_wrapper_generator(IN proc VARCHAR(100), IN area VARCHAR(100), IN afl VARCHAR(100), IN data AFL_PARAMETER_TABLE_TYPE_T) LANGUAGE SQLSCRIPT 
AS 
v_schema_name VARCHAR(1000) := '';
v_table_name VARCHAR(1000) := '';
v_string VARCHAR(32767) := '';
v_dim VARCHAR(100) :='';
CURSOR c_parameter_table FOR SELECT ID,TYPENAME,DIRECTION FROM :data ORDER BY ID;
CURSOR c_parameter_table_detail FOR 
          SELECT COLUMN_NAME,POSITION,DATA_TYPE_NAME,CS_DATA_TYPE_NAME,LENGTH 
                    FROM "PUBLIC"."TABLE_COLUMNS" 
                    WHERE SCHEMA_NAME = (CASE WHEN :v_schema_name != '' THEN :v_schema_name ELSE SCHEMA_NAME END) 
                    AND TABLE_NAME = :v_table_name ORDER BY POSITION;
BEGIN
FOR cur_row AS c_parameter_table DO
          v_schema_name := SUBSTR_BEFORE(cur_row.TYPENAME, '.');
          IF :v_schema_name != '' THEN
                    v_table_name := SUBSTR_AFTER(cur_row.TYPENAME, '.');
          ELSE
                    v_table_name := cur_row.TYPENAME;
          END IF;
          v_string := :v_string || ';';
          v_string := :v_string || :v_table_name || ',' || cur_row.DIRECTION ||','|| 'TrexTable' ||',,,,';
          FOR cur_row_detail AS c_parameter_table_detail DO
                    v_string := :v_string || ';';
                    IF cur_row_detail.DATA_TYPE_NAME = 'VARCHAR' OR cur_row_detail.DATA_TYPE_NAME = 'NVARCHAR' THEN
                              v_dim := cur_row_detail.LENGTH;
                    ELSE
                              v_dim := '';
                    END IF;
                    v_string := :v_string || cur_row_detail.COLUMN_NAME || ',,,' || cur_row_detail.CS_DATA_TYPE_NAME ||',,'||cur_row_detail.DATA_TYPE_NAME||','||:v_dim;
          END FOR;
END FOR;
v_string := :v_string || ';';
CALL "SYSTEM".aflpm_createprocedure(:area,:afl,:proc,:v_string);
END;

Happy Learning

Regards

Kumar

[former_member211055 · Accepted solution]

Thank you very much.  Appreciate your help.

[rindia]

Dear Arno,

Grant statement to the user is missing. Could you please try

grant execute on system.afl_wrapper_generator to relevant_user_name;

Regards

Raj