It's hard (impossible?) to find clear and concise documentation of case sensitivity in HANA, but other companies (e.g., SAS) have filled in the gap...


Naming Conventions for SAP HANA

Names are not case sensitive. For example, CUSTOMER and Customer are the same, but object names are converted to uppercase when they are stored in the SAP HANA database. However, if you enclose a name in quotation marks, it is case sensitive.


What that means is, FROM t in HANA is the same as FROM T but FROM "t" is not (go figure!).

The remote server (proxy table) CLASS 'HANAODBC' IN SQL Anywhere 16.0.0.1823 gets it wrong when using HANA 1.00.68.384084 via the HDBODBC 1.00.70.58439 driver.

In particular, if the CREATE EXISTING TABLE AT clause uses lower case for the table name (e.g., AT 'HANA_server..SYSTEM.t') the FROM clause passed to HANA looks like 'FROM SYSTEM."t"' which results in a VERY MISLEADING error "Could not find table/view t".

It should pass 'FROM SYSTEM."T"' to HANA.

The workaround is to use upper case in the AT clause: AT 'HANA_server..SYSTEM.T'

Some may be tempted to say "User error!" but it is not. The table name in the AT clause is not "double quoted" so it should not be regarded as case sensitive. If CLASS 'HDBODBC' wants to "quote" everything, it should force the table name to upper case just like it does with the column names; see the following statement below: SELECT t1."PKEY" , t1."DATA" FROM SYSTEM."t" t1

---------------------------------------------------------------------
-- On HANA

SELECT VERSION FROM M_DATABASE;

VERSION
'1.00.68.384084'

CREATE TABLE t ( 
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL );

INSERT INTO t VALUES ( 1, 2 );
INSERT INTO t VALUES ( 2, 2 );
COMMIT;

SELECT * FROM SYSTEM.t ORDER BY pkey;

PKEY,DATA
1,2
2,2

SELECT * FROM "M_TABLES" WHERE SCHEMA_NAME = 'SYSTEM';

SCHEMA_NAME,TABLE_NAME,RECORD_COUNT,TABLE_SIZE,IS_COLUMN_TABLE,TABLE_TYPE,IS_PARTITIONED,IS_REPLICATED
'SYSTEM','T',2,16384,'FALSE','ROW','FALSE','FALSE'

---------------------------------------------------------------------
-- On SQL Anywhere 16

SELECT @@VERSION;

@@VERSION
'16.0.0.1823'

SET OPTION PUBLIC.cis_option = '7';

CREATE SERVER HANA_server CLASS 'HANAODBC' 
   USING 'DRIVER=HDBODBC; ServerNode=breckcarterhana.vm.cld.sr:30015;';

CREATE EXTERNLOGIN DBA 
   TO HANA_server 
   REMOTE LOGIN "SYSTEM" IDENTIFIED BY 'manager';

CREATE EXISTING TABLE proxy_t 
   AT 'HANA_server..SYSTEM.t';

SELECT * FROM proxy_t ORDER BY proxy_t.pkey;

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Server 'HANA_server': [SAP AG][LIBODBCHDB DLL][HDBODBC] Base table or
view not found;259 invalid table name:  Could not find table/view t in
schema SYSTEM: line 1 col 42 (at pos 41)
SQLCODE=-660, ODBC 3 State="HY000"

I. 04/03 09:43:55. Now accepting requests
I. 04/03 09:45:17. Connect to 'HANA_server' class 'hanaodbc' using 'DRIVER=HDBODBC; ServerNode=breckcarterhana.vm.cld.sr:30015;'
I. 04/03 09:45:19. Connected to 'libodbcHDB.dll' version 'SAP HDB 1.00 (2013-11-29)' odbc version '03.51'
I. 04/03 09:45:19. Server name = 'breckcarterhana.vm.cld.sr:30015'
I. 04/03 09:45:19. DBMS name = 'HDB'
I. 04/03 09:45:19. DBMS version = '1.00.68.384084'
I. 04/03 09:45:19. ODBC conformance = level 2
I. 04/03 09:45:19. ODBC SQL conformance = Extended grammar
I. 04/03 09:45:19. SQL identifier case = Upper case
I. 04/03 09:45:19. Maximum connections = 8
I. 04/03 09:45:19. Maximum statements = 32767
I. 04/03 09:45:19. SQLGetData extensions = any column, any row, any order 
I. 04/03 09:45:19. Search pattern escape = '\'
I. 04/03 09:45:19. Concatenation null behavior = SQL_CB_NULL
I. 04/03 09:45:19. Cursor commit behavior = SQL_CC_PRESERVE
I. 04/03 09:45:19. Cursor rollback behavior = SQL_CR_CLOSE
I. 04/03 09:45:19. Cursor types = read only 
I. 04/03 09:45:19. Read only datasource = 'N'
I. 04/03 09:45:19. Correlation names are supported
I. 04/03 09:45:19. Transactions are supported
I. 04/03 09:45:19. Default isolation = read committed 
I. 04/03 09:45:19. Multiple result sets = 'Y'
I. 04/03 09:45:19. Multiple transactions = 'Y'
I. 04/03 09:45:19. Isolation options = repeatable read, read committed, 
I. 04/03 09:45:19. Outer join capabilities = 
I. 04/03 09:45:19. Maximum owner name length = 127
I. 04/03 09:45:19. Maximum qualifier name length = 0
I. 04/03 09:45:19. Maximum table name length = 127
I. 04/03 09:45:19. Executing SQLTables(NULL, SYSTEM, T)
I. 04/03 09:45:20. Found table 'T', owned by 'SYSTEM', qualified by ''
I. 04/03 09:45:20. Executing SQLColumns(NULL, SYSTEM, T)
I. 04/03 09:45:21. Executing SQLStatistics(NULL, SYSTEM, T)
I. 04/03 09:45:21. CREATE EXISTING TABLE "proxy_t" ( "PKEY" int not null , "DATA" int not null , primary key("PKEY") ) AT 'HANA_server..SYSTEM.t'
 LOCAL ONLY
I. 04/03 09:45:21. Executing SQLStatistics(NULL, SYSTEM, T)
I. 04/03 09:45:31. 
The query is being processed in PARTIAL PASSTHRU mode.
The capability check of 22 failed
I. 04/03 09:45:31. The Original Statement is 
    select proxy_t.PKEY,proxy_t.DATA from proxy_t order by proxy_t.pkey asc
I. 04/03 09:45:31. The Virtual Statement is 
    select vt_1.expr_1,vt_1.expr_2 from vt_1 order by.expr_1 asc
I. 04/03 09:45:31. The Remote Statement for vt_1 is 
    select proxy_t.PKEY,proxy_t.DATA from proxy_t
I. 04/03 09:45:31. Execute (HANA_server): 
SELECT t1."PKEY" , t1."DATA" FROM SYSTEM."t" t1  
I. 04/03 09:45:32. ODBC error (42S02). [SAP AG][LIBODBCHDB DLL][HDBODBC] Base table or view not found;259 invalid table name:  Could not find table/view t in schema SYSTEM: line 1 col 42 (at pos 41)

asked 03 Apr '14, 10:38

Breck%20Carter's gravatar image

Breck Carter
25.6k427586844
accept rate: 20%

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×102

question asked: 03 Apr '14, 10:38

question was seen: 4,569 times

last updated: 03 Apr '14, 10:38