Solved: Configurable ODBC DSP and Oracle - Qlik Community

Report Inappropriate Content · ‎2011-06-29

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!!!

Report Inappropriate Content · ‎2011-09-22

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

View solution in original post

Report Inappropriate Content · ‎2011-07-06

So I've made changes in the Settings:

And I'm getting this error now:

7/6/2011 14:36:03.7833586    Information    WebService call. FunctionName=GetResources
7/6/2011 14:36:03.7853588    Information    WebService call. FunctionName=GetResources Took=3ms  Result=<GetResources><GetResourcesResult><DSResource><dscresource><id>22504544-5be9-43ce-a3a1-2bbaa39e8785</id><name>DB DSP (ODBC://localhost)</name><type>odbc</type><path>ODBC://localhost</path><uid>DxdCGMWfOwU=</uid><pwd>DxdCGMWfOwU=</pwd><enabled>True</enabled><StringDictionarySerializer><Entry Key="ServiceTimeoutInSeconds" Value="30" /><Entry Key="DirectoryLabel" Value="DB DSP" /><Entry Key="EntityName" Value="NAME" /><Entry Key="EntityTableDbName" Value="USERS" /><Entry Key="GroupsTableDbName" Value="GROUPS" /><Entry Key="DataSourceName" Value="Oracle in OraDb11g_home1" /><Entry Key="ConnDbName" Value="" /><Entry Key="OverrideConnectionString" Value="Driver={Oracle in OraDb11g_home1};Dbq=ORCL;Uid=qv;password=REMOVED;" /><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>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>
7/6/2011 14:36:03.7913594    Information    WebService call. FunctionName=CheckNames
7/6/2011 14:36:03.7913594    Information    Start web service call CheckNames
7/6/2011 14:36:03.7943597    Error    (Database.DatabaseProvider) DB-search wasn't successful:
System.OverflowException: Arithmetic operation resulted in an overflow.
   at System.Data.Odbc.OdbcStatementHandle.RowCount(SQLLEN& rowCount)
   at System.Data.Odbc.OdbcDataReader.GetRowCount()
   at System.Data.Odbc.OdbcDataReader.FirstResult()
   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)
7/6/2011 14:36:03.7943597    Error    (Database.DatabaseProvider) Exception 'System.OverflowException: Arithmetic operation resulted in an overflow.
   at System.Data.Odbc.OdbcStatementHandle.RowCount(SQLLEN& rowCount)
   at System.Data.Odbc.OdbcDataReader.GetRowCount()
   at System.Data.Odbc.OdbcDataReader.FirstResult()
   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'
7/6/2011 14:36:03.7943597    Warning    (Database.DatabaseProvider) SearchCache lock inconsistency found (reader: False writer: True)
7/6/2011 14:36:03.7943597    Error    Exception checking names in provider DB DSP(name: Configurable ODBC, type: ODBC): Arithmetic operation resulted in an overflow.
7/6/2011 14:36:03.7963599    Information    WebService call. FunctionName=CheckNames Took=6ms  Result=<CheckNames><CheckNamesResult /></CheckNames>

What is this "Arithmetic operation resulted in an overflow."?! It looks like the server can see my database and tables but cannot count the records (I have just one in each table).

Is there any idea?

Report Inappropriate Content · ‎2011-08-10

Hello Nick,

Have you found a solution to this issue, because i've the same probleme with PostgreSQL

Everything looks good, i'v any error in DSC log but user's lookup doesn't works.

Cya

Report Inappropriate Content · ‎2011-08-10

Hi,

I raised this as an official case with QlikView and they inform me it is a known issue with QV10 - I am on SR2 where it is an issue and they have said it is also not fixed in SR3. There is as yet no set date for a fix.

I am using Oracle 10g.

I will update if I hear anything.

Virgil

Report Inappropriate Content · ‎2011-08-10

I'd contacted the support team. They didn't find a workout solution for this and decided to open a ticket to fix it in next release. Hopefully it will be fixed in version 11.

Report Inappropriate Content · ‎2011-08-22

Thx for this thread!

Same Problem here.

I also added a case at support!

We are running Oracle 11 ODBC Driver here; so it doesn't look like a driver issue to me..

Report Inappropriate Content · ‎2011-08-29

I'm experiencing the issue with Access. I have one access database that works, and another that doesn't. If anyone else has any ideas that would be great.

jg

Report Inappropriate Content · ‎2011-08-29

I had the same situation. I'd configured ODBC DSC for MS SQL without any problem. But it wasn't work for Oracle. That's why we have this post.

If you have some issue you need to be more detailed. After that it will be clear , we do have issue with configuration or with QV.

Report Inappropriate Content · ‎2011-08-29

Sql is working fine. The Access database I created had the groups table configured where the memberid and groupid are set to type "text". I changed them to type "number" and access is working now. From SQL server, I have the memberid and groupid as varchar and that appears to work as well.

Good luck with making it work with Oracle.

Greg_Hood · ‎2011-09-22

Hi all,

I understand that Oracle have addressed this issue in their latest ODBC driver. Please see 11.2.0.2.0 driver http://www.oracle.com/technetwork/topics/winx64soft-089540.html.

Thank you to Virgil for this information.