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 

[Romanesku]

Hi,

thanks a lot, usually it works (with strings and numbers), but  there should be problem with date format when is used as global variable.

this is my Java

globalMap.put("startdate", "2017-09-22T00:00:00");
globalMap.put("enddate", "2017-09-22T23:59:59");
globalMap.put("script",
"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 ('" + ((String)globalMap.get("startdate"))+"', 'YYYY-MM-DD\"T\"HH24:MIS')and TO_DATE ('" + ((String)globalMap.get("enddate"))+"', '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')");

System.out.println(globalMap.get("script"));
System.out.println(globalMap.get("startdate"));
System.out.println(globalMap.get("enddate"));

And this is the result from Job

Starting job ParamReadFromOracle at 16:21 29/09/2017.
[statistics] connecting to socket on port 3736
[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')
2017-09-22T00:00:00
2017-09-22T23:59:59
Exception in component tOracleInput_1 (ParamReadFromOracle)
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.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 at 16:21 29/09/2017. [exit code=1]

[Anonymous]

Unfortunately I cannot really debug your SQL from here. What I have seen looks OK (although you are using a lot of words that are often reserved). What you need to do is try to run the SQL in a DB application (TOAD, etc) and see if you can see what is wrong. Then when you find it, work those changes back into your Java code.

Try running this....

 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:MI:SS') and TO_DATE ('2017-09-22T23:59:59', 'YYYY-MM-DD"T"HH24:MISmiley FrustratedS') 
 group by engine_name, PROCESS_NAME, ACTIVITY, sender, to_char(timestamp, 'HH24') , to_char(timestamp, 'DD'), to_char(timestamp, 'MM'), to_char(timestamp, 'YYYY')

[Romanesku]

Now I found what should be the problem. I the previous solution I used SubJob for setting global variable and join it with Output component by OnSubJobOK, but now when I create tJava code in Job an join it the same way, the values are setted into query correctly and the Job was success.

[Romanesku]

Hi,

do you have guidance for date values as well?

I have problem with using it in where clause for MySQL.

when I use

" Select...

....WHERE `t_application`.`c_change_date` >= '2018-07-01 00:00:00.000'"

the select return 167 records.

but when I want to use global var

.....WHERE `t_application`.`c_change_date` >= '" + (globalMap.get("last_run")) +"' "

return nothing.

I tried

WHERE `t_application`.`c_change_date` >= '" + ((Date)globalMap.get("last_run")) +"' "

WHERE `t_application`.`c_change_date` >= '" + (globalMap.get("last_run")) +"' "

also with no records.

When I change the part after >=, without single parenthesis ' ... '

WHERE `t_application`.`c_change_date` >= " + ((Date)globalMap.get("last_run")) +" "

This return message where I see the value from variable

...

Sync_AppCat|2018-07-01 00:00:00.000|N|2018-08-07 09:18:26.026

Exception in component tMysqlInput_1 (ReadAPPFromCatalogue)

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Jul 01 00:00:00 CEST 2018' at line 122

at

...

Thank you

Roman