Can I read in an external sql file and use this in a query

Hello,

I would like to use an external text file as the source for a database query to be executed.

Background:

I would like to develop, test, etc. my sql scripts in a dedicated tool (eg. Toad) and then save that SQL file to a certain disk location so it can be used in a Knime workflow. Reason that I don't want to copy/paste it into the workflow is that some customer selections are made in this sql script and this script is also used by other people/scripts. 

I am afraid to have multiple versions of the customer selection script flying around...

Regards,

Geoffrey

If it's stored in a text file you could read the content into a cell (I think Vernalis just released a node that can do this without the fudging with a Java snippet).

 

You can probably then convert the cell to a flow variable and use the flow variable as the source of the SQL for the node. 

 

Cheers

 

Sam

Hi Geoffrey, 

This is definitely doable, just write your SQL to a plaintext file, and read it in with a regular filereader.  You can then convert this string to a flow variable, and use that to overwrite your "statement" node setting in the DB nodes. 

I've attached a simple example.  Note that the metanode which precedes the file reader and DB reader nodes is just to create the files (DB and Query) needed for the example. 

Let us know if this works for you.

 

 

Sam,

Thanks for the plug - yes we did!  If your sql file contains linebreaks, then I would say that this would be the way to go with it - if not, then the file reader would also work.  (I should clarify too that it will read the file no matter what the extension, as long as it is text!)

Steve

Aaron - thanks for the example, it works - but unfortunately my sql scripts are over multiple lines and can not be encapsulated between " - that's not the way we try to write our sql statements :(

Steve - I downloaded the Vernalis extension through Knime, but it didn't contain the text based load/save nodes. Did I do something wrong?

UPDATE: No I did not do something wrong - just forgot to include the nightly build repository :) Seems to be working on my 'home' pc... gonna do some real testing tomorrow in the office with a real database connection :)

Thanks

Geoffrey,

Thanks.  The extra nodes should have gone live on the stable release build on monday night/tuesday morning.  When did you find they were not there?  If it was after this, I will need to look into why they have not made it across to the build, as this is the first time we have updated the stable builds.

Steve

Alternatively, a line reader followed by "GroupBy / concatenate" works for me in these cases.

-- E

Sorry,

completely forgot to give an update.... I do have it working now.. And yes the version I downloaded today had the file reader included, so not sure what was wrong last time. (I did it on a Win XP machine today and on my Mac last time, but not sure if that would make a difference).

 

So I set it up as a Metanode containing 

String Input -> Variable to TableRow -> Load text base files -> Tablerow to Variable 

String input
Used to do right click in the main flow and type in the sql path and filename

The other nodes speak for themselves I assume.

Main flow

Metanode -> Database reader

The output of my Metanode is used as a variable input on my Database Reader node that feeds the statement attribute.

 

Dear Sir/ Madam,

Thank you for your useful explanation in this website. I would like to know if there is any SWOT analysis for this tool, KNIME, or any other clarified vision about it?

Thank you in advance,

Tahsin