Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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
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.
I got it. I've added the support reply for whole history stack.
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 !
HI Nick,
Whet is the configuration ODBC to SQL, I have a task fail when I play my task.
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