How do I mapping global variable into query?

[Romanesku]

Hello,

I have query with condition 

....where timestamp between
TO_DATE ('2017-09-10T00:00:00', 'YYYY-MM-DD"T"HH24:MIS')
and TO_DATE ('2017-09-10T23:59:59', 'YYYY-MM-DD"T"HH24:MIS')....

and I want to replace exact values with global variables and then during execution use it for reading data.  I tried to use iSetGlobalVar but I don't know how to joint it with tOracleInput.

Thanks for hint.

Roman 

[TRF]

Hi,
Just use globalMap. get method like this:

... TO_DATE ('" + (String)globalMap. get("yourMinVariable") + "', 'YYYY-MM-DD\"T\"HH24:MI:SS')
ANDTO_DATE ('" + (String)globalMap. get("yourMaxVariable") +"', 'YYYY-MM-DD\"T\"HH24:MI:SS') ...

Hope this helps.

Note: you may also have a look to this previous post.

[Romanesku]

Hi,

 this is clear thank you! But how can I setup the values into variables? Can I use tSetGlovalVar? and then how can I map this with Input table, where the SQL statement is used.

regards,

R

[Anonymous]

Setting values in globalMap variables can be achieved in several ways. You can use the tSetGlobalVar (as you suggested), you can also use the tFlowToIterate component. This component use the globalMap to hold records for each iteration that occurs after this component (with the key format {row_name}.{column_name} in a String....eg "row1.mycolumn"). If you are happy writing Java you can do this yourself in a tJava, tJavaRow or tJavaFlex using code like below....

globalMap.put("myKey", "my value");

globalMap.put("myKey2", 123);

globalMap.put("myKey3", row1.myColumn);

You can use globalMap values wherever you need to add a value. So you can add them to SQL statements simply by using Java String manipulation.....

"Select myColumn1,
myColumn2,
myColumn3
From myTable
Where myFilter = '" + ((String)globalMap.get("myFilterValue"))+"'"

You can also use them in parameter fields for components .....but you will need to make sure the values are set before the components are initialised in many cases.

[Romanesku]

Hi,

thank you! Another question, is this mapping and using global variables possible when I am using query from Metadata, means repository? How can I be sure that the setting values for these variables is before initialization of component tOracleInput where I want to use them?

Maybe I am doing it wrong way, what I want to achieve is, parameterized reading from source system, and the parameters will be changed during the execution. The values for these variables I want to read from configuration table in DB.

regards,

Roman 

[Romanesku]

And one more coment

I did tJava code within Job

globalMap.put("startdate", "2017-09-22T00:00:00");
globalMap.put("finishdate", "2017-09-22T23:59:59");

in the SQL Builder/Query my query has this part

******

....where timestamp between
TO_DATE ('" + ((String)globalMap.get("startdate"))+"', 'YYYY-MM-DD"T"HH24:MIS')
and TO_DATE ('" + ((String)globalMap.get("finishdate"))+"', 'YYYY-MM-DD"T"HH24:MIS')

******

But when I do test receive this message

Exception in component tOracleInput_1 (ReadFromOracle)
java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:202)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:45)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:766)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1244)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:420)
at localromanproject.readfromoracle_0_2.ReadFromOracle.tOracleInput_1Process(ReadFromOracle.java:1021)
at localromanproject.readfromoracle_0_2.ReadFromOracle.runJobInTOS(ReadFromOracle.java:1865)
at localromanproject.readfromoracle_0_2.ReadFromOracle.main(ReadFromOracle.java:1714)

[Anonymous]

You will get more used to the timing considerations within Talend as you use it more. A safe way of ensuring a value has been set is to make sure it is set in a previous SubJob. In case you are not sure, a SubJob is a block of components that are encased in a coloured rectangle. You link SubJobs with OnSubJobOK links. So if you set your globalMap values in a previous SubJob, you are guaranteed that they will be available in subsequent SubJobs. You can set them within the same SubJob, but then it is up to you to debug to ensure they have been set when you want to use them. I cannot help with that remotely.

Regarding your date issue in Oracle.....

TO_DATE ('" + ((String)globalMap.get("startdate"))+"', 'YYYY-MM-DD"T"HH24:MIMI:SS')
and TO_DATE ('" + ((String)globalMap.get("finishdate"))+"', 'YYYY-MM-DD"T"HH24:MIMI:SS')

.....you have to be aware of two things. First, are your globalMap values set? You can test this by using a System.out call in a previous tJava (just before this component). That is pretty easy to establish. The second issue here is that I believe the syntax is broken. You are using Java to write Oracle. Most of it is correct. However you are using double quotes as literal values without escaping them surrounding the T in your date format. That will not work. You will need to escape those other wise Java will interpret them as the end and beginning of new String sequences. You can escape them using a backslash just before them. Like this....

TO_DATE ('" + ((String)globalMap.get("startdate"))+"', 'YYYY-MM-DD\"T\"HH24:MIMI:SS')
and TO_DATE ('" + ((String)globalMap.get("finishdate"))+"', 'YYYY-MM-DD\"T\"HH24:MIMI:SS')

You do not have to escape the other quotes because they are quotes outside of a Java String or surrounding a String. 

If you still get errors, take few screenshots of how your job is set up and post them. In particular where you are setting the values of the globalMap, where you are using them and how they sit within the main job.

[Romanesku]

Hi,

works almost everything, I implemented SubJob and as well system.Out, the result is that I see setted values in the execution log.  Thanks. But I am not satisfied with the parameterized query. There are still errors. I tested it with Oracle and PostgreSQL DB as well.

With Oracle I receive messages like ORA-01722: invalid number when I try to use for example

where ID ='" + ((String)globalMap.get("ID"))+"'

It looks like, that the value is not set into query. I did one test where I set whole SQL statement into global parameter and then I tried to use it instead of query in input component.

In SQL Builder 

'" + ((String)globalMap.get("script"))+"'

In Basic settings / Query

"'\" + ((String)globalMap.get(\"script\"))+\"'" 

And the result was:

Starting job ParamReadFromOracle at 13:16 29/09/2017.
[statistics] connecting to socket on port 3950
[statistics] connected
Select distinct engine_name, PROCESS_NAME, ACTIVITY, sender, to_char(timestamp, 'HH24') as hours,to_char(timestamp, 'DD')as day, to_char(timestamp, 'MM') as month, to_char(timestamp, 'YYYY') as year, count(*) as pocet from tibco.TIB_MESSAGES t where timestamp between TO_DATE ('2017-09-22T00:00:00', 'YYYY-MM-DD"T"HH24:MIS')and TO_DATE ('2017-09-22T23:59:59', 'YYYY-MM-DD"T"HH24:MIS') group by engine_name, PROCESS_NAME, ACTIVITY, sender, to_char(timestamp, 'HH24') , to_char(timestamp, 'DD'), to_char(timestamp, 'MM'), to_char(timestamp, 'YYYY')
Exception in component tOracleInput_1 (ParamReadFromOracle)
java.sql.SQLException: Neplatný typ SQL: sqlKind = UNINITIALIZED
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:69)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:45)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:933)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1244)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:420)
at localromanproject.paramreadfromoracle_0_2.ParamReadFromOracle.tOracleInput_1Process(ParamReadFromOracle.java:1244)
at localromanproject.paramreadfromoracle_0_2.ParamReadFromOracle.tRunJob_1Process(ParamReadFromOracle.java:491)
at localromanproject.paramreadfromoracle_0_2.ParamReadFromOracle.runJobInTOS(ParamReadFromOracle.java:2088)
at localromanproject.paramreadfromoracle_0_2.ParamReadFromOracle.main(ParamReadFromOracle.java:1937)
[statistics] disconnected
Job ParamReadFromOracle ended

[Anonymous]

The problem which gives you the invalid number issue is caused because you are actually supplying a String or Varchar. You are using the code below.....

where ID ='" + ((String)globalMap.get("ID"))+"'

 This code will create this SQL if ID = 42 .....

where ID ='42'

You need to supply as number without single quotes. So remove them from your Java code like so....

where ID =" + ((String)globalMap.get("ID"))+"

[Anonymous · Accepted solution]

In response to your other issue, I think you are getting confused about how to build SQL with Java. In your DB component you are essentially writing Java to build a SQL query. Even a simple query like below.....

"Select
Column1,
Column2,
Coilumn3
FROM myTable"

Is Java. Once it is interpreted it becomes.....

Select
Column1,
Column2,
Coilumn3
FROM myTable

Which is essentially the same, minus the surrounding "s.

What you need to do is work out what SQL you need and what you need to parameterize, then build your Java SQL around that. So, in the following simple bit of SQL I will show you how to paramterize it....

Select
Column1,
Column2,
Column3
Where Column4 = 'Hello' AND
Column5 = 42

Notice the WHERE Clause has a Varchar param surrounded in single quotes and a numeric param without the single quotes. That is what we need to produce using Java. If we parameterize the WHERE Clause, it will look like this....

"Select
Column1,
Column2,
Column3
Where Column4 = '" +((String)globalMap.get("Column4Value")) + "' AND
Column5 = " + ((String)globalMap.get("Column5Value"))

Notice the subtle difference? 

You really have to scrutinise what your Java produces when you are not used to working like this. It does become relatively easy after a while