· Feb 9, 2018

SQL Outbound Adapter - Query not returning data as expected

Hi all,

I have an operation which uses the SQL outbound adapter to query a data source set up using a system DSN  on the server which uses the Intersystems ODBC35 driver (this is a cache db hosted elsewhere).

Whenever Ensemble runs the following query:

SELECT lnkdid,
FROM JAC.drug_basic
WHERE c_drugfull LIKE 'Para%'

No data is returned, however running the same query via SQL Server Management Studio, all expected rows are returned.

Also if I run  the following via Ensemble:

SELECT lnkdid,
FROM JAC.drug_basic
WHERE c_drugfull NOT LIKE 'Para%'

This returns all the rows as expected

It's probably obvious to someone what the issue is, any help much appreciated.

Discussion (7)1
Log in or sign up to continue

Yes, what I was meaning why does the query using LIKE not work (i.e. it doesn't return any rows, even though it should, and I can prove this by querying via SSMS).  I ran the second query from Ensemble just to see if there was something fundamentally wrong with my query, this would prove there is not, as the query using NOT LIKE returns the rows I would expect it to.

What I am questioning is why the query which just uses LIKE does not return any rows when it should from Ensemble.


Do you have an index on c_drugfull?

The most likely difference between the 2 queries above would be the first using that index and the second not using the index.  If the index has bad data that would explain the issue.

Of course there is a good chance your test from SQL Server would also use that index so I could be wrong.

Can you run the problem query directly on the Cache server in the System Management Portal?  If you get wrong results there you can look at the Show Plan to see if an index is involved.  If Yes you ran run $SYSTEM.SQL.%ValidateIndices() to see if there is a problem


If that does not explain what is going on please contact InterSystems support so we can dig in further.

A quick note from the documentation:


The LIKE predicate does not use the current %SelectMode setting. A pattern should be specified in Logical format, regardless of the %SelectMode setting. Attempting to specify a pattern in ODBC format or Display format commonly results in no data matches or unintended data matches.

You can use the %EXTERNAL or %ODBCOUT format-transform functions to transform the scalar-expression field that the predicate operates upon. This allows you to specify the pattern in Display format or ODBC format. However, using a format-transform function prevents the use of the index for the field, and can thus have a significant performance impact."

I'm not sure if this affects you but can you try:

SELECT lnkdid,
FROM JAC.drug_basic
WHERE %ODBCOUT(c_drugfull) LIKE 'Para%'

Apologies, as I have meant to come back and update.

So it was really pretty simple, and Mike Dawson was spot on "Try LIKE 'PARA%' (All upper case)".

This was indeed the issue,  what I hadn't realised was that the LIKE would be  case sensitive.  Feeling more than a little foolish I didn't spot this myself, but you know how it is sometimes when you are  blind to the obvious when you are in the zone.

Thanks all for your input.