DSXchange

I am attempting to run column analysis on SAP HANA.
We have:
- connected to it via ODBC through the HANA driver
- and have successfully imported and shared metadata through IMAM
- created an IA project and associated the data

When it comes to the column analysis, some of the tables were successfully profiled, but some had errors.
Upon investigation, I found that the SQL that IA generates has issues when slashes are contained within the column names.

From SystemOut.log:
[4/12/18 12:54:53:745 AEST] 00000085 SorcererServi I CDIIA0001I: PXBridgeHelper.java:588 select statement to be executed as part of this PX Job is: select
DIMID,
/B28/S_A9DPSV0 as IA_ALIAS_1
from SAPPB1."/B28/DA9INS7X1"
...
Job 634: BaseProfile1_1523501691188_d70c6594_80cb2b5c_0ab65du6k_mtudqth_jl6q9q_189s5ka4ailo4doomrg0m failed, please verify that the job is submitted correctly. DS job Number is RT_SC4157.
Detailed Log:
Event 1: pxbridge(0): ODBC function "SQLPrepare" reported: SQLSTATE = 42000: Native Error Code = 257: Msg = [SAP AG][LIBODBCHDB SO][HDBODBC] Syntax error or access violation;257 sql syntax error: incorrect syntax near "/": line 3 col 1 (at pos 17) (CC_OdbcDBStatement::dbsPrepare, file CC_OdbcDBStatement.cpp, line 362)

You can see that the table name is quoted for the slashes, but the column name is not.

Has anyone come across this before?

TIA.

I haven't seen this, but I haven't needed to. In your position I'd ask IBM support whether this is a known issue and, if so, whether a patch exists for it.

Hey Ray,

I raised a PMR but they just came back with "SAP HANA is not supported".
This isn't just a HANA problem. I've seen this sort of thing in SQL Server in the past, and SQL Server is obviously supported.

The silly thing is that the code that generates the SQL recognises that there is something non-standard about the name, because it tries to create an alias for the field. It just doesn't quote the field so the SQL is still no good.

I was hoping there's some setting or environment variable living in the bowels of IIS that would cause it to quote the column names the same way it is currently quoting table names.
I don't suppose you know of one I could try out?

Cheers,
Stuart.

Sounds like you may need to fall back on the workaround of defining a view over the table, one without those pesky characters in the column names.

Hi Craig,

Yeah, we've resigned ourselves to that fact too.

We were hoping to avoid that, as:
- we don't own the database so only have read-only access to it.
- the issue affects around 14k of the 50k tables in the database. 8-(

I'd better get started then!

PS: The thing that annoys me is that if I recreate the scenario in Oracle, IA adds the quotes around column names and it works just fine...

Idle thought - can you create a Virtual Column in IA that refers somehow to the column with the "/" in its name?

Another Idle Thought and Pet Peeve Alert. Anyone who designs databases with special characters like that in object names should be severely... disciplined.

Had to go with the views, unfortunately.
HANA is not on Information Server's list of ODBC supported databases.