53 views

Description

When using ODBC to query to a 'string' that matches the value of a field name, it translate to an incorrect 'same as' query that could returning the incorrect rows.

 

Steps to Reproduce

  1. Create an incident with short_description set as 'number' (string).

    2. Now open iSQL and run query:
    > select number from incident where short_description='number'.

    The query does not return any result. This is caused by the way ODBC translate SQL commands into SOAP requests.

    The actual encoded query section causing the problem is 'short_descriptionSAMEASnumber' instead of 'short_description=number'.

Workaround

This issue can be resolved using the ODBC component version 1.0.14 which is native in the London release. However, once available, the ODBC 1.0.14 driver can be downloaded and installed on any instance.

As a workaround, modify the queries to not include the value of a field name, e.g.: select number from incident where short_description LIKE  'number';

 

Here is a more complex example query that fails:

// problematic
SELECT sys_dictionary.name,
       sys_documentation.name
FROM sys_dictionary a,
     sys_documentation b
WHERE a.name=b.name
  AND a.element=b.element  // <== this fails because the data contains 'field' names on the table.
  AND a.name='task'
  ;


Here is the optimised version:


// without the problem
SELECT a.name,
       b.name
FROM (SELECT name+':'+element key1, name, element FROM sys_dictionary WHERE name='task') a,
     (SELECT name+':'+element key2, name, element FROM sys_documentation WHERE name = 'task') b
WHERE a.key1 = b.key2
   AND a.name='task'
;


Related Problem: PRB1162624

Seen In

There is no data to report.

Fixed In

ODBC 1.0.14

Associated Community Threads

There is no data to report.

Article Information

Last Updated:2018-04-11 20:18:23
Published:2018-02-22