Configurable ODBC DSP and Oracle

[#1]

I'm trying to use Configurable ODBC, but it's no so configurable as it sounds

What I'm doing:

1. I've created user QV in Oracle DB with two table: USERS and GROUPS according to documentations for Configurable ODBC:

36.2 Configurable ODBC
The ODBC database has to have two table, or two views, one for entities and one for groups.
The entity table must have the four following fields: entityid, name, descr and email. The fields name,
descr and email must be strings. Entityid must be a unique identifier (suitable for primary key).
The groups table must contain two fields: groupid and memberid. Together these two fields create a
unique identifier.

2. I've created Configurable ODBC:

2.a. I've created Setting for the ODBC:

So it means that I have Oracle instance with service name ORCL in tnsnames.ora file. And I have Oracle ODBC Driver "Oracle in OraDb11g_home1"

With configured System DSN:

3. When I click Apply the Configurable ODBC activates without any error in DSC log file.

4. But when I try to get users list from the DSC:

I'm getting the next error in DSC log file:

6/29/2011 11:07:46.3066836    Information    WebService call. FunctionName=GetResources
6/29/2011 11:07:46.3096839    Information    WebService call. FunctionName=GetResources Took=3ms  Result=<GetResources><GetResourcesResult><DSResource><dscresource><id>5b4e4754-8183-477e-b3ca-273fd8a1c69c</id><name>qv (ODBC://ORCL)</name><type>odbc</type><path>ODBC://ORCL</path><uid>7tcglu8jlHw=</uid><pwd>7tcglu8jlHw=</pwd><enabled>True</enabled><StringDictionarySerializer><Entry Key="ServiceTimeoutInSeconds" Value="30" /><Entry Key="DirectoryLabel" Value="qv" /><Entry Key="EntityName" Value="Users" /><Entry Key="EntityTableDbName" Value="USERS" /><Entry Key="GroupsTableDbName" Value="GROUPS" /><Entry Key="DataSourceName" Value="Oracle in OraDb11g_home1" /><Entry Key="ConnDbName" Value="ORCL" /><Entry Key="OverrideConnectionString" Value="" /><Entry Key="CacheExpiryInMinutes" Value="15" /></StringDictionarySerializer></dscresource></DSResource><DSResource><dscresource><id>3d577957-e533-40ea-b9cd-0e8f08bd1573</id><name>analytics1 (local://analytics1)</name><type>local</type><path>local://analytics1</path><uid>GjGZxIr3JvTpjf3521ZQnQ==</uid><pwd>o0rI2tKRnn+iVBZ6gjkrVQ==</pwd><enabled>True</enabled><StringDictionarySerializer><Entry Key="CacheExpiryInMinutes" Value="15" /></StringDictionarySerializer></dscresource></DSResource><DSResource><dscresource><id>9ae93e76-8336-4062-af65-6a05a0bd46df</id><name>ando (WinNT://ANDO)</name><type>nt</type><path>WinNT://ANDO</path><uid>DxdCGMWfOwU=</uid><pwd>DxdCGMWfOwU=</pwd><enabled>True</enabled><StringDictionarySerializer><Entry Key="CacheExpiryInMinutes" Value="15" /></StringDictionarySerializer></dscresource></DSResource><DSResource><dscresource><id>3fd55941-f51d-4032-ad07-49cc92a78a40</id><name>Custom (Custom)</name><type>custom</type><path>Custom</path><uid>DxdCGMWfOwU=</uid><pwd>DxdCGMWfOwU=</pwd><enabled>True</enabled><StringDictionarySerializer><Entry Key="DomainName" Value="Custom" /><Entry Key="CustomDirectoryPort" Value="4735" /></StringDictionarySerializer></dscresource></DSResource></GetResourcesResult></GetResources>
6/29/2011 11:07:46.3266856    Information    WebService call. FunctionName=CheckNames
6/29/2011 11:07:46.3266856    Information    Start web service call CheckNames
6/29/2011 11:07:46.3306860    Error    (Database.DatabaseProvider) DB-search wasn't successful:
System.Data.Odbc.OdbcException: ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904: "E"."USERS": invalid identifier
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
   at Database.DatabaseProvider.DoSearch(String[] pattern, eSearchType[] searchTypes, String otherAttribute)
6/29/2011 11:07:46.3306860    Error    (Database.DatabaseProvider) Exception 'System.Data.Odbc.OdbcException: ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904: "E"."USERS": invalid identifier
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
   at Database.DatabaseProvider.DoSearch(String[] pattern, eSearchType[] searchTypes, String otherAttribute)
   at Database.AbstractDSP.Search(String[] pattern, eSearchType type, String otherattribute)' when searching for 'Database.AbstractDSP+QueryBase'
6/29/2011 11:07:46.3306860    Warning    (Database.DatabaseProvider) SearchCache lock inconsistency found (reader: False writer: True)
6/29/2011 11:07:46.3316861    Error    Exception checking names in provider qv(name: Configurable ODBC, type: ODBC): ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904: "E"."USERS": invalid identifier
6/29/2011 11:07:46.3316861    Information    WebService call. FunctionName=CheckNames Took=5ms  Result=<CheckNames><CheckNamesResult /></CheckNames>
6/29/2011 11:07:46.3346864    Information    WebService call. FunctionName=GetAllResources
6/29/2011 11:07:46.3366866    Information    WebService call. FunctionName=GetAllResources Took=2ms  Result=<GetAllResources><GetAllResourcesResult><DSResource><dscresource><id>5b4e4754-8183-477e-b3ca-273fd8a1c69c</id><name>qv (ODBC://ORCL)</name><type>odbc</type><path>ODBC://ORCL</path><uid>7tcglu8jlHw=</uid><pwd>7tcglu8jlHw=</pwd><enabled>True</enabled><StringDictionarySerializer><Entry Key="ServiceTimeoutInSeconds" Value="30" /><Entry Key="DirectoryLabel" Value="qv" /><Entry Key="EntityName" Value="Users" /><Entry Key="EntityTableDbName" Value="USERS" /><Entry Key="GroupsTableDbName" Value="GROUPS" /><Entry Key="DataSourceName" Value="Oracle in OraDb11g_home1" /><Entry Key="ConnDbName" Value="ORCL" /><Entry Key="OverrideConnectionString" Value="" /><Entry Key="CacheExpiryInMinutes" Value="15" /></StringDictionarySerializer></dscresource></DSResource><DSResource><dscresource><id>3d577957-e533-40ea-b9cd-0e8f08bd1573</id><name>analytics1 (local://analytics1)</name><type>local</type><path>local://analytics1</path><uid>GjGZxIr3JvTpjf3521ZQnQ==</uid><pwd>o0rI2tKRnn+iVBZ6gjkrVQ==</pwd><enabled>True</enabled><StringDictionarySerializer><Entry Key="CacheExpiryInMinutes" Value="15" /></StringDictionarySerializer></dscresource></DSResource><DSResource><dscresource><id>7f258584-f427-41ec-8078-bc9f067832eb</id><name> (LDAP://ando.local)</name><type>ad</type><path>LDAP://ando.local</path><uid>DxdCGMWfOwU=</uid><pwd>DxdCGMWfOwU=</pwd><enabled>True</enabled><errstatus>An operations error occurred.
</errstatus><StringDictionarySerializer><Entry Key="CacheExpiryInMinutes" Value="60" /><Entry Key="ServiceTimeoutInSeconds" Value="30" /></StringDictionarySerializer></dscresource></DSResource><DSResource><dscresource><id>9ae93e76-8336-4062-af65-6a05a0bd46df</id><name>ando (WinNT://ANDO)</name><type>nt</type><path>WinNT://ANDO</path><uid>DxdCGMWfOwU=</uid><pwd>DxdCGMWfOwU=</pwd><enabled>True</enabled><StringDictionarySerializer><Entry Key="CacheExpiryInMinutes" Value="15" /></StringDictionarySerializer></dscresource></DSResource><DSResource><dscresource><id>3fd55941-f51d-4032-ad07-49cc92a78a40</id><name>Custom (Custom)</name><type>custom</type><path>Custom</path><uid>DxdCGMWfOwU=</uid><pwd>DxdCGMWfOwU=</pwd><enabled>True</enabled><StringDictionarySerializer><Entry Key="DomainName" Value="Custom" /><Entry Key="CustomDirectoryPort" Value="4735" /></StringDictionarySerializer></dscresource></DSResource></GetAllResourcesResult></GetAllResources>

So, What am I doing wrong??????

Help please!!!

[#2 · Accepted solution]

Here is what I've got from support team:

Dear Nick,

Your recentsupport case as described below has been classified as a WAD (Works AsDesigned) by our Issue Analysis team - ID 40323.

R&D has ruled Working as Designed, this is a third party bug. The OracleODBC driver returns a value to the odbc class in .Net Framework that it can'thandle. A different ODBC driver might possibly fix the problem.

Please accept our sincere apologies for any inconvenience this may cause.
Michael TerenziQlikView Support

[Greg_Hood]

Hi Nick, WAD in this case means our code is working correctly.  However it is (or was) a driver problem with the Oracle driver and the bug was closed as a 3rd party bug.  We only found out today that Oracle had corrected the issue so we are trying to let as many people know as possible.   I hope the link will help you.

[#4]

I got it. I've added the support reply for whole history stack.

[#5]

Dear Nick,

Have you found a solution to your problem?

I am trying to reach the two Oracle tables (entity and groups) with an ODBC (11.02.00.03) driver without success.

I also have the same issue : "Arithmetic operation resulted to an overflow"

Please help !

[#6]

HI Nick,

Whet is the configuration ODBC to SQL, I have a task fail when I play my task.

[#7]

as Mentioned in QV reference i have configured the ODBC to SQL sqrver. since the connection is estableshed by two tables Entity (column: EntityID,Name,Descr,email)and Group(column: GroupID,MemberID)

which is considered as User Name and Password. I ve mentioned the Entity as name