Inline SQL select statements - table name is always passed in lower case

[iliyan-velichkov]

When having a table with upper case and the following inline sql statement

CLASS zcl_dirigible_employee_dao DEFINITION PUBLIC.
  PUBLIC SECTION.
    TYPES:
      BEGIN OF ty_employee,
        ID         TYPE n LENGTH 10,
        FIRST_NAME  TYPE string,
        LAST_NAME   TYPE string,
      END OF ty_employee,
      ty_employees TYPE STANDARD TABLE OF ty_employee WITH DEFAULT KEY.
    CLASS-METHODS select_all
        RETURNING VALUE(rv_result) TYPE string.

ENDCLASS.
CLASS zcl_dirigible_employee_dao IMPLEMENTATION.
  METHOD select_all.
    DATA: lt_employees TYPE ty_employees,
          lv_json       TYPE string.

    SELECT ID FIRST_NAME LAST_NAME
      FROM EMPLOYEES
      INTO TABLE lt_employees.

    zcl_dirigible_response=>println(
      EXPORTING
        message_in = lt_employees ).
  ENDMETHOD.

ENDCLASS.

the table name is passed in lower case to registered DB.DatabaseClient implementation (method select). This way, the client cannot execute a valid select statement.

2024-06-14 15:04:22.926 [DEBUG] [http-nio-8080-exec-11] [default-tenant] a.o.e.d.DirigibleDatabaseClient - Executing select [SELECT "ID", "FIRST_NAME", "LAST_NAME" FROM "employees"]... Input options [{"select":"SELECT \"ID\", \"FIRST_NAME\", \"LAST_NAME\" FROM \"employees\""}]
2024-06-14 15:04:22.927 [ERROR] [http-nio-8080-exec-11] [default-tenant] o.e.d.c.api.db.DatabaseFacade - Failed to execute query statement [SELECT "ID", "FIRST_NAME", "LAST_NAME" FROM "employees"] in data source [null].
org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "employees" not found (candidates are: "EMPLOYEES"); SQL statement:
SELECT "ID", "FIRST_NAME", "LAST_NAME" FROM "employees" [42103-224]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:514) ~[h2-2.2.224.jar!/:na]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:489) ~[h2-2.2.224.jar!/:na]
    at org.h2.message.DbException.get(DbException.java:223) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.getTableOrViewNotFoundDbException(Parser.java:8067) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.getTableOrViewNotFoundDbException(Parser.java:8035) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.readTableOrView(Parser.java:8024) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.readTablePrimary(Parser.java:1788) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.readTableReference(Parser.java:2268) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.parseSelectFromPart(Parser.java:2718) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.parseSelect(Parser.java:2824) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.parseQueryPrimary(Parser.java:2708) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.parseQueryTerm(Parser.java:2564) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.parseQueryExpressionBody(Parser.java:2543) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.parseQueryExpressionBodyAndEndOfQuery(Parser.java:2536) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.parseQueryExpression(Parser.java:2529) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.parseQuery(Parser.java:2498) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.parsePrepared(Parser.java:627) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.parse(Parser.java:592) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.parse(Parser.java:564) ~[h2-2.2.224.jar!/:na]
    at org.h2.command.Parser.prepareCommand(Parser.java:483) ~[h2-2.2.224.jar!/:na]
    at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:639) ~[h2-2.2.224.jar!/:na]
    at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:559) ~[h2-2.2.224.jar!/:na]
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1166) ~[h2-2.2.224.jar!/:na]
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:93) ~[h2-2.2.224.jar!/:na]
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:316) ~[h2-2.2.224.jar!/:na]
    at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:327) ~[HikariCP-5.0.1.jar!/:na]
    at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) ~[HikariCP-5.0.1.jar!/:na]
    at org.eclipse.dirigible.components.api.db.DatabaseFacade.query(DatabaseFacade.java:234) ~[dirigible-components-api-database-11.0.0-SNAPSHOT.jar!/:na]

As you can see, the passed input options are: "select":"SELECT \"ID\", \"FIRST_NAME\", \"LAST_NAME\" FROM \"employees\""} where the table is in lower case.

[iliyan-velichkov]

Hi @larshp, Can we preserve the tables and columns case?

[pavelbaltiyskibix]

Hi @larshp,

Following up @iliyan-velichkov comment and the scenario provided we have the following SQL: SELECT "ID", "FIRST_NAME", "LAST_NAME" FROM "employees"

In my case I am using Snowflake. My points are:

• To use the query provided we need to take care also about quotes not only the lowercase. In this case I set it working as I am using the create statement like:

create table "employees" ( ... )

if I create the table like create table EMPLOYEES or create table employees the select statement will not work.

Exactly the same applies to column names. I hope this can be fixed.

Thank you