Logical unit of work in HANA?

[former_member184795]

I'm hoping one of you guys can help. We're trying to create a sql script procedure in HANA where we have multiple dml statements grouped together, and based on whether those are successful or not, then we want to either COMMIT or ROLLBACK respectively. This seems like a basic enough function of a dbms, but we're struggling with implementing it.  Even adding a COMMIT/ROLLBACK statement in a simple create procedure gives a feature not supported error. I've tried to create this in Studio and hdbsql.

Changed the end line marker to hash. I want auto commit to be off in this case.

DROP PROCEDURE test_ins#

CREATE PROCEDURE test_ins ( ) LANGUAGE SQLSCRIPT AS
BEGIN

INSERT INTO SH_TEMP VALUES (1, 1);
COMMIT;

END#

* 7: feature not supported: line 1 col 137 SQLSTATE: HY000

Create statement for table below.

CREATE TABLE HOLLAS.SH_TEMP (KEY INT PRIMARY KEY, VAL INT)#

On HANA SPS05, Studio 48, Client 48

[lucas_oliveira]

Hi,

Yes, that's not supported nor recommended. There was a discussion some time ago regarding this approach (here to be more precise). And as you may see from the comments that's not what you're supposed to be doing.

In short: why commit/rollback at DB procedure level? It is usually better to send the errors from the inner code (procedures, etc) and treat those at controller level (xsjs, Java, python, whathever you're using).

Something like (xsjs speaking here):

...
try {
var conn = $.hdb.getConnection();
conn.setAutocommit(false);


// call your SQL procedure...
var fnProc = connection.loadProcedure('YOURSCHEMA', 'namespace::YourProcedure');
var results = fnProc('blablabla');


//get results and....
/// [...]
// if it's all good
conn.commit();


} 
catch(ex) {  //ehhh, something went wrong
printNiceErrorScreen(ex);
conn.rollback();
}
...

For relational related exceptions, the db will do the work for you and signal an error (which your application can catch, like in the code above). For business related exceptions you can create your own sql error codes and signal those from within your procedure.

How to create and manage? Take a look into the official documentation on that:

Exception Handling Examples - SAP HANA SQLScript Reference - SAP Library

Finally, there are quite a few examples in the SHINE application (not particularly using procedures, but still proves the point here). If you have that installed on your system I'd go and check the code.

I hope that helps.

ps.: HANA SPS05? We're in SPS10 now... sps05 has been a 'no no' for quite some time already.

BRs,

Lucas de Oliveira