Setting a Database Reader SQL statement in batch mode over the command line [SOLVED]

Hello,

New KNIME user and RapidMiner/RapidServer convert here.  I have tried for hours to set -workflow.variable=name,"SQL statement",String over the command line, but even when I get the workflow to run succesfully over the command line, I do not observe changes to the value of the results that reflect changes I make to the -workflow.variable SQL statement's WHERE clause.

Does anyone have some specific advice or a test case I can try to follow?  In addition to the Database Reader node, I have also played extensively with the nodes SQL Inject, Database Connector, String Input (and more) without luck.  I have googled and forum-spelunkd and recieved too many error messages to relate everything I have tried here, but let me assure you this plea for help is not for lack of effort. 

Any help is, of course, very appreciated, and I promise to pay it forward to the KNIME community someday.

-Brian Muchmore

 

Hi Brian, 

Are you correctly overriding the node settings in your database reader?  You will need to construct the whole SQL statement as a flow variable and then overide the "statement" setting of your database reader.  

Yeah, I believe I am correctly setting it up.  Do you see anything obviously wrong with my command?

"C:\Program Files\KNIME_2.10.0\knime.exe" -consoleLog -noexit -nosplash -application org.knime.product.KNIME_BATCH_APPLICATION -workflowDir="C:\Users\Administrator\knime-workspace\LabResults" -workflow.variable=SQL,"\"SELECT TestName, DatePerformed, VisitID, MedRecNo, TestResultValue, TestResultUnit FROM CDX_BE_LabResult WHERE SiteID='CFC' AND MedRecNo='15900'\"",String

It says it executes correctly, but as I said before, changing the value of "SiteID" and/or "MedRecNo" in the command does not change a result in the corresponding values (the values remain the default values).

Thanks for your time and response.

-Brian Muchmore

Did you assign the flow variable to the SQL query setting in the node's dialog ("Flow Variables" tab)?

Yes:  I am 90% I know exactly how to do it, but I am obviously making some kind of mistake somewhere.

If there is nothing obviously wrong with my command then I must be making some small mistake that people remotely would be unlikely to guess.  However, if anyone else has suggestions I am happy to hear them.

-Brian

 

Hi Brian, 

Would you mind posting a screen shot of the flow variables tab of the configuration dialog of your database reader?  

Cheers,

Aaron

 

 

Of course not, thanks so much for the help.

I have attached a few screen shots.

So, the workflow says it executes succesfully, but I can't find any proof of it.  No PDF or html files are created (they are created when I run KNIME from the UI), and the files in the "knime-workspace" folder don't change to reflect any changes in the query (e.g. the Database Reader/internal/spec.xml file).  As a reminder, this is what I am putting into the command prompt:

"C:\Program Files\KNIME_2.10.0\knime.exe" -consoleLog -noexit -nosplash -application org.knime.product.KNIME_BATCH_APPLICATION -workflowDir="C:\Users\bear\knime-workspace\Example_SQL_Statement_Input" -workflow.variable=SQL,"\"SELECT SiteID, Adjustment, BWSComplete, CoPayPaid, Cash, CreditCard, Cheque, CoPayPaidMethod, Discount, PWSComplete, PWSTotalCharges, PayAtDischargePaid, PayAtDischargePaidMethod, SelfPayPaid, SelfPayPaidMethod, VisitID, PWSBalanceDue FROM CDX_PaymentInfo WHERE SiteID='CFC'\"",String

What am I doing wrong?  Again, thanks so much for your help.

-Brian Muchmore

Changes to workflow variables via the command line are not saved in the workflow, so you won't see any changes to the workflow itself. You should, however, get different output files. Apart from that your configuration looks correct. Some things that come to my mind:

- Did you try an invalid SQL query? This should result in an execution error.

- The nodes in the saved workflow are not executed, but only configured? You can try to pass -reset at the command line in order to make sure all nodes are reset prior to execution.

Thor, I think your -reset suggestion fixed the problem.  Thanks to everyone.  

To try and pay your help forward, I am going to post what I learned about editing report parameters.  At least on my set-up this guide was not entirely correct:

https://tech.knime.org/report-parameters

-Brian Muchmore