Edit an SQL statement and send to MySQL database

KNIME Analytics Platform
1

Hi

I am looking for a way to edit an SQL statement using variables/tables produced in a Knime workflow. I know SQL statements can be sent using the database reader node, but that does not seem to allow variables to be used instead of the statement. I would also be perfectly happy with another method, eg. using knime nodes. The script needs to run several times, loading multiple files, so it may be worth appending the script for each file.

The statement to be altered is:
(Where [FILE_DATE] is a date/time extracted from the file name, and [FILE] is the file name, both to be replaced by variables. There are other actions performed to table2, but they are static.)

DELETE FROM schema.table1 WHERE file_date=’[FILE_DATE]’;

LOAD DATA LOCAL INFILE’C:/File/Path/[FILE]'
INTO TABLE schema.table2
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n’
IGNORE 1 LINES
(col1,col2…);

UPDATE schema.table2
SET file_date=’[FILE_DATE]’;

INSERT IGNORE INTO schema.table1
SELECT *
FROM schema.table2
DELETE FROM schema.table1 WHERE true;

Thanks for your time!

2

Hi Alan, 

Have you tried putting the database reader inside a table row to variable loop start node? If not, you can use flow variables in your SQL statements by clicking on them from the Flow Variables pane in the lower left corner of the dialog.  Then on each iteration of the loop, you should get the results for the parameterized query.

Regards,

Aaron 

3

I had sort of tried, but something complicated that didn’t work.
And it helped when I remembered I needed to run the loop first.

Thanks a lot, that was a huge help.