Skip to Content
0

how to use insert query in controller.js file

Feb 25, 2017 at 02:59 PM

68

avatar image
Former Member

Hi professionals,

I'm new to SAP HANA Cloud Platform and I have a question to ask...

how to call stored procedure or execute insert statement in controller.js file ??

I'm using SAP HANA Web-Based Development Workbench...

here is my code:

(.controller.js file)

navaddbrownies: function() {

var oView = this.getView();

var tem = {};

tem.DISHESID = oView.byId("browniesidinput").getValue();

tem.DISHESNAME = oView.byId("browniesnameinput").getValue();

tem.DISHESDESC = oView.byId("browniesdescinput").getValue();

tem.DISHESPRICE = oView.byId("browniespriceinput").getValue();

this is my button function, when I click this button , I want to stored these 4 data into my table.

my table name is : testing.table::Dishes.Dishes

my stored procedure name with 4 input parameters is : testing.procedure::CreateDishes()

Thanks for helping me.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Florian Pfeffer
Feb 25, 2017 at 03:18 PM
0

If you wanna have CRUD functionality I would recommend to create and XSOData service on HANA (maybe with Validition and/or Modification Exits depending on your requirements).

If you wanna use your procedure you have to wrap it in a XSJS service which can than be called using jQuery.ajax.

Regards,
Florian

Show 7 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Florian,

Thanks for helping me, but I couldn't solve the problem yet...

are those code correct ?? with all these code , I couldn't insert any data into my table... please tell me if my code has problem or some file or code is missing.

Thanks.

this is my button function

navaddbrownies: function() { 
var oView = this.getView();
var oModel = this.getView().getModel();
var tem = {};
tem.DISHESID = oView.byId("browniesidinput").getValue();
tem.DISHESNAME = oView.byId("browniesnameinput").getValue(); tem.DISHESDESC = oView.byId("browniesdescinput").getValue();
tem.DISHESPRICE = oView.byId("browniespriceinput").getValue(); oModel.create("/Dishes", tem, null, sap.m.MessageToast.show("success"), sap.m.MessageToast.show("fail")); oModel.refresh();

and this is my Connection.xsodata file:

service{
"_SYS_BIC"."testing.table::Dishes.Dishes" as "Dishes";
"_SYS_BIC"."testing.table::OrderStock.OrderStock" as "OrderStock";
"_SYS_BIC"."testing.table::Receiving.Receiving" as "Receiving";
}

I've tried by putting creat using procedure at the back of the Dishes, but it couldn't work...

service{
"testing.table::Dishes.Dishes" as "Dishes" create using "testing.procedure::CreateDishes";
"testing.table::OrderStock.OrderStock" as "OrderStock";
"testing.table::Receiving.Receiving" as "Receiving";
}

this is my table Dishes.hdbdd

namespace testing.table;
@Schema: '_SYS_BIC'
context Dishes {
   
   @Catalog.tableType: #COLUMN
   Entity Dishes{
       key DISHESID: String(10);
       DISHESNAME: String(100);
       DISHESDESC: String(1000);
       DISHESPRICE: Decimal(10,2);
   };
   
   context procedures{
   
        type Dishes{
       DISHESID: String(10);
       DISHESNAME: String(100);
       DISHESDESC: String(1000);
       DISHESPRICE: Decimal(10,2);
   };
   type errors {
            HTTP_STATUS_CODE : Integer;
            ERROR_MESSAGE : String(100);
            DETAIL : String(100);
        };  
    };
};

and this is my procedure CreateDishes.hdbprocedure file ( is this procedure helps ?? )

PROCEDURE "_SYS_BIC"."testing.procedure::CreateDishes"(
		IN intab "testing.table::Dishes.procedures.Dishes",
OUT outtab "testing.table::Dishes.procedures.errors"
	)
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER
AS


BEGIN
declare t_DISHESID STRING;
declare t_DISHESNAME STRING;
DECLARE t_DISHESDESC STRING;
DECLARE t_DISHESPRICE DECIMAL(10,2);


SELECT DISHESID , DISHESNAME , DISHESDESC , DISHESPRICE
INTO t_DISHESID , t_DISHESNAME , t_DISHESDESC , t_DISHESPRICE from :intab;
if :t_DISHESID = NULL then
outtab = select 500 as http_status_code,
'Invalid title~! ' || t_DISHESID as error_message,
'No Way! Title field must not be empty' as detail from dummy;
else
	INSERT INTO "testing.table::Dishes.Dishes" VALUES(t_DISHESID, t_DISHESNAME, t_DISHESDESC, t_DISHESPRICE);
END IF;
END;
0

Do you get any errors when you trigger the POST via the oModel.create function. Please check that in the network console of your browser.

1
Former Member
Florian Pfeffer

Hi Florian,

there are many errors, but most of it are "sap-ui-core.js" error...

another error is the create function error, it says " Uncaught TypeError: Cannot read property 'create' of undefined " , which is this line

oModel.create("/Dishes", tem, null, sap.m.MessageToast.show("success"), sap.m.MessageToast.show("fail"));

I saw some example are using this function, but why I get this error ??

Thanks.

0

There can be different reasons for that error. How did you create the OData Model (manually or is it created automatically via the app descriptor)? Please share the relevant code positions.

0
Former Member
Florian Pfeffer

Hi Florian,

this is my Dishes.controller.js file

sap.ui.define(["sap/ui/core/mvc/Controller"], function(Controller) {
	"use strict";
	return Controller.extend("testing.controller.DishesView", {
		navback: function() {
			var app = sap.ui.getCore().byId("testing");
			app.back();
		},	
		navaddbrownies: function() {
			var oView = this.getView();
			var oModel = new sap.ui.model.odata.ODataModel("Connection.xsodata");
			//sap.ui.getCore().setModel(oModel);

			var tem = {};
			tem.DISHESID = oView.byId("browniesidinput").getValue();
			tem.DISHESNAME = oView.byId("browniesnameinput").getValue();
			tem.DISHESDESC = oView.byId("browniesdescinput").getValue();
			tem.DISHESPRICE = oView.byId("browniespriceinput").getValue();
			oModel.create("/Dishes", tem, null, sap.m.MessageToast.show("success"), sap.m.MessageToast.show("fail"));
			oModel.refresh();			
}
	});
});

this is my models.js file

sap.ui.define([
	"sap/ui/model/json/JSONModel",
	"sap/ui/Device"
], function(JSONModel, Device) {
	"use strict";
	return {
		createDeviceModel: function() {
			var oModel = new JSONModel(Device);
			oModel.setDefaultBindingMode("OneWay");
			return oModel;
		}
	};
});

this is my CreateDishes.hdbprocedure file

PROCEDURE "_SYS_BIC"."testing.procedure::CreateDishes"(
		IN intab "testing.table::Dishes.procedures.Dishes",
OUT outtab "testing.table::Dishes.procedures.errors"
	)
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER
AS

BEGIN
declare t_DISHESID STRING;
declare t_DISHESNAME STRING;
DECLARE t_DISHESDESC STRING;
DECLARE t_DISHESPRICE DECIMAL(10,2);

SELECT DISHESID , DISHESNAME , DISHESDESC , DISHESPRICE
INTO t_DISHESID , t_DISHESNAME , t_DISHESDESC , t_DISHESPRICE from :intab;
if :t_DISHESID = NULL then
outtab = select 500 as http_status_code,
'Invalid title~! ' || t_DISHESID as error_message,
'No Way! Title field must not be empty' as detail from dummy;
else
	INSERT INTO "testing.table::Dishes.Dishes" VALUES(t_DISHESID, t_DISHESNAME, t_DISHESDESC, t_DISHESPRICE);
END IF;
END;

this is Connection.xsodata file

service{
"testing.table::Dishes.Dishes" as "Dishes" create using "testing.procedure::CreateDishes";
"testing.table::OrderStock.OrderStock" as "OrderStock";
"testing.table::Receiving.Receiving" as "Receiving";
}

Dishes.hdbdd file

namespace testing.table;
@Schema: '_SYS_BIC'
context Dishes {
   
   @Catalog.tableType: #COLUMN
   Entity Dishes{
       key DISHESID: String(10);
       DISHESNAME: String(100);
       DISHESDESC: String(1000);
       DISHESPRICE: Decimal(10,2);
   };
   
   context procedures{
   
        type Dishes{
       DISHESID: String(10);
       DISHESNAME: String(100);
       DISHESDESC: String(1000);
       DISHESPRICE: Decimal(10,2);
   };
   type errors {
            HTTP_STATUS_CODE : Integer;
            ERROR_MESSAGE : String(100);
            DETAIL : String(100);
        };  
    };
};

Thanks for helping me to solve my problems...

0

I asked for the place where you created the OData model which points to your OData service. If you don't have such a coding then maybe that is the issue :-)

0
Show more comments