Skip to Content
0

HANA - FUNCTION TO GENERATE PRIMARY KEY CALLING A INSERT STATEMENT

Dec 08, 2016 at 01:59 PM

236

avatar image
Former Member

Hello Experts,

I created a function on HCP and I would like to use it in the INSERT statement for generating the primary key.

Below is the FUNCTION code:

CREATE FUNCTION plant_key_increment_v2()
 RETURNS output NVARCHAR(64) LANGUAGE SQLSCRIPT AS
 BEGIN
 DECLARE C_PREFIX VARCHAR(64) := 'PLANT';
 DECLARE AUX VARCHAR(64);
 DECLARE I_AUX INT;
 SELECT SUBSTRING(MAX(PLANTID), 6, 3) INTO AUX FROM "SYSTEM"."MPLANT_ATTR";
 I_AUX := TO_INT(AUX);
 I_AUX := :I_AUX + 1;
 IF :I_AUX <= 9 THEN
 output := CONCAT (C_PREFIX, CONCAT('00', TO_VARCHAR(I_AUX)));
 END IF;
 IF :I_AUX > 9 AND :I_AUX <= 99 THEN
 output := CONCAT (C_PREFIX, CONCAT('0', TO_VARCHAR(I_AUX)));
 END IF;
 IF :I_AUX > 99 THEN
 output := CONCAT (C_PREFIX, TO_VARCHAR(I_AUX));
 END IF;
 END

Here I am trying to use the function in the insert statement:

INSERT INTO "SYSTEM"."MPLANT_ATTR" VALUES("SYSTEM"."PLANT_KEY_INCREMENT_V2"(),'2016-12-08T13:34:27+00:00',12,1,'','null','null',null);

And here is the error I got while executing the INSERT statement:

Is it possible to create a sequence for this purpose? How it would be done once I need to concatenate PLANT + SEQUENCE NUMBER, for instance PLANT001, PLANT002...

Many thanks,

Filipe

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

1 Answer

Best Answer
Florian Pfeffer
Dec 08, 2016 at 02:47 PM
2

Hello Filipe,

user-defined scalar functions cannot be called in Insert statements.

It is also not possible to create a sequence for your requirement. A sequence can only deal with int32 values, but not with alphanumeric values.

Options you have so far:

  • Use a trigger to set the primary key according to your requirement (with XS classic not the best option, cause triggers cannot be transported; with XS Advanced ok).
  • Create a wrapper procedure which determines the primary key using e.g. your function and inserts the data.

One recommendation regarding your procedure logic. You should replace max(plantid) select in your coding by a coding which determines the number using a sequence. So a valid concurrency control is applied. With your coding there is possibility that several similar executed processes determine the same max plantid.

Regards,
Florian

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

Hello Florian,

thanks a lot for the quick answer but I have another question once I am new to this kind of development.

I created a ODATA service as follows:

I am consuming this service in my HTML5 app and It is working properly, I have configured the destinations, routes to be able to consume it.

The thing is that I need to create a 'EXIT' calling a procedure before the ODATA service insert a row in my table - In case I am doing a POST request.

The reason behind that is that I am going to generate de table KEY which is not coming from the front-end, and only after that I will call the insert statement.

Is it possible to achieve that?

Best regards,

Filipe

0

Sure this can be done using an OData service exit. For that a link write operation has to be used as described in the documentation. An example (implemented with XSJS) can be found in the SHINE demo content.

0
Former Member
Florian Pfeffer

Florian,

I checked already the documentation you've mentioned but It seems that it does not meet my requirements:

There is no link between my application (where the ODATA Service file stands - 'EDITOR' perspective) and my SCHEMA (where the procedures and tables stands - CATALOG perspective).

I mean, I don't have any .hdbtable file in EDITOR perspective, only my .XSODATA as follows which is exposed. I can only use it because of the destination, I am developing the APP on WEBIDE by simply calling the REST URL.

Additionally it seems that the doc way of creating the ODATA is slightly different from mine:

"sample.odata::table"

I think that I am missing some concepts. How my procedure created directly on my SCHEMA (CATALOG perpective) will be visible in my ODATA definition? How to call it?

thanks,

Filipe

0

It has nothing to do with an .hdbtable artifact.

Maybe following example helps you.

XSOData definition uses "create using ..." statement to define which procedure has to be called in case a new entry is created:

service {
    "MISC"."MISC_PLANT" as "Plants"
    create using "test.misc.procedures::plantCreateExit";
}

The procedure itself inserts the data into the table. Before the insert a new ID is created using a sequence (and combined with value "PLANT"):

PROCEDURE "MISC"."test.misc.procedures::plantCreateExit" ( 
    IN new_data "MISC"."MISC_PLANT",
    OUT ex_error table ( http_status_code integer, error nvarchar(100) )
)
   LANGUAGE SQLSCRIPT
   SQL SECURITY INVOKER
   AS
BEGIN
    declare lv_description nvarchar(100) := :new_data.DESCRIPTION[1];

   insert into "MISC"."MISC_PLANT" values ('PLANT' || "test.misc.data::plantId".NEXTVAL, :lv_description);
   
   if 1 = 0 then
      ex_error = select 500 as http_status_code, 'dummy' as error from dummy;
   end if;
END;

2
Former Member
Florian Pfeffer

Hi Florian, thank you very much! You are omnipresent.

I created a XS PROJECT in Eclipse and shared it already. But when I execute my new OData Service, I am getting an authorization error:

Odata service definition:

Error Message:

.xsaccess Definition:

Please let me know if I am missing something.

Regards,

Filipe

0

Does your user have select privileges on the table?

1
Former Member
Former Member

I just solved the issue by creating a new role a assigning it to my user:

role MYCONECTEDGARDEN::model_access {
catalogschema "MYSCHEMA": SELECT, UPDATE, INSERT, DELETE;
}

Many thanks again!

0
Former Member
Florian Pfeffer

Hi Florioan,

I just implemented a procedure for the CREATE exit but now I can't execute the POST request using my ODATA cause I am getting an authorization issue:

Service exception: [258] insufficient privilege

0

Then please check if your user has the necessary rights. I would do that in case of that message. You not?

1
Show more comments