topic Re: Configurable ODBC DSP and Oracle in QlikView

Configurable ODBC DSP and Oracle

Wed, 29 Jun 2011 16:10:11 GMT

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

Re: Configurable ODBC DSP and Oracle

Wed, 06 Jul 2011 19:40:29 GMT

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?

Configurable ODBC DSP and Oracle

Wed, 10 Aug 2011 14:00:45 GMT

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

Configurable ODBC DSP and Oracle

Wed, 10 Aug 2011 14:05:41 GMT

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

Configurable ODBC DSP and Oracle

Wed, 10 Aug 2011 14:52:05 GMT

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.

Configurable ODBC DSP and Oracle

Mon, 22 Aug 2011 15:23:48 GMT

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

Re: Configurable ODBC DSP and Oracle

Mon, 29 Aug 2011 14:45:56 GMT

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.

Re: Configurable ODBC DSP and Oracle

Mon, 29 Aug 2011 15:21:03 GMT

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.

Re: Configurable ODBC DSP and Oracle

Mon, 29 Aug 2011 15:38:22 GMT

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.

Re: Configurable ODBC DSP and Oracle

Greg_Hood — Thu, 22 Sep 2011 12:42:48 GMT

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.

Re: Configurable ODBC DSP and Oracle

Thu, 22 Sep 2011 12:47:10 GMT

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

Re: Configurable ODBC DSP and Oracle

Greg_Hood — Thu, 22 Sep 2011 12:54:09 GMT

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.

Re: Configurable ODBC DSP and Oracle

Thu, 22 Sep 2011 12:56:59 GMT

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

Re: Configurable ODBC DSP and Oracle

Mon, 25 Mar 2013 18:34:15 GMT

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 !

Re: Configurable ODBC DSP and Oracle

Thu, 06 Jun 2013 19:42:48 GMT

HI Nick,

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

Re: Configurable ODBC DSP and Oracle

Wed, 17 Jul 2013 11:09:11 GMT

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