Example: CREATE EXISTING Proxy Table On HANA

Question: How do I create a SQL Anywhere remote access proxy table for an actual table on HANA?

Answer: The following code was tested on SQL Anywhere 16.0.0.1823, HANA 1.00.68, and the HDBODBC 1.00.70 ODBC driver.

Here's a command that lets you use SQL Anywhere 16's ISQL utility with HANA:

"%SQLANY16%\bin64\dbisql"^ -c "DRIVER=HDBODBC; HOST=breckcarterhana.vm.cld.sr:30015; USER=SYSTEM; PASSWORD=manager;"^ -hana

Note: There is nothing at all about that command line that's the same with a SQL Anywhere database:

• The DRIVER is different,
  

• the HOST is different (in this case pointing to a HANA "vanity URL" and port on Cloudshare),
  

• the USER and PASSWORD keywords are different, as are the values (the HANA equivalent of DBA/sql), and
  

• the special dbisql -hana option is different.
  

You can use a DSN instead of the DRIVER and HOST, but this is a DSN-free article.

Here's a simple table on HANA:

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

Here are the commands to create a SQL Anywhere 16 database:

"%SQLANY16%\Bin64\dbinit.exe"^ ddd16.db "%SQLANY16%\Bin64\dbspawn.exe"^ -f "%SQLANY16%\Bin64\dbeng16.exe"^ -o dbeng16_ddd16_log.txt^ ddd16.db "%SQLANY16%\Bin64\dbisql"^ -c "ENG=ddd16; DBN=ddd16; UID=dba; PWD=sql; CON=ddd16"

Here are the SQL statements to set up a proxy table on SQL Anyhere:

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; PKEY,DATA 1,2 2,2

• The USING clause is written in HANA-speak (ServerNode versus HOST) but otherwise it looks just like the DSN-less connection string in the earlier dbisql command.
  

• The CREATE EXTERNLOGIN statement points at the SYSTEM user on HANA.
  

• The CREATE EXISTING TABLE statement points at the SYSTEM.T table on HANA (note the uppercase T; more on this later).
  

Tip: If you get an error like the following, try switching from the 32-bit version of SQL Anywhere to the 64-bit version, or vice versa, or download the "other bit-ness" version of the HANA ODBC driver.

Could not execute statement. Unable to connect to server 'HANA_server': [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application SQLCODE=-656, ODBC 3 State="HY000"

Tip: If you get an error like the following, try changing the table name to upper case in the CREATE EXISTING ... AT clause; e.g., from this 'HANA_server..SYSTEM.t' to this 'HANA_server..SYSTEM.T'.

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"

You shouldn't HAVE to make that change, and by the time you read this maybe you don't. Here's the explanation:

• Identifiers in HANA are case-insensitive unless they are enclosed in "double quotes" in which case they become case sensitive.
  

• Table names in HANA are converted to upper case when they are stored in the system catalog.
  

• If you code AT 'HANA_server..SYSTEM.t' SQL Anywhere currently sends the SELECT to HANA like this: SELECT t1."PKEY" , t1."DATA" FROM SYSTEM."t" t1
  

. . . and voila! "invalid table name: Could not find table/view t"

---