2117215 - Removing quotes from Crystal Reports query for a Postgres Database in a .NET Application

SAP Knowledge Base Article - Public

2117215 - Removing quotes from Crystal Reports query for a Postgres Database in a .NET Application

Symptom

I am using a report template (this template was created with Oracle database) to generate report for PostgreSQL database.

I use RowsetController.GetSQLStatement() function to get the query from the report template, in order to print the query in a trace file.

The query I get looks like this:

SELECT "TABLE_NAME"."COLUMN_1" FROM "TABLE_NAME" "TABLE_NAME" WHERE ("TABLE_NAME"."COLUMN_1">={ts '1899-12-30 00:00:00'} AND "TABLE_NAME"."COLUMN_1"<{ts '2014-09-27 00:00:00'})

But in PostgreSQL database, table and column names are in small letters.i.e. table_name column_1

After removing quotes the query would look like this SELECT TABLE_NAME.COLUMN_1 FROM TABLE_NAME TABLE_NAME WHERE (TABLE_NAME.COLUMN_1>={ts '1899-12-30 00:00:00'} AND TABLE_NAME.COLUMN_1<{ts '2014-09-27 00:00:00'})

 

Environment

Crystal Reports for Visual Studio ( version 13 )

Postgres version 9.3

Reproducing the Issue

Need PostgreSQL database. Here table name and field names are in small characters. Need a report template designed with Oracle database, where table name and filed names are in captial character

Resolution

To enable Crystal Reports and CR for .NET to use un-quoted SQL add this registry key:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\Crystal Reports\Database\QueryBuilder\NameBuilder]

"NoQuotes"=" ODBC3PostgreSQL"

[HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Crystal Reports for .NET Framework 4.0\Crystal Reports\Database\QueryBuilder\NameBuilder]

"NoQuotes"=" ODBC3PostgreSQL"

It is a String key.

PLEASE back up your registry before attempting to make any changes

Also note: this will work for any database client - add the client dll name without the dll extension.

Keywords

Removing quotes, Crystal Reports query, Postgres, .NET, Crystal Reports developer for Visual Studio .NET , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem

Product

SAP Crystal Reports, developer version for Microsoft Visual Studio