Debug generated SQL by CAP framework

[WouterLemaire]

Introduction

The Cloud Application Programming model is a great framework that generates a lot of stuff for you. But what do you do when something goes wrong behind the scene? How do you know what the framework does?

In this blog, I’m going to share how you figure this out. It’s a very small trick so I will keep it short 🙂

 

Problem

I faced this problem recently. The build and deploy scripts worked perfectly fine. Running the project and accessing the entities was also not a problem except for one specific type of request. When I tried to expand an association on one of the entities together with a filter on a field in the association, I got a SQL error. In my case this was “SQLITE_ERROR: no such column: a.<Association>.<Searchfield>”, more details regarding my problem are in this post on the SAP community:

https://answers.sap.com/questions/13057758/cap-filter-not-working-on-expanded-entity.html

Besides my example, this can also occur in others cases. When using a framework, there can always be bugs in the framework and maybe not your code. You could then start debugging all the libraries to figure out what happens… Instead of that, I found another trick that could save some time.

 

Solution

The solution I use to figure out what the framework does, is looking for the SQL command that the framework generates. Normally when you run “cds run” and you access an entity, you will just see the information of the request. For example, I access the entity “Authors”:

This will give the following result in the console:

 

How to know the SQL query that the framework runs on the database? Try enabling verbose when running “cds run”? Not an options, this options is invalid...

 

So how can we get more information about what is happening? We need to enable debugging when running “cds run”. How? There are two options:

• Use the global environment variable “debug”
  
  
  
  • Simple run the command “set debug=true” in your command line (when using windows)
  
  
  
  

• enable debugging in “default-env.json” file
  
  
  
  • if you do not already have this file, create it in the root directory of your project
  
  
  
  

• 
  
  
  
  • add “debug”:true to the root of this file
  
  
  
  

 

When you now run “cds run” and access one of the entities with a filter or any odata parameter you like, you will see the generated SQL in the console. In case I go to the entity “Authors” now, I will see the following:

The debug mode will also provide more information when starting the command “cds run” about creating the tables, views and filling the data.

 

This is how I found out that CAP generated the wrong SQL command. Which probably means that there is a potential bug in the framework… It immediately tells you what goes wrong without debugging the framework.

Hope this will help you as well 😉