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"
}
Source: scn.sap.com
No comments:
Post a Comment