Hi,
I have to insert a time based set of rows into a target table. The target table may have existing rows within that time range so I have to first delete any of those rows from the target table. I get the min and max range values via a query to my source database and then store those in global variables. How to I generate the delete statement using those values as the range values?
I.e.
delete from T where dt > ? and dt <= ?
Or is there a better way to do this?
Thanks,
David
I have tried that but I may be doing something incorrectly. I have 2 columns flowing through to the tMSSQLRow component but when I try to get it to recognize both parameters (startDate and endDate) at the same time it can't do it. Do I use the preparedStatement? If so, how do I map the global variables to the "?" place holders?
Hello,
for example when u want to select data from database using tOracleInput or whatever else database Input component, you could use there following statement:
"SELECT number FROM myTable WHERE number<" + Integer.parseInt((String)globalMap.get("myCondition"))
the global variable is now very limited to be capable of keep only the String object value, I will try to make it capable of keep also other primitive types, in the future it should also keep whole objects, whole data structure...but this is only idea right now.
Best regards,
Ladislav
e.g. "DELETE FROM T WHERE dt>'"+TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",(Date)globalMap.get("MinDt"))+"' AND dt<'"+TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",(Date)globalMap.get("MaxDt"))+"'"
Note that you don't have to store MinDt and MaxDt into the globalMap; you can connect the row from your input component containing those values directly to tRow and replace e.g. (Date)globalMap.get("MinDt") with row1.MinDt in the above.
Thanks! But after I've ensured that no data remain within the target table in the date range, I need to use those same values to query the data, which is why I used the global variable. It would be helpful if I could retain those values w/o the global variable. Is it possible as part of the data flow?
--David
You can't pass the flow through the tMSSqlRow component to tMSSqlInput but you can use an iterate link between the two, in which case the row values will be available to use in the latter. However, if you need the values in a new subjob, you'll have to store them into the globalMap or a context variable.
OK. I like your suggestion of not using the global variable, taking the input values directly from the tMSSSqlInput component and using that in the tMSSqlRow component. It works like this:
" delete from myTable where createdDate >= '" + TalendDate.formatDate("yyyy-MM-dd HH:mm:ss", row1.dtRangeStartIn) + "'" + " and createdDate < '" + TalendDate.formatDate("yyyy-MM-dd HH:mm:ss", row1.dtRangeEndEx) + "'"
I would like to have done this as a preparedStatement but I couldn't get that to work. I tried as the setup:
Parameter Index Parameter Type Parameter Value
"1" Date row1.dtRangeStartIn
"2" Date row1.dtRangeEndEx
However, that produces the following error messages:
The method setTimeStamp(int, Timestamp) in the type PrepareStatement is not applicable for the arguments (String, Timestamp)
Is there a way to do that? If so, it would mean that I wouldn't have to convert objects explicitly, which is cleaner.
The solution that you mention regarding and iterate link is interesting but I don't understand how that would work. Would that be a
tIterateToFlow component or a
tFlowToIterate component?
And where would it go?
I haven't used prepared statements so can't help you there.
To change a flow to an iterate (and store the row values in the globalMap) use tFlowToIterate and vice versa.
If you're just connecting tMSSqlInput after tMSSqlRow you don't need tFlowToIterate; you can use iterate directly from tMSSqlRow and reference the row values leading in to tMSSqlRow. Remember that Talend generates Java code so those row/column value variables have been declared and are accessible to any component following that is part of the same subjob.
I tried implementing your suggestion about not needing the tFlowToIterate component but can't get it to work. First, I have to connect the tMSSQLInput to the MSSQLRow via the iterate submenu choice. At this point, I can't find the variables from the previous steps.
