[resolved] How to code a SQL query that needs parameters?

One Star

[resolved] How to code a SQL query that needs parameters?

New to Talend.

How do you code a parameterized query in an database input component?

Typically in a process you (1) get some values from a single row in a control table, (2) store them into variables, and then (3) use those variables downstream in a subsequent source SQL extract process.

Ex) Select * from sales where LastUpdateDate > ? and LastUpdateDate <= ? (this is the syntax I am used to in SSIS, where there is also a mapping UI for the ? tokens)

Thanks

Dave
Employee

Re: [resolved] How to code a SQL query that needs parameters?

Hi David,

Into Talend you can write the following SQL query into the QUERY field of any Database components :

"Select * from sales where LastUpdateDate > "+context.LastUpdateDate+" and LastUpdateDate <= "+context.LastUpdateDate


The context.LastUpdateDate variable is a context variable that I create into the context tab; you can also choose to have a prompt into the GUI each time you launch the process to modify the LastUpdateDate value.

Best regards;
One Star

Re: [resolved] How to code a SQL query that needs parameters?

Thanks - I will try that.

How arre context variables scoped? Global? Local? ,etc

Can they be populated in the the output of a sql query?

Sorry - New to Talend - I am an expert in SSIS.

Dave
Community Manager

Re: [resolved] How to code a SQL query that needs parameters?

Hello Dave
I just create a simple job to show you how to use the context var in slq query. Please see my screenshots.

Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] How to code a SQL query that needs parameters?

Ah - thanks!

Is input_row a reserved word referencing the upstream input?

Can you set more than one context variable in a tJavaRow? Separate statements by semicolons?

Dave
One Star

Re: [resolved] How to code a SQL query that needs parameters?

shong wrote:
Hello Dave
I just create a simple job to show you how to use the context var in slq query. Please see my screenshots.

Best regards

shong

Just tried that. When I run, I get an error: [row1.source cannot be resolved or is not a field]

source is what I am retuening from the sql query in upstream tOracleInput.
Employee

Re: [resolved] How to code a SQL query that needs parameters?

David,

Please add some screenshots or error traces. It's help to understand where is your mistake or problem.

What's the name of the ROW in your jobDesign ? and what's exactly the name of the Field ?

The trouble is because you call the variable with a wrong VARIABLE_NAME. Unknown for the code generator.

Best regards
One Star

Re: [resolved] How to code a SQL query that needs parameters?

cantoine wrote:
David,

Please add some screenshots or error traces. It's help to understand where is your mistake or problem.

What's the name of the ROW in your jobDesign ? and what's exactly the name of the Field ?

The trouble is because you call the variable with a wrong VARIABLE_NAME. Unknown for the code generator.

Best regards

Figured it out - I was linking from the tJavaRow to a downstream subjob. I changed he flow to link from the tOracleInput to the downstream subjob, and that worked.
Employee

Re: [resolved] How to code a SQL query that needs parameters?

Sounds good Smiley Happy

Best regards;
One Star

Re: [resolved] How to code a SQL query that needs parameters?

Hi, I am trying to use context variables from the mapping perspective using the getmapproperty.
here is a screen shot to better explain what I am trying to do.


the getmapproperty has a return type of string. how do I over come this issue?
thanks
Rajani

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog