0 Replies Latest reply: Feb 3, 2012 9:37 AM by Dave Riley RSS

    Microsoft OLE DB Provider for ODBC Drivers - not same as direct ODBC

    Dave Riley

      I’ve been looking into a massive data anomaly between figures reported in a Qlikview  document and our front-end system and have discovered this was being caused by the Microsoft OLE DB Provider for ODBC Drivers.  The System DSN in this case is using an Informix driver, but if I use this ODBC connection directly, the figures returned are correct.  When testing the provider in Excel, it fails with an error, but Qlikview is not reporting a failure, instead returning figures as explained later.

       

      I understand this provider is deprecated by Microsoft, so am advising all my developers to use the ODBC connection, but wanted to ask if any users had experienced anything similar.

       

      And now the problem.  Imagine a data table as this in the backend …

       

      Item                 Estimate1                    Estimate2                    Estimate3

      A                      100                              0                                  23

      B                      150                              123                              0

      C                     0                                  0                                  0

      D                     0                                  0                                  26

      E                      12                                1                                  125

      F                      0                                  2                                  0

       

      When using the OLEDB provider, Qlikview is ignoring the zero values and instead returning the last loaded value in that field giving …

       

      Item                 Estimate1                    Estimate2                    Estimate3

      A                      100                              0                                  23

      B                      150                              123                              23

      C                     150                              123                              23

      D                     150                              123                              26

      E                      12                                1                                  125

      F                      12                                2                                  125

       

      I suspect it is because of issues with Nulls in the backend, but the worrying aspect is that Qlikview is unable to report any problem.  If the figures hadn't been so widely different, this could have gone unnoticed.

       

      I then decided to test against another ODBC connection (in this case some file based FoxPro databases) and, although have no issues like above, do see TRUE/FALSE flags handled differently – the provider returns -1(true)/0(false), whereas ODBC returns 0(false)/1(true).

       

      Anyone got any comments?

       

       

      flipside