Problem with parameterized DB Query Reader

[l_lindenthal]

Hello. I have a problem. My parameterized DB Query Reader wont work and I dont know why.
Some technical data: I use the newest version (4.6.3)
With Oracle it works, but not with MySQL.

and this is the code I use.

select * from (
select MeasurementId, MeasureTime, Value from SensorLog
where MeasureTime BETWEEN FROM_UNIXTIME(1663237373) AND FROM_UNIXTIME(1663330640)
and DeviceId = ‘@10#3#158#91’
and MeasurementType = 7
and Value= $column1$
) timerange_raw
where substring_index(substring_index(MeasurementID, ‘|’, 4), ‘|’, -1) = ‘3’
;

I cant even excute it. I always get this error code:

WARN Parameterized DB Query Reader 3:31 Cannot retrieve an output data specification. Caused by You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
) AS tempTable_1603530827527730267 WHERE 1 = 0’ at line 9

(sometimes its line 8 without me changing the code)

I hope someone can help.

[takbb]

Hi @l_lindenthal , and welcome to the KNIME community!

What happens when you remove the semicolon at the end? One of the things KNIME does to interpret the results of the query is to wrap the whole query in a further select() statement, so if your query is

select x,y,z from some_table

KNIME will wrap it with the equivalent of :

select * from (
select x,y,z from some_table
)  AS tempTable_nnnnnnnn WHERE 1 = 0

So if you have a ; at the end of your statement, this could cause it to break.

If that’s not the issue, can you tell me what the datatype of the $column1$ column is, and also the Value on the db table?

Finally a quick tip when pasting code (e.g. sql) here on the forum is to highlight the code and then press the </> button on the edit toolbar in the forum edit window.

That way it doesn’t change apostrophes etc in the code and makes it more readable.

[ReWi]

Hi @takbb,

one question regarding your (helpful) explanation:
Why does the wrapping in KNIME (I saw this too) ending with WHERE 1=0? How does this work as 1=0 is always false?

[takbb]

Hi @ReWi

The condition WHERE 1=0 is, as you say is always going to return false. This is used here as a technique of sending a query to the database that is guaranteed to return zero rows as this allows KNIME to retrieve the table metadata (namely the columns and their data types), or test the query for errors without returning data.

I do it occasionally myself (for large intensive queries) when manually interacting with a sql database, as a means of testing that a query is syntactically correct without actually retrieving any data if it is!