[resolved] Mapping output from a SQL query into variables to be used downstream

[Anonymous]

Scenario:
(1) Need to look up values in a control table using a database input component in which the SQL query will return a single row of values, and then (2) map those values into variables to be used subsequently in a downstream process (i.e NOT connected to subsequent component using "trigger by row").
In a database input component is there a way to map the results returned from the sql into variables which can be used in downstream components / subjobs, etc?
Note, that I prefer not to use stored procedures with OUT variables, as my application needs to be as database agnostic as possible.
Thanks,
Dave

[Anonymous]

the same question in 7763.
Best regards

shong

[Anonymous]

No - not the same question.
This one is regarding how to get output from a sql query, and then map in to context variables.
The other one is how to code a parameterized query.

[Anonymous]

David,
How many result do you have from your SQLQuery ?
If you have just once, you can probably use a tJava just after your SQLQuery output component, and assign the value like this :
context.myVariable1 = row1.field1;
context.myVariable2 = row1.field2;

* myVariable1 is your context variable created into the context tab.
* row1is the name of the link row linked to the tJava component.
* field1 the name of the metadata schema column value return by the SQLQuery.
Best regards;

[Anonymous]

I'm try to do the same thing, but it doesn't work...
First in a tMySQLInput i get the unique result of the following query :

select fullfilelines.COMPANY,fullfilelines.DATE_,fullfilelines.LINES 
from fullfilelines 
where fullfilelines.COMPANY = '" + context.COMPANY_CODE + "' 
order by DATE_ DESC limit 0,1

i need to push the DATE_ result (declared as DATE_ with the following date pattern 'yyyyMMdd') to a context variable called DATE_
i'm trying to use the following code into the tJava :

context.START_DATE = Last_Date.DATE_;

But when i try to retrive the value for context.START_DATE it seems that tJava assigned a null value
I've test the output of the query with a tLogRow and it runs.

An idea on how to put simply the field values into context variable when having one ounique row as a result.
I've already done that with tFlowtoIterate when managing several rows as result but when there is only one, is there a smarter way to do?

[Anonymous · Accepted solution]

In fact this code seems not running properly :

context.START_DATE = Last_Date.DATE_;

because when i call Last_Date.DATE_ with an msgbox i have the right values.