How to support both MySQL and SAP HANA during development phase

Have you ever experienced a project that require support both MySQL and SAP HANA during the development phase.

In this blog post, I’d like to share my experience of how to support MySQL and SAP HANA during the development phase.

If We want to develop applications that support both MySQL and SAP HANA at the same time. We have to find the difference between them.

I will compare the differences from below three points.
1. Primary key generation
2. Data type
3. Function

Primary key generation

MySQL must have a primary key (PRIMARY KEY) when building a table, and each primary key content must be unique as a unique identifier for the piece of data in the table. At the same time, the primary key is often given an “auto_increment” attribute, so that each record in the field of the primary key is incremented by “1”.

SAP HANA doesn’t have this “auto_increment” attribute, so it can’t define a self-increment primary key in a table like MySQL. However, the sequence in SAP HANA (SEQUENCE) can indirectly achieve the role of the self-increment primary key.

MySQL implement:

CREATE TABLE `USER` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SAP HANA implement:

CREATE COLUMN TABLE "USER" (
"ID" BIGINT CS_FIXED GENERATED BY DEFAULT AS IDENTITY NOT NULL ,
"NAME" NVARCHAR(200),
"CREATED_DATE" LONGDATE CS_LONGDATE,
PRIMARY KEY ("ID")) UNLOAD PRIORITY 5  AUTO MERGE ;
CREATE SEQUENCE "USER_SEQUENCE";

JAVA implement sequence:

 @Id
 @GeneratedValue(strategy = GenerationType.AUTO, generator = "sequence_generator")
 @SequenceGenerator(name = "sequence_generator", initialValue = 1, allocationSize = 1, sequenceName = "USER_SEQUENCE")
 private Long id;

Data type

For data type I just compare some frequently-used ones like below picture.

Function

For Function I also just compare some frequently-used ones, please see below items.

1. Globally unique identifier

-- MySQL
uuid()

-- SAP HANA
select SYSUUID from dummy;

2. SUBSTRING

-- MySQL
select mid('abcdefg',2,3);
select substring('abcdefg',2);
select substring('abcdefg' from 2);

-- SAP HANA
select left（’abcdefg’, ‘5’） from dummy;
select right（’abcdefg’, ‘5’） from dummy;

3. Before or after the current day

-- MySQL
-- The day before today
 SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY),'%Y-%m-%d');
-- The day after today
 SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL 1 DAY),'%Y-%m-%d');

-- SAP HANA
-- The day before today
SELECT ADD_DAYS(CURRENT_TIMESTAMP,-1) from dummy
-- The day after today
SELECT ADD_DAYS(CURRENT_TIMESTAMP,1) from dummy

4. GROUP BY

-- MYSQL
-- Mysql group by statement can select fields that are not grouped
select id,name,age from A group by age

-- SAP HANA
-- HANA group by statement can't select fields that are not grouped
select id,name,age from A group by id

5. Data type casting

-- MYSQL
-- CAST
select * from dummy where is_deleted=0 order by
 case when dummy.status='Draft' then cast(1 as signed)
 when dummy.status='WaitApproval' then cast(2 as signed)
 when dummy.status='Reject' then cast(3 as signed)
 when dummy.status='Approve' then cast(4 as signed)
 else cast(5 as signed) end asc

-- SAP HANA
-- CAST
select * from dummy where is_deleted=0 order by
 case when dummy.status='Draft' then cast(1 as integer)
 when dummy.status='WaitApproval' then cast(2 as integer)
 when dummy.status='Reject' then cast(3 as integer)
 when dummy.status='Approve' then cast(4 as integer)
 else cast(5 as integer) end asc

-- MYSQL
-- CONVERT
convert(filed_name, data_type)

-- SAP HANA
-- NO CONVERT method

More information about the difference of the data types and functions, please email to me. I have prepare a document.

After mastered above knowledge. I believe we can easily develop applications that support both MySQL and SAP HANA  at the same time.