15 Replies Latest reply: Jul 17, 2013 7:09 AM by baalasundarg RSS

    Configurable ODBC DSP and Oracle

    Nick Borunov

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

        • Re: Configurable ODBC DSP and Oracle
          Nick Borunov

          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?

          • Re: Configurable ODBC DSP and Oracle
            Gregory Hood

            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
                Nick Borunov

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

                • Re: Configurable ODBC DSP and Oracle

                  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 !