CDS – creating a table and a view – Part 1
Hello, In this blog post, I will showcase how to create a table and a view via the Core Data Services (CDS).
In my example I will be using the HCP trial landscape and the Web Development Workbench editor.
1) Create a CDS file ( .hdbdd extension)
2) Add some (correct) content and activate the file
3) Go to your schema and open the tables folder to see the newly generated table.
4) Perform a select statement – as expected, initially your table will be empty
5) Right click on the table, select “Generate Insert” or type it (if you want to make spelling mistakes) 🙂
6) Your generated insert statement is opened on a new tab. then, you can save it as a .sql file (selct a location on your package)
7) I opened the sql file, copied and pasted into the SQL console so i could run it. Notice console output successful insert message, “Rows affected 1” after I ran my insert statement.
8) after I was able to insert my statement, then I went back to my select statement and I ran it again to verify my record was in fact inserted. As a result, you can see my first record in the table
9) After a few inserted records, we can see the table again with more rows populated.
10) Since I have a table, then I want to create a simple view. I went back to my CDS file and I declared a view. Then I re-activated my file.
11) Once the hdbdd file was activated, then I went back to my Catalog and opened the Views folder where my newly created view was saved.
12) Once again, I did a right click on the view and generated a select statement.( I avoid typing my SQL as much as possible so that I do not end up having to type it over and over before I get the correct spelling 🙂 but up to you to try to get the name right the first time.
13) My select statement was generated
14) After the statement was generated I ran it (I changed it a little but the one above should have returned the same number of records since I only have 6 on my example. See output below
* Be careful if you are doing this on a table with a lot of records as you may select *
Thins to remember about CDS:
1) Stored procedures and sequences cannot be created in CDS (as of SP10)
2) CDS files may contain one or more entities or views and when it is activated, your entities, views, etc are directly created as design time objects on your schema and are ready for use.
3) HCP trial ( in your Web Development Workbench) is very helpful for learning how to use CDS ( as you may also use HANA Studio)
Things to forget:
1) Forget to re-run SQL statements when promoting your code from one environment (such as dev) into another environment (such as QA or Prod). CDS plays very well with HALM during migrations.
2) Forget to think you are alone on this journey, you have an entire SCN group behind you
3) (I forgot 3 – i am working on the next exercise on my Workbench, aren’t you?)
Again, this is a very simple document explaining how to create a table and a view via CDS. Please share your experience or struggles with CDS. Happy programming. For a more advanced scenario, please read part 2 of this blog where I showcase a view with a 1 to 1 association on a table
Here are some helpful links I used while writing this blog:
I have a few follow up questions you might help me understand..
I want to use CDS so I can use HALM for migrating my objects between environments.
I'm not using hdbtable files because as far as I understand, they don't supports spatial data.
So to the questions:
1. Associations - Why using them (other then easier creation of joins in a view)? I noticed that no foreign keys were created on the runtime objects. Isn't it the meaning of the association?
2. Inserting/Selecting and using Procedures - If I create my model with CDS - What is the best way to work with the runtime objects? Do I "have to remember" the runtime objects names? Is there another method of selecting/inserting to the tables? (I'll do it using Procedures - so I can migrate the same logic with HALM).
3. I'm using spatial data - Do you know of any limitations if I use the CDS? (e.g I noticed I can't make a calculation field that it's answer is a spatial type)
4. Is there any solution like SQL "GENERATED ALWAYS AS" column creation?
thank you for reading the blog and also very good questions. I will try to answer as best as possible.
1) regarding associations - they do in fact create associations, however, I do not think they must create foreign keys. here is the official documentation for CDS associations
2) you may still create stored procedures as design time objects such as .hdbprocedure objects and they will migrate with HALM just fine. as far as selecting and inserting, I am aware of the stored procedures which would be the easiest and cleanest, however, depending on your need, you could also try insert/select statement from XSJS but I do not recommend it.
3) Spatial data is now available on CDS as of SPS11 - here is the documentation
I have not used spatial data myself on CDS, however, I have used it directly on the HANA DB as of SP10. my situation was to calculate distances between 2 points so I went about it as creating a stored procedure and then reading from my lat/lon and doing the calculation like that.
4) I am not aware of the SQL GENERATED AS column creating
I hope I answered most of your questions and if you still have more, please post it here or via the corresponding SCN forum.
Thanks again Yaron