Variable for SQL in Database Reader

Hello,

I was wondering if there is any way that I can pass in values to the Database Reader in order to restrict my SQL query. Since there is no input port, I'm guessing this is not possible with the Database Reader. Does anyone know of another (custom?) component that would permit this?

Many thanks,

Andrew

Hello,

Good news: in KNIME 2.0 we will have a Database Looping node which allows to iteratively restrict the SQL where-clause. During each run one or more values, delivered by an input table, can be replaced in the where-clause place holder. The result sets are combined into a final KNIME table and returned at the node's out-port.

Cheers,
Thomas

Hi Thomas,

Good news about the database row filter, but it would be useful if you can have muliple place holders, so for example you can have the following sql query;

Select * from table1 where col1 = <##placeholder1##> and col2 = <##placeholder2##> and col3 = <##placeholder3##> etc.

I routinely need to do this type of query, and the ability to add as many columns as necessary into the filter would be useful

Stanage

Hi,

even this is possible. With KNIME 2.0 one is able to define loop variables which are passed along the pipeline. This variables (e.g. defined by table with a list SQL statements) can be passed into the Database Query node (with new database ports). The query node can then be execute multiple times for each SQL query; the Loop End node collects the results and composes the final table.

Happy?
Thomas

Hi all,
Just for completeness, in order to use the Database Query node inside a loop, the flow variable must contain the enter select statement - even the placeholder #table# needs to be included. This can magically be ignored by commenting it out, such as SELECT * /* #table# */ FROM newtable. Those statements can for example be generated inside the Java Snippet (that allows using flow variables as well), translated to variables using the TableRow to Variable and later injected into a database connection using the Inject Variable (Database) node. Good luck!
Best, Thomas

Hi,

I want to dump from some external database to my local database for some frequently used data. For such purpose I setup local database and I have query for both side. But with my general approach data get duplicate on every execuation at localdb. In order to avoid, I want first query to local database table from which i will get last record datetime and pass this result in query to external database and get recent data after datetime. After that I want to update my table with recent data. How can I do this. Could you please help me how to do. I am unable to assign output from local database query to query for external database.

I am getting last record with: SELECT datetime FROM table1 ORDER BY datetime DESC LIMIT 1. I want to pass this result as variable to another database query in query statement.

BR,

New User

Sritemp,

Read your date to string variable.

Pass it to Database Query node.

Then use a sintax like service_date >'$${S@D_From}$$' in were clause.

@D_From is variable name.

Hi,

Still I could not get it.

Could you please help me assigning variable from output of database reader and use that variable in another database reader as u said variable name S@D_From.

Hi,

Still I could not get it.

Could you please help me assigning variable from output of database reader and use that variable in another database reader as u said variable name S@D_From.