How to return value XS OData using XSJSLIB and Stored Procedure

[goeseels_lawrence]

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

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"

}