INDEX keyword

[robleer]

Hi,

I'm trying to integrate an external MS SQL Server database to view some data in our Aware Application.

Connecting to the database works fine and attributes are Discovered fine. But there are a few fieldnames in the tables which causes problems.
This fields are called "index", "id" a.s.o.

The response of Aware is:
Internal error. Error reading business object. Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

I cannot change the external database fieldnames (this is not our database), so is there a way to solve this? Can I change the fieldmapping somewhere/somehow?

TIA,

Rob

[BLOMASKY]

Since you have had no answers, I figure I would just spit ball here....

1). Can you enter the name in []. i.e. [index]?

2). Can you create a calculated field in the table that references the "Index" column?

3). Can you create a view where you rename the "index" column?


Bruce

[PointsWell]

Don’t have an answer but I’m intrigued as to how MSSQL allowed columns with reserved words as their name.

[robleer]

@Pointswell: I don't know. It is a service I use to get data from our financial system (via REST) into an MS SQL Database. It is normally used to show this data in PowerBI (which works fine). But I wanted this data in Aware and bumped against this problem. The REST -> SQL service is an external service, so I cannot change things and have to use it as it is. I do not know why and how MS SQL allows this. It is no in my scope.

@BLOMASKY: not unfortunately I cannot change anything in this SQL Server database because it is not mine and an external service I use for reporting (and connecting with Aware).

[BobK]

Color me confused

I'm trying to integrate an external MS SQL Server database to view some data in our Aware Application.

Connecting to the database works fine and attributes are Discovered fine. But there are a few fieldnames in the tables which causes problems.
This fields are called "index", "id" a.s.o.

Are you trying to connect to a MS SQL Database as an external table within AwareIM?

I don't know. It is a service I use to get data from our financial system (via REST) into an MS SQL Database. It is normally used to show this data in PowerBI (which works fine). But I wanted this data in Aware and bumped against this problem. The REST -> SQL service is an external service, so I cannot change things and have to use it as it is. I do not know why and how MS SQL allows this. It is no in my scope.

Or are you calling a REST service and trying to store the response in to a MS SQL Database?

[robleer]

Hi,

Yes, I connect to a SQL Server database as an external table.

The external table has those "id" and "index" fields, but this SQL Server is not mine, so I cannot change things there.

That causes the problem.

[BobK]

I do not know about 'index', that might be a problem, but Aware should automatically map id to EXT_id. So inside Aware, you would access the id attribute as 'YourBO.EXT_id'

[robleer]

@BobK: unfortunately it does not...