Skip to Content
Author's profile photo Lawrence Goeseels

How to return value XS OData using XSJSLIB and Stored Procedure

Information:


This document contains code of a small test. This logic allow us to return the Id with XS OData. A stored procedure is used to insert records into a table.

A structure is used specifically as input. This highlights, that I only insert directly from the stored procedure into the table. In this way, data can be validated before inserting into the table.

A XSJSLIB file is used to generate the new Id. In this post you will see an example, how to combine a XSJSLIB with a Stored Procedure and some validation inside the stored procedure.


Objects used:


Table:

ZTEST.hdbtable

/wp-content/uploads/2016/07/table_1003361.png


Sequence:

ZTESTID.hdbsequence



schema= "TESTSCHEMA";
increment_by = 1;
start_with = 1;
nomaxvalue=true;
cycles= false;





Structure:

ZTESTSTRUCT.hdbstructure



table.schemaName = "TESTSCHEMA";
table.tableType = COLUMNSTORE;
table.columns =
[
  {name = "TESTID"; sqlType = INTEGER; nullable = false; comment = "test id"; },
    {name = "TESTNAME"; sqlType = NVARCHAR; length = 30; comment = "test name"; },
    {name = "TESTDESCRIPTION"; sqlType = NVARCHAR; length = 256; comment = "test description"; }
];
table.primaryKey.pkcolumns = ["TESTID"];



Procedure

ZTESTPROC.hdbprocedure

Info:

This simple procedure inserts data in the table ZTEST.hdbtable. There is an if statement that checks if “TESTNAME” is filled in. If not, the stored procedure will throw an error.


PROCEDURE "TESTSCHEMA"."lawrence.demo.procedure::ZTESTPROC" (
  IN ROW "TESTSCHEMA"."lawrence.demo.data::ZTEST",
  out error "TESTSCHEMA"."lawrence.global.data::ZTT_ERROR" )
  LANGUAGE SQLSCRIPT
  SQL SECURITY INVOKER
  DEFAULT SCHEMA TESTSCHEMA
  --READS SQL DATA
  AS
BEGIN
/*****************************
  Write your procedure logic
 *****************************/
 DECLARE invalid_input CONDITION FOR SQL_ERROR_CODE 19000;
 declare ERROR_MSG string;
  declare TESTID integer;
 declare TESTNAME string;
 declare TESTDESCRIPTION string;
 SELECT "TESTID", "TESTNAME", "TESTDESCRIPTION"
 INTO TESTID, TESTNAME, TESTDESCRIPTION
 FROM :ROW;
 IF (:TESTNAME IS NULL OR :TESTNAME = '')
 THEN
  error = select 400 as http_status_code, ERROR_MSG as error_message,
  'Testname empty' as detail from dummy;
  SIGNAL invalid_input SET MESSAGE_TEXT = 'Testname empty';
 END IF;
 INSERT INTO "TESTSCHEMA"."lawrence.demo.data::ZTEST"
 VALUES(:TESTID,
  :TESTNAME,
  :TESTDESCRIPTION);
END;



XSODATA

TestEntry.xsodata

Info:

First the function create_Test in the createTest.xsjslib will be executed. After this function is successfully executed, the stored procedure will be executed.


service {
  "lawrence.demo.data.structures::ZTESTSTRUCT" as "TestService"
  create using "lawrence.demo.procedure::ZTESTPROC"
  events( before "lawrence.demo.js:createTest.xsjslib::create_test");
}



XSJSLIB

createTest.xsjslib

Info:

Before the stored procedure is executed. The function “create_test” in createTest.xsjslib will generate a new Id for the field “TESTID” using the created sequence. The function will insert the new unique Id in the field “TESTID” of structure “ZTESTSTRUCT”. After this, the stored procedure is called with the now filled in Id.


function create_test(param) {
  $.trace.debug("Entered create test function...");
  let after = param.afterTableName;
  let pStmt = param.connection.prepareStatement('update "' + after + '" set TESTID = "TESTSCHEMA"."lawrence.demo.data.sequence::ZTESTID".NEXTVAL');
  pStmt.executeUpdate();
  pStmt.close();
}



OData POST


{
    "TESTID" : "0",
    "TESTNAME" : "Something",
    "TESTDESCRIPTION" : "Something else"
}



Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      nice one 😉

      Author's profile photo Karthik Ravi
      Karthik Ravi

      Hi Lawrence,

      We tried the same scenario, but it does not work for us. it shows 403 forbidden error while post using the postman.

      Author's profile photo Ekansh Saxena
      Ekansh Saxena

      Hello,

      Thanks for sharing the idea.

      However, I tried without creating a duplicate structure and it worked.  So I guess no need of creating duplicate structure, you can directly use your main table.

       

      Regards,

      Ekansh

      Author's profile photo Santiago Garcia
      Santiago Garcia

      When I try to update temporary table an error appears

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

      Seems like I cannot update a temporary table? I am using HANA Express edition

      Thanks

       
      Author's profile photo Gautami Edara
      Gautami Edara

      Hi Lawrence,

       

      Thanks for the blog. I have tried the above scenario and it works smoothly. I was trying a small modification to this scenario where I am using SYSUUID instead of the sequence generation in my create_test function. This value is being returned in the service but it's not being inserted into the DB table. Can you help me with this scenario?

       

      Regards,

      Gautami

      Author's profile photo Bruna Lima Leão
      Bruna Lima Leão

      Hi, Lawrence,

      When I try to perform the POST, I get a “Service exception: [2048] column store error” message error. Can you help me with this error?

      Best regards,

      Bruna.

      Author's profile photo Jimmy Arnold Moreno Nuñez
      Jimmy Arnold Moreno Nuñez

      Hi Lawrence.

      Nice blog. When I execute CREATE sentence, my function xsjslib call to the sequence but my entity id isn't update. What's wrong?