Error while executing DB query :xsodata calling XSJSLIB function for create operation

[former_member195421]

Hi All,

I have imported an UI5 application from HANA 1.0 SP06 to HANA 2.0 SP02 to see if the application works well with HANA 2.0 SP02 . From the application , we use a XSODATA call with custom exit to insert values to a table. While testing the application in HANA 2.0 SP02 , XSODATA call throws below error

-------------------------

sap-ui-core.js:88 2019-01-28 12:17:14 The following problem occurred: HTTP request failed400,Bad Request,{ "error": { "code": "", "message": { "lang": "en-US", "value": "DB access exception"},"innererror":{"errordetail":{"DETAILS":"dberror(Connection.prepareStatement): 259 - invalid table name: Could not find table/view #NEW_vsegmentType140012385857792 in schema XXX: line 1 col 70 (at pos 69)--createSegmentUsing@/XXX/xsjslib/segmentc/SegmentCreate.xsjslib:32\

--------------------------

Below is the XSJSLIB function

function createSegmentUsing(param)

{ try { var sql, pstmt,userName = $.session.getUsername();

// do some operations

pstmt = param.connection.prepareStatement('update "' + param.afterTableName + '" SET ' +'EXT_SGMT_ID="XXX"."XXXodata.txt.db::SEQ_SGMT_EXT".NEXTVAL, CREATED_ON=CURRENT_UTCTIMESTAMP,CREATED_BY=?,CHANGED_ON=CURRENT_UTCTIMESTAMP,CHANGED_BY=?,DELETE_FLAG=?');

pstmt.setString(1, userName);

pstmt.setString(2, userName);

pstmt.setString(3, 'N');

pstmt.executeUpdate();

pstmt.close();

sql = 'INSERT INTO "SMA"."sma.db::SGMT.TBL_USR_SEGMENT" SELECT * FROM "SMA"."'+param.afterTableName+'"';

pstmt = param.connection.prepareStatement(sql);

pstmt.executeUpdate();

pstmt.close(); }

catch (e) { return { HTTP_STATUS_CODE : e.code, ERROR_MESSAGE : 'DB access exception', DETAILS : e.message +"--" +e.stack }; } }

-----------------------------------------------------------

From the log , it says cant find the temporary table created as part of PARAM.AFTERTABLENAME .

Is this error has something to do with HANA 2.0 SP02 ? is there anything changed in terms of XSODATA exit handling ?

[lsubatin]

Hi, I'm having a hard time following the code with the random replacements. Are the quotes in the original code not escaped?

[thomas_jung]

Is this an override, before, or after exit? And it is a Create operation? The different exit types do use different interfaces. For instance the afterTableName is used in CREATE and UPDATE and the beforeTableName is used in UPDATE and DELETE.

https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/2.0.03/en-US/03735142bfd647c2a016fcd935...

The thing is, fundamentally the way the exits work hasn't changed when moving to HANA 2.0 or even when moving to XSA.

[alexander_rau]

Hi Thomas,

message: 'feature not supported: update statement for volatile table: line 1 col 8 (at pos 7)'

This is the result of using a custom exit which updates the PK with a sequence (actually I consider using a custom exit for PK generation to be a total hack but @sap/xsodata cannot handle IDENTITY column at all and creates broken statements).

xsodata:

"SALES" as "Sales" create events ( before "test:jsutils.xsjslib::createSalesEntry" );

jsutils.xsjslib:

...<br>let after = param.afterTableName;
let pStmt = param.connection.prepareStatement('update "' + after + '" set ID = SALES_ID.NEXTVAL');
pStmt.executeUpdate();
pStmt.close();<br>...

The exact same custom exit worked on Neo inside the Hana 1 XS engine.

The feature not supported message is coming from the HDB engine I think and this used to work in Neo. What's your opinion on this ?

Best Regards, Alex

Update: Figured it out. On CF I used a column table, on Neo a row table. Insert/Update is not allowed for volatile column tables. Switching to a row table made it work 🤔🙄