Debug XSoData Create using Stored Procedure

[Former Member]

Hi,

I've created an xsodata file in which I have one entity that is defined like below.

The stored procedure is used to update one or more fields from a record in a table, based on one key field.

If you would see something that is not done, please correct me.

As you can see I'm using an importing record and not individual variables, which are being read in the script.

When testing the POST service using postman i'm getting a 201 (created) return but this seems to be a lie when I check the DB afterwards.

What I would like to learn as a result of this post is how to debug the stored procedure using the debug configuration below.

And how to debug the procedure by testing the post operation using POSTMAN (or a similar tool).

____

I checked all the settings based on other SCN posts, debugger active in system, rights have been added, breakpoints are active in procedure,...

Please don't reply with the standard answers as I did my research.

____

Note: Another concern I have is how we can handle deep entity inserts using XS OData, is it possible or do you have to 'force' the UI developer to use batch requests ? It used to be possible in ABAP to write your own logic for deep inserts.

Thanks very much in Advance!!

[Former Member · Accepted solution]

I was hoping you could help me with this.

[thomas_jung · Accepted solution]

If you want to debug from from XSODATA into SQLScript within the Studio you can. You have to create a new debug configuration of type XS JavaScript: XS OData-based.  In the Main configuration, use the Debug Also Stored Procedures option:

You wouldn't start the POST from postman but instead use the Input Parameters section of the Debug configuration - which allows for setting headers and complex body:

Otherwise I suggest debugging from the Web-based Development Workbench.  It also allows you to debug into XSODATA exits by default (as of SPS 09).

[Former Member · Accepted solution]

Hi Thomas,

Thank you for your reply but I also tried this approach, which did not trigger the debug session either.

That is why I went for the debug trigger using the procedure debugging.

[Former Member · Accepted solution]

Thomas,

So I went and tried this approach again but it is still not triggering any debug session in Eclipse.

It is triggering a google chrome session where I need to grant soms session debug access for the user and a second chrome session where a service is being called but without the POST body I think.

[thomas_jung · Accepted solution]

Try Web Browser Internal instead of External. It should all run within Studio then.

[Former Member · Accepted solution]

Thanks for you quick reply!

I also gave this a shot but nothing is being triggered and I only have the indication in the right lower corner of Eclipse. But I can wait for days, it won't do anything but keep filling and clearing the green bar.

Bye the way, I'm using Eclipse Mars 4.5.2 with all MARS extra software installed and have no other issues when using Eclipse.

[Former Member · Accepted solution]

I had one last option (as far as I know) and that is using the web based workbench from the HCP.

Here I can enter debugging when I envoke the procedure (envoke using UI doesn't work either).

CREATE TABLE hciupdate

(

"TIMESHEETID" INTEGER,

"EMPLOYMENTID" NVARCHAR(8),

"ENTEREDBYPERNR" NVARCHAR(8),

"BULKXLSUPL" NVARCHAR(1),

"PROJECT" NVARCHAR(40),

"WORKPCKG" NVARCHAR(50),

"WORKITEM" NVARCHAR(10),

"WORKDATE" DATE,

"WORKHOURS" DECIMAL(4,2),

"WORKDESCR" NVARCHAR(100),

"NONPROJECTCODE" NVARCHAR(40),

"STATUS" INTEGER,

"WFSTATUS" INTEGER,

"WFREJECTREASON" NVARCHAR(100),

"ENTRYDATE" DATE,

"ENTRYTIME" TIME,

"SUBMITTIME" TIME,

"SUBMITDATE" DATE, 

"DELETEDATE" DATE,

"DELETETIME" TIME,

"DELETEDESCR" NVARCHAR(100),

"WFSTATUSCHANGEDATE" DATE,

"WFSTATUSCHANGETIME" TIME,

"S4HCOUNTER" NVARCHAR(10),

"S4HCATS_DELETE_COUNTER" NVARCHAR(12),

"REQUIRESPO" NVARCHAR(1),

"REFERENCEPO" NVARCHAR(16),

"SERVICETICKET" NVARCHAR(30),

"ACTIVITYTYPE" NVARCHAR(5),

"MILEAGE" INTEGER,

"ONCALL" NVARCHAR(1),

"SFABSENCEGUID" VARBINARY(16)

);

INSERT INTO hciupdate (TIMESHEETID, S4HCATS_DELETE_COUNTER, S4HCOUNTER, STATUS) VALUES (51,000000001893,0000001892,2); 

CALL "__"."__.global.procedures.interfacing::TimesheetToHCIUpdate"(ROW => 'hciupdate' ,ERROR => ?);

However I don't think this is 100% correct since i'm doubting that [ROW => 'hciupdate' ] is the correct way to pass my test data in table hciupdate.

Any thoughts ?

[Former Member · Accepted solution]

It's strange answering my own questions but it's working now and I was able to correct the error in my script.

Very stupid one actually .

I has forgotten to read the other imported values into the local variables, the rest of the code worked like a charm.

SELECT "TIMESHEETID", "S4HCOUNTER", "S4HCATS_DELETE_COUNTER", "STATUS"

  INTO TIMESHEETID, S4HCOUNTER, S4HCATS_DELETE_COUNTER, STATUS

  FROM :row;

  IF (:TIMESHEETID = ' ') THEN

       error = SELECT 400 AS http_status_code,

      'empty field' AS error_message,

       'TIMESHEETID must be filled in' AS detail

       FROM dummy;

  ELSE

       -- rest of coding, you can check original question for details if you want.

  END IF;

Triggering the debugger in the web tools also works with the script below (you need to add DROP test table otherwise a second row will be added when you execute the sql the second time which will result in an error in the procedure when reading the value from :row without index).

-- Create test data table

CREATE TABLE hciupdate

(

Add your fields here...

);

-- Add test data

INSERT INTO hciupdate (TIMESHEETID, S4HCATS_DELETE_COUNTER, S4HCOUNTER, STATUS)

VALUES (51,000000001893,0000001892,2);

-- Call procedure and pass table with test data

CALL "__"."__.global.procedures.interfacing::TimesheetToHCIUpdate"(ROW => 'hciupdate' ,ERROR => ?);

-- Delete test data table

DROP TABLE hciupdate;

So this has been fun .

OPEN Question:

Still don't know why stored procedure debugging did not work in any possible way using Eclipse Mars 4.5.2 while I clearly have enough authorzations etc ?

[SergioG_TX]

since you have a table type.... you should post an array with objects matching the structure of it... in your postman body.. i only see 1 record... wrap  it with [ ] to make it an array... looking for more possible issues...

also make sure your sp works properly first by testing it from the sql console.. you can break this into pieces and then test the whole thing from postman..   to test from the sql console.. create a local temporary table with the same structure as your table type.. then call the stored proc.. once it works.. then you can call it from postman..

also, you want to make sure your data types match...

[Former Member]

Sergio Guerrero,

Thank you for your reply.

I know that you might expect an array as input but I have a working example that works in exactly the same way.

In the importing row of the procedure I'm referencing a table but just for the structure.

If you want to use an actual table as input for a SP (over XS OData) you need to create a table type like I did for the error return.

But I tried this in a different case and it seems this feature is not supported in XS OData context. In that instance I used batch requests to process multiple records using the SP.

I did change the data types in de SP by now, good remark.

Your suggestion about testing the SP in the SQL console also makes good sence but I'll want to know how to debug a stored procedure that is being called up by the use of an XS OData service call.

How should I call the stored procedure that is defined like in the screenshot of the original question, using SQL command ?

     CALL "___"."___.interfacing.procedures::zManageUserIdMapping"(51, ?);          (This gives an error but It's what I would like to know too.)

Web -> xs odata call via url with post operation and body -> create using sp -> ?? how to debug the sp ??

[Former Member]

Hi,

So after some testing I figuered it out how to test the script from SQL..

CREATE TABLE hciupdate(
	same fields as the import table in the script...

);

INSERT INTO hciupdate (TIMESHEETID, S4HCATS_DELETE_COUNTER, S4HCOUNTER, STATUS) VALUES (51,000000001893,0000001892,2);

CALL "DELAWARE"."delaware.global.procedures.interfacing::TimesheetToHCIUpdate" (hciupdate, ?) IN DEBUG MODE;

DROP TABLE hciupdate;

This gives me the same result as I would get when testing the procedure via POSTMAN.

However when I set a break point in the script, it doesn't get triggered...

Anybody any idea why this is happening ?

Thanks!

[SergioG_TX]

Have you tried this?

[Former Member]

Hi,

Yes I saw that post as well.

I even created a new procedure without any parameters, just to test things out.

It's not working

[SergioG_TX]

what hana version are you using? have you considered not using xsodata and using xsjs instead for your stored proc call ?

here is an example:

http://scn.sap.com/community/developer-center/hana/blog/2015/11/18/callling-stored-proc-with-multipl...

[Former Member]

It's is a service that will be used in an HCI system so we can't change it to SSJS unfortunately.

It is a HCP system (a real one, not the trial) with the newest -1 release (will upgrade the system tonight) so this won't be the problem i think.

But I think the procedure gets called in a correct way when I test it via POSTMAN because when I call the procedure directly using a SQL script it gives me the same result.

The problem is that there is a bug in the prodecure script, for which I want (need) to be able to debug the stored procedure.

This is where I'm having issues and what I would like to solve.

so bottom line: How come I can't seem to trigger the debug session when I use a stored procedure debug configuration for the very easy procedure below (I created a new one for testing).

PROCEDURE "__"."__.global.procedures::Debug"

  ( out error "__"."__.global.data::ZTT_ERROR" )

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  AS

BEGIN

  error = select 400 as http_status_code, 'empty field' as error_message, 'Test Error'

         as detail from dummy;

END;

When calling the procedure via the script below I'm getting the return message as expected.

So the procedure works.

CALL "__"."__.global.procedures::Debug"(?) IN DEBUG MODE;

When I set a break point in the procedure and create a debug configuration for this stored procedure script, the debug session is not being triggered.

[SergioG_TX]

and you have the debugging role right???

[Former Member]

Right.

The role and I gave all grants using SQL commands as wel.

I used to have a small x over my breakpoints which was due to insufficient authorization but I fixed this already.