Error while executing a DB query: xsodata calling hdbprocedure from sapui5 for create operation

[shivam_bedwal]

Hello,

I have an xsodata service -

service { "tinyworld.tinydb::world.country" as "country" create using "tinyworld.tinydb::createCountry"; }

hdbcds --------------------------------------------------------------

namespace tinyworld.tinydb;

context world {

entity country {

key im_name : String(100);

im_continent : String(100);

};

};

hdbprocedure ---------------------------------------------------------

PROCEDURE "tinyworld.tinydb::createCountry"(

IN im_name nvarchar(100), IN im_continent nvarchar(100)

)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER AS

--DEFAULT SCHEMA <default_schema_name>

--READS SQL DATA AS

BEGIN /************************************* Write your procedure logic *************************************/

insert into "tinyworld.tinydb::world.country" values (im_name, im_continent);

END

I am calling the xsodata from ui5 application -

var oModel = this.getModel("euroModel");

//we create an object to be passed as a JSON object

var oEntry = {};

//we add to the JSON object the values of the columns of the row we want to insert

oEntry.im_name = "Sri Lanka";

oEntry.im_continent = "Asia";

//we perform the insert request using the create() method of the model

oModel.setHeaders({ "content-type": "application/json;charset=utf-8" });

var mParams = {};

mParams.success = function() { sap.m.MessageToast.show("Create Successfully"); };

mParams.error = this.onErrorCall;

oModel.create('/country', oEntry, mParams);

oModel.read("/country");

When I run the ui5 application, it gives me error -

"Error while executing a DB query"

The read operation from UI works fine and I can see the data.

I get the error when 'create' operation is triggered from the UI.

Please provide your inputs as what am I doing wrong.

Thanks,

Shivam

[pfefferf · Accepted solution]

You expect that the procedure used as write exit for the OData service, gets the entity properties as single parameters, but that is wrong. The procedure will get a table typed to the entity type. In addition an output parameter error is necessary. Details can be found in section "XS Odata Custom Exit as an SQLScript Stored Procedure" in the official documentation.

What you have to to basically:

• Create an error type as described in the documentation.
• Change the interface of the procedure:

PROCEDURE "tinyworld.tinydb::createCountry"(
  IN new "tinyworld.tinydb::world.country",<br>  OUT error <your error type>
)
...

• Change the body of your procedure to handle the "new" table typed parameter for the insert + to catch errors (if necessary) and fill the error OUT parameter.

Regards,
Florian

[shivam_bedwal · Accepted solution]

Hello Florian,

Thank you for your response.

Kindly find below my updated code as per your inputs. Still I get the same error when I execute the xsodata service from UI.

table.hdbtable ----------------------

COLUMN TABLE "sample.db::table" (

"ID" INTEGER NOT NULL,

PRIMARY KEY ("ID")

);

insert.hdbprocedure-------------------------

procedure "sample.db::insert"

(IN im_row "sample.db::table")

language sqlscript

sql security invoker as

idnew INT;

begin

select ID into idnew from :im_row;

insert into "sample.db::table" values (:idnew);

end;

table.xsodata-----------------------------------

service {

"sample.db::table" as "Tables"

create using "sample.db::insert";

}

UI5 application index.html-----------------------------------------

var oParams = {};

oParams.json = true;

oParams.defaultBindingMode = sap.ui.model.BindingMode.TwoWay;

oParams.defaultUpdateMethod = "PUT";

oParams.useBatch = false;

var oModel = new sap.ui.model.odata.v2.ODataModel( "/table.xsodata/", oParams);

sap.ui.getCore().setModel(oModel, "tableModel");

var oEntry = {};

oEntry.ID = "2";

oModel.setHeaders({ "content-type": "application/json" });

var mParams = {};

mParams.success = function() { sap.m.MessageToast.show("Create successful"); };

mParams.error = function(oError) {

if (oError.statusCode === 500 || oError.statusCode === 400) {

var errorRes = JSON.parse(oError.responseText);

return; }

else {

sap.m.MessageBox.alert(oError.response.statusText);

return; } }

oModel.read("/Tables");

oModel.create("/Tables", oEntry, mParams);

The oModel.read("/Tables") call is successful, but the oModel.create("/Tables", oEntry, mParams) call is failing with error -

"Error while executing a DB query".

Am I doing something wrong while passing data in json format from the UI. May be the hdbprocedure is interpreting it differently.

Please provide your inputs.

Regards,

Shivam

[pfefferf · Accepted solution]

Why you named the IN parameter of the procedure IM_ROW instead of NEW like requested? And where is the OUT error parameter?

[shivam_bedwal · Accepted solution]

Hello Florian,

Now I have maintained the INPUT and OUTPUT parameters for the insert procedure as has been mentioned in the documentation.

procedure "insert"
     (IN new "table", OUT error "error") 
language sqlscript 
sql security invoker as 
     idnew INT;
begin
	 select ID into idnew from :new;
     if :idnew < 0 then
          error = select 400 as http_status_code, 
          'invalid ID' error_message, 
          'value must be >= 1000' detail from dummy;
     else
          insert into "table" values (:idnew);
     end if;
end;



The error.hdbtabletype---------------
TYPE "error" AS TABLE (
	"HTTP_STATUS_CODE" INTEGER,
	"ERROR_MESSAGE" NVARCHAR(100),
	"DETAIL"  NVARCHAR(100) 
)


table.hdbtable--------------------
COLUMN TABLE "table" (
	"ID" INTEGER NOT NULL,
	PRIMARY KEY ("ID")
);


index.html-------------------

var oParams = {};
		oParams.json = true;
		oParams.defaultBindingMode = sap.ui.model.BindingMode.TwoWay;
		oParams.defaultUpdateMethod = "PUT";
		oParams.useBatch = false;
	    var oModel = new sap.ui.model.odata.v2.ODataModel(
		          "/table.xsodata/", oParams);
	  		
	    sap.ui.getCore().setModel(oModel, "tableModel");  
	          
		var oEntry = {};
		oEntry.ID = "2";
		oModel.setHeaders({
			"content-type": "application/json"
		});
		var mParams = {};
		mParams.success = function() {
			sap.m.MessageToast.show("Create successful");
		};
		mParams.error = function(oError) {
			if (oError.statusCode === 500 || oError.statusCode === 400) {
			var errorRes = JSON.parse(oError.responseText);
			return;
		} else {
			sap.m.MessageBox.alert(oError.response.statusText);
			return;
		}
		}
		oModel.read("/Tables");
		oModel.create("/Tables", oEntry, mParams);

I still get the error.

Kindly provide your inputs.

Regards,

Shivam

[former_member719482 · Accepted solution]

Hi Shivam, were you able to solve this.I am also facing exactly same issue