Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Configurable ODBC DSP and Oracle

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:

configODBC.png

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

ODBCSettings.png

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"

ODBC driver.png

With configured System DSN:

ODBC DSN.png

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:

ODBC User search.png

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

1 Solution

Accepted Solutions
Not applicable
Author

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

15 Replies
Not applicable
Author

So I've made changes in the Settings:

DSPSetting.png

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?

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

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.