error feature not supported while calling afl wrapper generator inside a SQLScript procedure.

[Former Member]

hi all,

I am creating a SQLScript procedure to encapsulate a PAL procedure configuration, creation and call.

here is the procedure right now:

drop procedure OutlierDetection;

create procedure OutlierDetection(in  schemaName varchar(100),

                                                                                  in  currentDimension varchar(100))

          language SQLSCRIPT as

          insertInto varchar(100);

begin

    exec 'set schema '||:schemaName;

    exec 'drop type DATA_T';

    exec 'create type DATA_T as table("'||:currentDimension||'" VARCHAR(100), SUM_MEASURE DOUBLE)';

          exec 'DROP TYPE OUTLIER_T';

          exec 'create TYPE OUTLIER_T AS TABLE("'||:currentDimension||'" VARCHAR(100),"OUTLIERS" INT)';

          exec 'DROP TYPE RESULT_T';

    exec 'CREATE TYPE RESULT_T AS TABLE("MEAN" DOUBLE,"SD" DOUBLE)';

    exec 'DROP TYPE CONTROL_T';

           exec 'CREATE TYPE CONTROL_T AS TABLE("Name" VARCHAR(100), "intArgs" INT, "doubleArgs" DOUBLE,"strArgs" VARCHAR(100))';

           DROP table SIGNATURE_TAB;

          create column table SIGNATURE_TAB("ID" INT,"TYPENAME" VARCHAR(100),"DIRECTION" VARCHAR(100));

          insertInto := :schemaName||'.DATA_T';

          insert into SIGNATURE_TAB values (1,:insertInto,'in');

          --insert into SIGNATURE_TAB values (1,'I301605.DATA_T','in');

          insertInto := :schemaName||'.CONTROL_T';

          insert into SIGNATURE_TAB values (2,:insertInto,'in');

          insertInto := :schemaName||'.RESULT_T';

          insert into SIGNATURE_TAB values (3,:insertInto,'out');

          insertInto := :schemaName||'.OUTLIER_T';

          insert into SIGNATURE_TAB values (3,:insertInto,'out');

          exec 'GRANT SELECT ON '||:schemaName||'.SIGNATURE_TAB to SYSTEM';

          --insertInto := 'PALOutlierDetectionProcedure'; 

          --exec "call SYSTEM.afl_wrapper_eraser("||:insertInto||")";

          --call SYSTEM.afl_wrapper_eraser(:insertInto);

          call SYSTEM.afl_wrapper_eraser('PALOutlierDetectionProcedure');

          call SYSTEM.afl_wrapper_generator('PALOutlierDetectionProcedure','AFLPAL','VARIANCETEST', SIGNATURE_TAB);

end;

call OutlierDetection('I301605', 'CITY');

I getting an error when calling the WRAPPER_GENERATOR which says:

SAP DBTech JDBC: [7] (at 1706): feature not supported: Only table variable is allowed in input parameter in a nested call

could you please tell me what's wrong?

Thanks & regards

Mohamed Ali

[rindia]

Hi,

Could you try commenting this line call OutlierDetection('I301605', 'CITY');

and validate the procedure.

If it is working then it is easy to invoke the procedure using parameters with placeholder.

Regards

Raj

[Former Member]

hi, thanks for replying.

I actually did that, the procedure compilation is successful (as far as the debugger is saying)

bu when I call the procedure i am getting weird errors. You can check my message on the employee network here: https://community.wdf.sap.corp/message/422872#422872

thanks & regards

Mohamed Ali

[rindia]

Hi,

I do not have access to view your error message.  Always paste the error message here to get fast response.

Regards

Raj

[Former Member]

Thanks for replying.

new infos:

when I try to create a new PAL procedure within my stored procedure using the AFL_WRAPPER_GENERATOR

the call of the stored procedure, in this way:

call OutlierDetection('I301605', 'CITY');

take a very long time like 40 minutes or so. And I get an error, which says in the indexserver trace file :

[50637]{-1}[25/150706897] 2013-07-04 03:09:32.864566 e AFLPM_SQL        AFLPM_SQLDriverObj.cpp(05151) : Registration of AFL wrapper PALOutlierDetectionProcedure4 failed at "grant execute on _SYS_AFL.PALOutlierDetectionProcedure4 to AFL__SYS_AFL_AFLPAL_EXECUTE" with error 131: "transaction rolled back by lock wait timeout - Lock timeout occurs while waiting TABLE_LOCK of mode EXCLUSIVE(TRANSACTION_ID=49, UPDATE_TRANSACTION_ID=150706904)"!

but  when i call my procedure, with a pal procedure name that's been already used i get this error in the indexserver trace file, which says that:

[50632]{-1}[25/150707381] 2013-07-04 03:52:38.955776 e AFLPM_SQL        AFLPM_SQLDriverObj.cpp(05151) : Registration of AFL wrapper PALOutlierDetectionProcedure4 failed at "CREATE TYPE _SYS_AFL.PALOutlierDetectionProcedure4__TT_P1 AS TABLE ( "CITY" VARCHAR(100), "SUM_MEASURE" DOUBLE )" with error 396: "cannot use duplicate user-defined type name - PALOUTLIERDETECTIONPROCEDURE4__TT_P1: line 1 col 22 (at pos 21): line 1 col 22 (at pos 21)"!

the second error is weird because before calling the wrapper generator I am always calling the AFL_WRAPPER_ERASER before I call the AFL_WRAPPER_GENERATOR

by the way, I tried to call the AFL_WRAPPER_GENERATOR outside of my stored procedure and every thing works fine in this case. here is the call process

drop procedure OutlierDetection;
create procedure OutlierDetection(in  schemaName varchar(100),
   in  currentDimension varchar(100))
          language SQLSCRIPT as
          insertInto varchar(100);
begin
    exec 'set schema '||:schemaName;
    exec 'drop type DATA_T';
    exec 'create type DATA_T as table("'||:currentDimension||'" VARCHAR(100), SUM_MEASURE DOUBLE)';
  exec 'DROP TYPE OUTLIER_T';
  exec 'create TYPE OUTLIER_T AS TABLE("'||:currentDimension||'" VARCHAR(100),"OUTLIERS" INT)';
  exec 'DROP TYPE RESULT_T';
    exec 'CREATE TYPE RESULT_T AS TABLE("MEAN" DOUBLE,"SD" DOUBLE)';
    exec 'DROP TYPE CONTROL_T';
  exec 'CREATE TYPE CONTROL_T AS TABLE("Name" VARCHAR(100), "intArgs" INT, "doubleArgs" DOUBLE,"strArgs" VARCHAR(100))';
  DROP table SIGNATURE_TAB;
  create column table SIGNATURE_TAB("ID" INT,"TYPENAME" VARCHAR(100),"DIRECTION" VARCHAR(100));
  insertInto := :schemaName||'.DATA_T';
  insert into SIGNATURE_TAB values (1,:insertInto,'in');
  insertInto := :schemaName||'.CONTROL_T';
  insert into SIGNATURE_TAB values (2,:insertInto,'in');
insertInto := :schemaName||'.RESULT_T';
insert into SIGNATURE_TAB values (3,:insertInto,'out');
insertInto := :schemaName||'.OUTLIER_T';
insert into SIGNATURE_TAB values (4,:insertInto,'out');
exec 'GRANT SELECT ON '||:schemaName||'.SIGNATURE_TAB to SYSTEM';
call SYSTEM.afl_wrapper_eraser('PALOutlierDetectionProcedure');
end; 
call OutlierDetection('I301605', 'CITY');
call SYSTEM.afl_wrapper_generator('PALOutlierDetectionProcedure','AFLPAL','VARIANCETEST', SIGNATURE_TAB);
DROP TABLE #CONTROL_TAB;
CREATE LOCAL TEMPORARY COLUMN TABLE #CONTROL_TAB ("Name" VARCHAR(100), "intArgs" INT, "doubleArgs" DOUBLE,"strArgs" VARCHAR(100));
INSERT INTO #CONTROL_TAB VALUES ('SIGMA_NUM',null,3.0,null);
INSERT INTO #CONTROL_TAB VALUES ('THREAD_NUMBER',8,null,null);
DROP VIEW DATA_VIEW;
create VIEW DATA_VIEW AS select CITY, sum(AMOUNT_SOLD) AS SUM_MEASURE from _SYS_BIC."efashion/EFASHION_STAR_SCHEMA" where MONTH_NAME='April' and YR = 2003 GROUP BY CITY;
DROP TABLE RESULT_TAB;
CREATE COLUMN TABLE RESULT_TAB ("MEAN" DOUBLE,"SD" DOUBLE);
DROP TABLE OUTLIERS_TAB;
create COLUMN TABLE OUTLIERS_TAB ("CITY" VARCHAR(100),"OUTLIER" INT);
CALL _SYS_AFL.PALOutlierDetectionProcedure(DATA_VIEW, "#CONTROL_TAB", RESULT_TAB, OUTLIERS_TAB) with overview; 
drop view VT_outliers_results;
create view VT_outliers_results as select a.CITY, SUM_MEASURE, b.OUTLIER from DATA_VIEW a full join OUTLIERS_TAB b on (a.CITY = b.CITY);
select * from VT_outliers_results;

hope this makes the problem clearer.

thanks & regards

Mohamed Ali.

[rindia]

Hi,

You missed the giving authorization part.

With the user SYSTEM, grant the privilege as

GRANT EXECUTE ON system.afl_wrapper_generator to your_user_name;

This is one time process and do not include in proc.

Now call the proc and see what error comes?

Regards

Raj

[Former Member]

thanks for replying.

Actually I am using a shared instance, In SAP, what you ask me to do has already been done

and it requires SYSTEM user privileges something I don't have.

What you need to know is that I've already created the PAL script which is running correctly. (which means that the thing you're asking is already done)

but now I trying to encapsulate this script within a stored procedure, and here the problems start.

And they start exactly when I call the AFL_WRAPPER_GENERATOR from within the procedure.

If I call it from outside the procedure, everything works fine.

thanks & regards

Mohamed Ali