on 2014 Apr 03 11:38 AM
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...
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)
User | Count |
---|---|
66 | |
11 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.