Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
werner_daehn
Active Contributor
Recently I wanted to create a hdbdd file with multiple entities, based on a virtual table. This code works with any table though, as it is based on the table_columns dictionary view.

This procedure creates the text for a single Entity, so use it in another procedure which calls it once per table and add the boiler plate text for a complete hdbdd file.

I took extra care on the data types but it seems some data types cannot be used in CDS, like the CHAR/NCHAR (fixed length strings) or BOOLEAN.

Should work with all Hana versions 1.0 included.
CREATE PROCEDURE "create_hdbdd" (
p_schema_name nvarchar(256),
p_table_name nvarchar(256),
out p_text nclob
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA AS
BEGIN
declare v_line nvarchar(500) array;
declare v_current_line nvarchar(500);
declare v_data_type nvarchar(30);
declare v_count integer;
declare cursor c1 for
select c.column_name, c.data_type_name,
c.length, c.scale, c.is_nullable,
c.comments, p.is_primary_key
from table_columns c
left outer join constraints p
on (p.is_primary_key = 'TRUE' and
p.schema_name = c.schema_name and
p.table_name = c.table_name and
p.column_name = c.column_name)
where c.schema_name = :p_schema_name and
c.table_name = :p_table_name
order by p.position nulls last, c.position;
declare v_crlf NVARCHAR(2);

v_crlf = BINTOSTR(HEXTOBIN('0D0A'));

for r_row as c1 do
if r_row.is_primary_key = 'TRUE' then
v_current_line := ' key ';
else
v_current_line := ' ';
end if;
v_current_line = rpad(
v_current_line || r_row.column_name,
40) || ': ';

if r_row.data_type_name = 'ALPHANUM' then
v_data_type := 'hana.ALPHANUM(' || r_row.length || ')';
elseif r_row.data_type_name = 'BIGINT' then
v_data_type := 'Integer64';
elseif r_row.data_type_name = 'BINARY' then
v_data_type := 'hana.BINARY(' || r_row.length || ')';
elseif r_row.data_type_name = 'BINTEXT' then
v_data_type := 'LargeBinary'; -- ?????????????
elseif r_row.data_type_name = 'BLOB' then
v_data_type := 'LargeBinary';
elseif r_row.data_type_name = 'BOOLEAN' then
v_data_type := 'Integer'; -- ?????????????
elseif r_row.data_type_name = 'CHAR' then
v_data_type := 'hana.VARCHAR(' || r_row.length || ')';
elseif r_row.data_type_name = 'CLOB' then
v_data_type := 'hana.CLOB';
elseif r_row.data_type_name = 'DATE' then
v_data_type := 'LocalDate';
elseif r_row.data_type_name = 'DECIMAL' then
if r_row.length = 0 then
v_data_type := 'DecimalFloat';
else
v_data_type := 'Decimal(' || r_row.length ||
', ' || r_row.scale || ')';
end if;
elseif r_row.data_type_name = 'DOUBLE' then
v_data_type := 'BinaryFloat';
elseif r_row.data_type_name = 'INTEGER' then
v_data_type := 'Integer';
elseif r_row.data_type_name = 'NCHAR' then
v_data_type := 'String(' || r_row.length || ')'; -- ??
elseif r_row.data_type_name = 'NCLOB' then
v_data_type := 'LargeString';
elseif r_row.data_type_name = 'NVARCHAR' then
v_data_type := 'String(' || r_row.length || ')';
elseif r_row.data_type_name = 'REAL' then
v_data_type := 'hana.REAL';
elseif r_row.data_type_name = 'SECONDDATE' then
v_data_type := 'UTCDateTime';
elseif r_row.data_type_name = 'SHORTTEXT' then
v_data_type := 'String(' || r_row.length || ')';
elseif r_row.data_type_name = 'SMALLDECIMAL' then
v_data_type := 'hana.SMALLDECIMAL';
elseif r_row.data_type_name = 'SMALLINT' then
v_data_type := 'hana.SMALLINT';
elseif r_row.data_type_name = 'ST_GEOMETRY' then
v_data_type := 'hana.ST_GEOMETRY';
elseif r_row.data_type_name = 'ST_POINT' then
v_data_type := 'hana.ST_POINT';
elseif r_row.data_type_name = 'TEXT' then
v_data_type := 'LargeString';
elseif r_row.data_type_name = 'TIME' then
v_data_type := 'LocalTime';
elseif r_row.data_type_name = 'TIMESTAMP' then
v_data_type := 'UTCTimestamp';
elseif r_row.data_type_name = 'TINYINT' then
v_data_type := 'hana.TINYINT';
elseif r_row.data_type_name = 'VARBINARY' then
v_data_type := 'Binary(' || r_row.length || ')';
elseif r_row.data_type_name = 'VARCHAR' then
v_data_type := 'hana.VARCHAR(' ||
r_row.length || ')';
end if;
v_current_line := v_current_line || :v_data_type;

if r_row.is_nullable = 'FALSE' then
v_current_line := v_current_line || ' not null';
end if;
v_current_line := v_current_line || ';';
v_line[CARDINALITY(:v_line)+1] = :v_current_line;
end for;
p_text = ' @Catalog.tableType: #COLUMN' || v_crlf ||
' Entity ' || :p_table_name ||
' {' || v_crlf;
for v_count in 1..CARDINALITY(:v_line) do
p_text = p_text || :v_line[:v_count] || v_crlf;
end for;
p_text = p_text || ' }' || v_crlf;
END
Labels in this area