Skip to Content
Technical Articles
Author's profile photo Wouter Lemaire

Debug generated SQL by CAP framework

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 😉

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Nabheet Madan
      Nabheet Madan

      Thanks for sharing the great tip Wouter Lemaire. I wanted to understand how did you discover it?

      Author's profile photo DJ Adams
      DJ Adams

      "And the curious shall inherit the earth" 🙂

      I often start with searching the source code. In my case, this is a good beginning:

      grep -iR debug "`dirname $(which cds)`/../lib/node_modules/@sap"  | more

      this shows me lines (with filenames) in the @sap/cds-dk package, wherever it happens to be, with references to "debug" (case-insensitive). From there I can take the next steps.

      Author's profile photo DJ Adams
      DJ Adams

      Also, you should already know this 😉 See https://blogs.sap.com/2019/10/22/annotated-links-episode-40-of-hands-on-sap-dev-with-qmacro/#debug

       

       

      Author's profile photo Wouter Lemaire
      Wouter Lemaire
      Blog Post Author

      Not as cool as the answer of DJ Adams  but I found a small clue in the changelog by using the global search on https://cap.cloud.sap/docs/

      Author's profile photo DJ Adams
      DJ Adams

      Your stuff is always cool, Wouter.

      Author's profile photo DJ Adams
      DJ Adams

      Nice tip, Wouter - btw, on non-Windows systems the env var is DEBUG not debug, so one would need, for example:

      DEBUG=true cds watch

      if you wanted to turn debug level on on the command line

      Author's profile photo Wouter Lemaire
      Wouter Lemaire
      Blog Post Author

      Thanks DJ!

      Author's profile photo p. plenkov
      p. plenkov

      It looks like "true" doesn't work anymore in 2021. But here is good news. Now we can do same with DEBUG=db, where db - is your db module name.

      https://cap.cloud.sap/docs/releases/feb20#miscellaneous