Use ANSI-SQL double-quoted identifiers instead of backticks

building semantic layer
1

Hi,

We are currently testing a custom database server with Business Objects 12 and are encountering a problem.

For historical reasons the system creates column names containing the originating table when processing join queries, so select * from table1 inner join table2 would result in columns
“table1.field1”, “table2.field2”

My issue is that the Business Objects universe designer sees the full stop/period character within these table names and correctly identifies that they need to be quoted, but instead of an ANSI-SQL double quote character, it is applying a backtick character on subsequent queries.

select derived_table.table1.field1 from (select * from table1 inner join table2) derived_table

We support ANSI-SQL and do not recognise the backtick as a valid identifier quote character.

We are working to remove the period characters but as a shorter term solution I’d appreciate it if anyone is aware of a way to configure the Business Objects system to use double-quotes as field identification characters rather than backticks. I have examined the Universe parameters reference and can not see any way of configuring this setting.

Thanks

Adam

adampknight (BOB member since 2012-10-31)

2

Have you set the ANSI 92 option to Yes in the universe parameters?

3

Mark,

Thanks for the reply.

Yes I have created a new universe and set ANSI92 parameter to yes. As far as I can see from the documentation this only affects the use of ANSI JOIN syntaxes and not identifier quoting characters.

Cheers,

Adam.
ANSI_params.png

adampknight (BOB member since 2012-10-31)

4

Adam,

What DBMS is being used on the database server?

jwaterbury :us: (BOB member since 2007-09-21)

5

It is our own database product.

We have an SQL92 compliant SQL parsing engine and ODBC drivers.

I am connecting via a generic ODBC connection in the universe designer using a DSN to access our ODBC driver.

Thanks

Adam

adampknight (BOB member since 2012-10-31)

6

Does the ODBC connection setup have the option ‘Use ANSI quoted identifier’ ? I know some do.

::Rich:H :uk: (BOB member since 2002-09-11)

7

We don’t have that option, but we do set

SQL_IDENTIFIER_QUOTE_CHAR to “”"

in the ODBC connection properties (SQLGetInfo) for all connections which should tell any connecting applications to use the double quote for identifiers. BO seems to be ignoring this parameter and using backticks and I can find no way to override this.

Thanks

Adam

adampknight (BOB member since 2012-10-31)