Issue with executing a basic query in Hana 2.0 SPS 03 through xsjs

[veeresh1]

I am trying to execute the below code in the Hana 2.0 SPS 03 environment.

TEMP_DATA is the context name and Entity is the entity name. I don't have namespace in my project.

Straight forward but when I run this, I get an error "500 invalid column name: ID" but when I change the query to add a double quotes to the id column(as shown below) my query executes.

var query = 'select "id" as "Identifier" FROM TEMP_DATA.Entity';

But this would be a problem to me since I have made my context and entity names to match my 1.0 names, so the code in 1.0 works in 2.0. If I have to add quotes for the columns in the select clause, I have to manually do it for all the queries in all the files which would time consuming. So is there a way to make all the queries in 1.0 artifacts to work in 2.0 with just the table name change?

P.S. I found the same issue in procedures and functions too

[thomas_jung · Accepted solution]

>The column creation is not the problem here. I have made it to match the 1.0 definition

But I believe it is the problem. Query 1 only works if the id column is actually ID in the DB. If you don't quote the column it is converted to all upper case. Query 2 only works if the Column is explicitly defined as id in all lower case. I think if you look at your table definition in the catalog you will see a difference from the old version to the new. This is why I asked you how you were creating the table. If using CDS, the rules of syntax directly in relation to case of the columns has changed. Therefore the same CDS syntax will NOT result in the same thing being created in the DB.

[veeresh1 · Accepted solution]

This makes a lot of sense. Yes, as mentioned, I have the column as id(lower case). I can change it to ID and test this.

Meanwhile, the table definition is in CDS format for 2.0. My intension is to make all the artifacts in 1.0 to work in 2.0 with minimal effort. So if I convert any column names with lower case to upper case in my table definition, then any query executing in the artifacts with lower case column names will be automatically converted to the upper case, but the execution will proceed smoothly

[thomas_jung]

How are you creating this table? I think when you create it you should specify the case of the column to match what you had before if you want to avoid having to adjust the query. Are you using HDBCDS? There are differences in the use of case and you might have to adjust the definition.

[veeresh1]

The column creation is not the problem here. I have made it to match the 1.0 definition. The issue is wrt the query formation in xsjs and procedures.

case 1: var query = 'select id as "Identifier" FROM TEMP_DATA.Entity';
case 2: var query = 'select "id" as "Identifier" FROM TEMP_DATA.Entity';

In case 1: the id is not wrapped with a double quotes and that is how the query is written in 1.0 files. But when executing it as it is in 2.0, I get an error "500 invalid column name: ID"

In case 2: When I add "" to my column(i.e. from id to "id"), my code executes.

Take the example of a much more complex query having case statements, aggregations, subqueries, etc. If my query executes as case 1, I can just change the table names(if needed) while migrating using a simple script. But if I have to enclose all my columns with quotes, as in case 2, I can't do it using a script coz as I mentioned, there are complex scenarios and also in many cases the query is built using multiline strings or even passed around functions and the query string is generated.