Can not insert data from temp table (XSJS and PAL)

[Former Member]

Hi!

I'm having an issue with a code I've been working on!

I have a SQL code which runs perfectly in the HANA SQL Console. I need that code to be dynamic so I put it on an XSJS service. (Still haven't worked on the dynamic part because of this error i keep getting).

The SQL Code has a PAL algorithm call, that gives me an error when I execute it on XSJS. The error is this:

dberror(CallableStatement.execute): 2048 - column store error: search table error:  [2620] executor: plan operation failed;Can not insert data from temp table "SYSTEM:P3_55497E0FFE9D18A7E10000007F000002 (-1)" into table ""TRIALFIRE_CLIENTDATA"."KM_RESULTS"" at ptime/session/eapi/jdbc/ExternalStatement.cc:927

The code that's giving the error is the following:

(The error is on line 66)

var response = {};
var conn = $.db.getConnection();
var eString = "";
function onesql(sql) { 
    try { 
        var ps = conn.prepareStatement(sql); 
        ps.execute(); 
        ps.close(); 
    }catch(e){ 
        eString += sql + ":\n" + e.toString() + "\n--------\n\n"; 
    } 
}


try {

    var fieldsParam = $.request.parameters.get('fields'),
    typesParam = $.request.parameters.get('types'),
    fields = fieldsParam.split("|"),
    types = typesParam.split("|");
    onesql( 'SET SCHEMA TRIALFIRE_CLIENTDATA');
    onesql( 'DROP TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_DATA');
    onesql( 'DROP TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_PARAMS');
    onesql( 'DROP TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_RESULTS');
    onesql( 'DROP TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_CENTERS');
    onesql( 'DROP TABLE TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE');
    var pc = conn.prepareCall("CALL SYSTEM.AFL_WRAPPER_ERASER ('PAL_KM')");
    pc.execute();
    onesql( 'DROP VIEW TRIALFIRE_CLIENTDATA.V_KM_DATA');
    onesql( 'DROP TABLE TRIALFIRE_CLIENTDATA.KM_RESULTS');
    onesql( 'DROP TABLE TRIALFIRE_CLIENTDATA.KM_CENTERS');
    onesql( 'CREATE TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_DATA AS TABLE '
     + ' ( STORE_SK INTEGER, COST DOUBLE, SALES DOUBLE, TRAFFIC_BAND DOUBLE, '
     + ' CUSTOMER_COUNT DOUBLE, GENDER_F DOUBLE, GENDER_M DOUBLE, AGE DOUBLE)');
    onesql( 'CREATE TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_PARAMS AS TABLE (NAME VARCHAR(60), INTARGS INTEGER, DOUBLEARGS DOUBLE, STRINGARGS VARCHAR(100))');
    onesql( 'CREATE TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_RESULTS AS TABLE (ID INTEGER, CENTER_ID INTEGER, DISTANCE DOUBLE)');
    onesql( 'CREATE TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_CENTERS AS TABLE'
    + ' ( CENTER_ID INTEGER, COST DOUBLE, SALES DOUBLE, TRAFFIC_BAND DOUBLE,'
    + ' CUSTOMER_COUNT DOUBLE, GENDER_F DOUBLE, GENDER_M DOUBLE, AGE DOUBLE)');
    onesql('CREATE COLUMN TABLE TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE (ID INTEGER, TYPENAME VARCHAR(100), DIRECTION VARCHAR(100))');
    onesql("INSERT INTO TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE VALUES (1, 'TRIALFIRE_CLIENTDATA.PAL_T_KM_DATA', 'in')");
    onesql("INSERT INTO TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE VALUES (2, 'TRIALFIRE_CLIENTDATA.PAL_T_KM_PARAMS', 'in')");
    onesql("INSERT INTO TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE VALUES (3, 'TRIALFIRE_CLIENTDATA.PAL_T_KM_RESULTS', 'out')");
    onesql("INSERT INTO TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE VALUES (4, 'TRIALFIRE_CLIENTDATA.PAL_T_KM_CENTERS', 'out')"); 
    conn.commit();
    var pc = conn.prepareCall("CALL SYSTEM.AFL_WRAPPER_GENERATOR ('PAL_KM', 'AFLPAL', 'KMEANS', TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE)");
    pc.execute();
    onesql('CREATE VIEW TRIALFIRE_CLIENTDATA.V_KM_DATA AS' 
  + ' SELECT STORE_SK, SUM(COST) AS COST, SUM(SALES) AS SALES, MAX(TRAFFIC_BAND) AS TRAFFIC_BAND,'
  + ' MAX(CUSTOMER_COUNT) AS CUSTOMER_COUNT, MAX(GENDER_F) AS GENDER_F, MAX(GENDER_M) AS GENDER_M, MAX(AGE) AS AGE'
  + ' FROM "TRIALFIRE_CLIENTDATA"."STORE_SALES_TRAITS"'
  + ' GROUP BY STORE_SK');
    onesql('CREATE COLUMN TABLE TRIALFIRE_CLIENTDATA.KM_RESULTS LIKE TRIALFIRE_CLIENTDATA.PAL_T_KM_RESULTS');
    onesql('CREATE COLUMN TABLE TRIALFIRE_CLIENTDATA.KM_CENTERS LIKE TRIALFIRE_CLIENTDATA.PAL_T_KM_CENTERS');
    onesql('DROP TABLE KM_PARAMS');
    onesql('CREATE TABLE KM_PARAMS LIKE TRIALFIRE_CLIENTDATA.PAL_T_KM_PARAMS');
    onesql("INSERT INTO KM_PARAMS VALUES ('THREAD_NUMBER', 2, null, null)");
    onesql("INSERT INTO KM_PARAMS VALUES ('GROUP_NUMBER', 150, null, null)");
    onesql("INSERT INTO KM_PARAMS VALUES ('INIT_TYPE', 1, null, null)");
    onesql("INSERT INTO KM_PARAMS VALUES ('DISTANCE_LEVEL', 2, null, null)");
    onesql("INSERT INTO KM_PARAMS VALUES ('MAX_ITERATION', 100, null, null)");
    onesql("INSERT INTO KM_PARAMS VALUES ('NORMALIZATION', 0, null, null)");
    onesql("INSERT INTO KM_PARAMS VALUES ('EXIT_THRESHOLD', null, 0.00001, null)");
    conn.commit();
    var pc = conn.prepareCall("CALL _SYS_AFL.PAL_KM (TRIALFIRE_CLIENTDATA.V_KM_DATA, KM_PARAMS, TRIALFIRE_CLIENTDATA.KM_RESULTS, TRIALFIRE_CLIENTDATA.KM_CENTERS) WITH OVERVIEW");
    pc.execute();

    response.values = fields;
    response.types = types;
    conn.commit();
    conn.close();


    $.response.contentType = "application/json";
    $.response.headers.set("Access-Control-Allow-Origin", "*");
    $.response.setBody(JSON.stringify(response));
    response.status = $.net.http.OK;


} catch (e) {
    eString = "\nException.toString(): " + e.toString() + "\n";
    var prop = "";
    for (prop in e) {
        if (e.hasOwnProperty(prop)) {
            eString += prop + ": " + e[prop] + "\n";
        }
    }


    response.status = $.net.http.INTERNAL_SERVER_ERROR;
    response.contentType = "plain/text";
    $.response.setBody(eString);
}

And this is the SQL code that is working perfectly on the console, which is basically the same as the above code:

set schema TRIALFIRE_CLIENTDATA;
DROP TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_DATA;
DROP TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_PARAMS;
DROP TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_RESULTS;
DROP TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_CENTERS;
DROP TABLE TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE;
CALL SYSTEM.AFL_WRAPPER_ERASER ('PAL_KM');
DROP VIEW TRIALFIRE_CLIENTDATA.V_KM_DATA;
DROP TABLE TRIALFIRE_CLIENTDATA.KM_RESULTS;
DROP TABLE TRIALFIRE_CLIENTDATA.KM_CENTERS;
--DROP VIEW V_KM_RESULTS;


-- PAL setup
CREATE TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_DATA AS TABLE
( STORE_SK INTEGER, COST DOUBLE, SALES DOUBLE, TRAFFIC_BAND DOUBLE,
CUSTOMER_COUNT DOUBLE, GENDER_F DOUBLE, GENDER_M DOUBLE, AGE DOUBLE
);
CREATE TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_PARAMS AS TABLE (NAME VARCHAR(60), INTARGS INTEGER, DOUBLEARGS DOUBLE, STRINGARGS VARCHAR(100));
CREATE TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_RESULTS AS TABLE (ID INTEGER, CENTER_ID INTEGER, DISTANCE DOUBLE);
CREATE TYPE TRIALFIRE_CLIENTDATA.PAL_T_KM_CENTERS AS TABLE
( CENTER_ID INTEGER, COST DOUBLE, SALES DOUBLE, TRAFFIC_BAND DOUBLE,
CUSTOMER_COUNT DOUBLE, GENDER_F DOUBLE, GENDER_M DOUBLE, AGE DOUBLE
);


CREATE COLUMN TABLE TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE (ID INTEGER, TYPENAME VARCHAR(100), DIRECTION VARCHAR(100));
INSERT INTO TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE VALUES (1, 'TRIALFIRE_CLIENTDATA.PAL_T_KM_DATA', 'in');
INSERT INTO TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE VALUES (2, 'TRIALFIRE_CLIENTDATA.PAL_T_KM_PARAMS', 'in');
INSERT INTO TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE VALUES (3, 'TRIALFIRE_CLIENTDATA.PAL_T_KM_RESULTS', 'out');
INSERT INTO TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE VALUES (4, 'TRIALFIRE_CLIENTDATA.PAL_T_KM_CENTERS', 'out');




--GRANT SELECT ON TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE TO SYSTEM;
CALL SYSTEM.AFL_WRAPPER_GENERATOR ('PAL_KM', 'AFLPAL', 'KMEANS', TRIALFIRE_CLIENTDATA.PAL_KM_SIGNATURE);


-- app setup
CREATE VIEW TRIALFIRE_CLIENTDATA.V_KM_DATA AS 
SELECT STORE_SK, SUM(COST) AS COST, SUM(SALES) AS SALES, MAX(TRAFFIC_BAND) AS TRAFFIC_BAND,
MAX(CUSTOMER_COUNT) AS CUSTOMER_COUNT, MAX(GENDER_F) AS GENDER_F, MAX(GENDER_M) AS GENDER_M, MAX(AGE) AS AGE
FROM "TRIALFIRE_CLIENTDATA"."STORE_SALES_TRAITS"
GROUP BY STORE_SK;
CREATE COLUMN TABLE TRIALFIRE_CLIENTDATA.KM_RESULTS LIKE TRIALFIRE_CLIENTDATA.PAL_T_KM_RESULTS;
CREATE COLUMN TABLE TRIALFIRE_CLIENTDATA.KM_CENTERS LIKE TRIALFIRE_CLIENTDATA.PAL_T_KM_CENTERS;


-- app runtime
DROP TABLE KM_PARAMS;
CREATE COLUMN TABLE KM_PARAMS LIKE TRIALFIRE_CLIENTDATA.PAL_T_KM_PARAMS;
INSERT INTO KM_PARAMS VALUES ('THREAD_NUMBER', 2, null, null);
INSERT INTO KM_PARAMS VALUES ('GROUP_NUMBER', 150, null, null);
INSERT INTO KM_PARAMS VALUES ('INIT_TYPE', 1, null, null);
INSERT INTO KM_PARAMS VALUES ('DISTANCE_LEVEL', 2, null, null);
INSERT INTO KM_PARAMS VALUES ('MAX_ITERATION', 100, null, null);
INSERT INTO KM_PARAMS VALUES ('NORMALIZATION', 0, null, null);
INSERT INTO KM_PARAMS VALUES ('EXIT_THRESHOLD', null, 0.00001, null);


TRUNCATE TABLE TRIALFIRE_CLIENTDATA.KM_RESULTS;
TRUNCATE TABLE TRIALFIRE_CLIENTDATA.KM_CENTERS;


--GRANT CREATE ANY ON SCHEMA TRIALFIRE_CLIENTDATA TO _SYS_AFL;


CALL _SYS_AFL.PAL_KM (TRIALFIRE_CLIENTDATA.V_KM_DATA, KM_PARAMS, TRIALFIRE_CLIENTDATA.KM_RESULTS, TRIALFIRE_CLIENTDATA.KM_CENTERS) WITH OVERVIEW;


SELECT * FROM TRIALFIRE_CLIENTDATA.KM_RESULTS ORDER BY CENTER_ID;
SELECT * FROM TRIALFIRE_CLIENTDATA.KM_CENTERS;

[Former Member · Accepted solution]

Hi!

I managed to solve this with help from SAP Tech Support, thanks to the Startup Focus Program.

So the trick was to change the line of code that was giving the error from this:

var pc = conn.prepareCall("CALL _SYS_AFL.PAL_KM (TRIALFIRE_CLIENTDATA.V_KM_DATA, KM_PARAMS, TRIALFIRE_CLIENTDATA.KM_RESULTS, TRIALFIRE_CLIENTDATA.KM_CENTERS) WITH OVERVIEW");
pc.execute();

to this

var pc = conn.prepareCall("CALL _SYS_AFL.PAL_KM (TRIALFIRE_CLIENTDATA.V_KM_DATA, KM_PARAMS, ?, ?) WITH OVERVIEW");
resultSet = pc.execute();
resultSet = pc.getResultSet();
while (resultSet.next()) { 
  tables.push(resultSet.getString(2));
}
onesql('INSERT INTO TRIALFIRE_CLIENTDATA.KM_RESULTS SELECT * FROM ' + tables[0]); 
onesql('INSERT INTO TRIALFIRE_CLIENTDATA.KM_CENTERS SELECT * FROM ' + tables[1]);
conn.commit();

So in the ?'s the names of the tables that store the results from the clustering are allocated, and after executing we can retrieve those names with getResultSet. After that we simply do an insert into the tables we want to hold the results and centers for the clustering.

Hope this helps someone else!

Greetings and Blessings.