SQL Outbound Adapter - Query not returning data as expected

Question

Question

Mark Lang · Feb 9, 2018

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,
c_fastsearch_code,
c_drugfull
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,
c_fastsearch_code,
c_drugfull
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

Mike Dawson · Feb 14, 2018

Hi there,

Try LIKE 'PARA%' (All upper case)

Best MikeD

0 0

score 0 · Answer 1 · 2018-02-09T09:53:26-05:00

Saurav Gupta · Feb 9, 2018

You have NOT LIKE comparison in the second query

0 0

score 0 · Answer 2 · 2018-02-09T10:05:22-05:00

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.

score 0 · Answer 3 · 2018-02-09T12:01:31-05:00

Robert Cemper · Feb 9, 2018

Could it be you experience a timeout due to long execution time ?

0 0

score 0 · Answer 4 · 2018-02-14T06:40:30-05:00

Mark

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

docs: http://docs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?P...

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

score 0 · Answer 5 · 2018-02-14T07:32:51-05:00

A quick note from the documentation:

"%SelectMode

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,
c_fastsearch_code,
c_drugfull
FROM JAC.drug_basic
WHERE %ODBCOUT(c_drugfull) LIKE 'Para%'

score 1 · Answer 6 · 2018-03-16T06:17:46-04:00

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.