Post a reply

Write your message and submit

Options

Click in the dark area of the image to send your post.

Go back

Topic review (newest first)

CAu
2008-09-18 13:02:50

Hello,

SORRY.
IT'S GOOD. A mistake in my verification. I have forgotten a filter.
So, the two solutions are good.

THANKS A LOT, José for the method with the tmap.

Best regards.

CAu
2008-09-17 11:11:16

hello,

Very GOOD idea, José..

Nevertheless, I don't succeed to have all my lines.

Source Mysql datetime type field. Lines I want to load.
I want to compare this datetime to a date (timestamp) stored in a postgresql table (the date of the last success execution job). This table is built with a tstatcatcher.

The job :
tPostgresqlInput ---(Main)---tJavaRow---(On Component OK) ---tMysqLInput----(Main)---tMap---(Main)---tlogRow

tPostgresqlInput

Code:

select * from "HIST" where "type_msge" = 'end' and "msge" = 'success'
order by "date" DESC LIMIT 1

tJavaRow

Code:

System.out.println(row4.date);                                                                          --> Tue Sep 09 16:06:26 CEST 2008
context.dateComparaison =ConvDate.convertDate(row4.date);                          --> context.dateComparaison is string type - ConvDate.convertDate gives J-1
System.out.println(context.dateComparaison);                                                     --> 2008-09-08 16:06:26

The routine ConvDate :

Code:

package routines;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.util.Calendar;

public class CBConvDate {
 public static String convertDate(Date ladate){
 
     Calendar c=Calendar.getInstance();
     c.setTime(ladate);
     c.add(Calendar.DAY_OF_MONTH, -1);
     SimpleDateFormat sim=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
     String reportDate=sim.format(c.getTime());
     return reportDate;
 }
 }

tMysqlInput

Code:

select * from tableA

tMap
with the following filter as José suggests :
row1.last_acti.after(TalendDate.parseDate("yyyy-MM-dd HH:mm:ss",context.dateComparaison))

The result :

Code:

2832||0||||||||0|||2008-09-17 09:40:51
2837||0||||||||0|||2008-09-17 09:26:50
3981||0||||||||0|||2008-09-17 10:14:26
4047||0||||||||0|||2008-09-17 10:21:57
4674||0||||||||0|||2008-09-17 09:28:16
4826||0||||||||0|||2008-09-17 09:28:34
4858||0||||||||0|||2008-09-17 09:29:01
5294||0||||||||0|||2008-09-17 09:29:21
5495||0||||||||0|||2008-09-17 09:29:41
5612||0||||||||0|||2008-09-17 09:30:02
5622||0||||||||0|||2008-09-17 09:30:23
5982||0||||||||0|||2008-09-17 09:40:20
5984||0||||||||0|||2008-09-17 09:33:12
6459||0||||||||0|||2008-09-17 09:02:07
6654||0||||||||0|||2008-09-17 09:24:10
6694||0||||||||0|||2008-09-17 09:47:13
6977||0||||||||0|||2008-09-17 09:36:26
7016||0||||||||0|||2008-09-17 09:33:44
7274||0||||||||0|||2008-09-17 08:57:44
7387||0||||||||0|||2008-09-17 08:56:46
7439||0||||||||0|||2008-09-17 08:56:07
7587||0||||||||0|||2008-09-17 09:30:13
7747||0||||||||0|||2008-09-17 09:35:34
7755||0||||||||0|||2008-09-17 09:22:31
7797||0||||||||0|||2008-09-17 09:21:33
7954||0||||||||0|||2008-09-15 14:27:58
8034||0||||||||0|||2008-09-09 17:12:56
8126||0||||||||0|||2008-09-17 09:35:56
8280||0||||||||0|||2008-09-17 08:49:22
8400||0||||||||0|||2008-09-17 10:20:27
8448||0||||||||0|||2008-09-17 09:38:26
8752||0||||||||0|||2008-09-17 08:44:03
8757||0||||||||0|||2008-09-08 17:23:34
8812||0||||||||0|||2008-09-17 09:12:29
8817||0||||||||0|||2008-09-15 11:01:54
8823||0||||||||0|||2008-09-17 09:39:33
8848||0||||||||0|||2008-09-08 17:06:21
8888||0||||||||0|||2008-09-16 11:19:38
8910||0||||||||0|||2008-09-17 08:38:45
8928||0||||||||0|||2008-09-17 08:41:18
8938||0||||||||0|||2008-09-17 09:11:21
8949||0||||||||0|||2008-09-10 17:18:40
8953||0||||||||0|||2008-09-08 17:05:34
8970||0||||||||0|||2008-09-15 14:26:35
8971||0||||||||0|||2008-09-17 09:47:55
8973||0||||||||0|||2008-09-08 17:13:43
8988||0||||||||0|||2008-09-11 15:41:44
8992||0||||||||0|||2008-09-16 11:28:25
8999||0||||||||0|||2008-09-09 11:27:54
9016||0||||||||0|||2008-09-17 09:07:33
9023||0||||||||0|||2008-09-12 16:39:04
9029||0||||||||0|||2008-09-09 09:34:11
9030||0||||||||0|||2008-09-09 09:15:39
9039||0||||||||0|||2008-09-12 10:35:28
9049||0||||||||0|||2008-09-11 14:03:45
9050||0||||||||0|||2008-09-09 11:27:23
9051||0||||||||0|||2008-09-15 10:01:55
9060||0||||||||0|||2008-09-09 09:14:01
9061||0||||||||0|||2008-09-09 09:49:38
9063||0||||||||0|||2008-09-09 09:50:59
9068||0||||||||0|||2008-09-11 11:35:31
9071||0||||||||0|||2008-09-16 11:56:23
9073||0||||||||0|||2008-09-11 14:03:05
9075||0||||||||0|||2008-09-17 09:24:38
9076||0||||||||0|||2008-09-15 09:33:54
9077||0||||||||0|||2008-09-08 17:05:04
9079||0||||||||0|||2008-09-09 11:31:25
9084||0||||||||0|||2008-09-09 10:58:58
9087||0||||||||0|||2008-09-09 10:32:06
9088||0||||||||0|||2008-09-17 08:36:17
9089||0||||||||0|||2008-09-09 09:25:11
9090||0||||||||0|||2008-09-17 09:26:55
9091||0||||||||0|||2008-09-09 09:50:29
9092||0||||||||0|||2008-09-11 14:02:15
9093||0||||||||0|||2008-09-10 09:15:35
9094||0||||||||0|||2008-09-16 11:38:03
9096||0||||||||0|||2008-09-11 14:48:02
9097||0||||||||0|||2008-09-12 09:51:21
9102||0||||||||0|||2008-09-09 11:22:09
9103||0||||||||0|||2008-09-09 11:26:37
9105||0||||||||0|||2008-09-11 10:01:13
9106||0||||||||0|||2008-09-11 10:03:01
9107||0||||||||0|||2008-09-09 16:35:41
9108||0||||||||0|||2008-09-09 15:01:30
9109||0||||||||0|||2008-09-17 09:40:05
9110||0||||||||0|||2008-09-09 17:00:55
9112||0||||||||0|||2008-09-10 17:33:30
9114||0||||||||0|||2008-09-11 12:09:32
9116||0||||||||0|||2008-09-11 09:38:14
9117||0||||||||0|||2008-09-11 10:02:29
9118||0||||||||0|||2008-09-15 14:22:45
9119||0||||||||0|||2008-09-10 13:44:59
9120||0||||||||0|||2008-09-11 10:01:23
9122||0||||||||0|||2008-09-11 10:08:22
9124||0||||||||0|||2008-09-11 10:10:35
9125||0||||||||0|||2008-09-11 13:56:45
9126||0||||||||0|||2008-09-11 10:13:45
9127||0||||||||0|||2008-09-16 15:04:13
9128||0||||||||0|||2008-09-11 10:11:46
9129||0||||||||0|||2008-09-11 15:00:12
9131||0||||||||0|||2008-09-11 10:45:53
9132||0||||||||0|||2008-09-15 14:31:14
9134||0||||||||0|||2008-09-16 14:00:03
9135||0||||||||0|||2008-09-11 10:45:53
9136||0||||||||0|||2008-09-11 10:45:02
9137||0||||||||0|||2008-09-11 15:00:16
9138||0||||||||0|||2008-09-11 10:26:37
9139||0||||||||0|||2008-09-11 14:41:28
9140||0||||||||0|||2008-09-11 16:35:07
9141||0||||||||0|||2008-09-11 16:01:31
9142||0||||||||0|||2008-09-11 11:00:31
9143||0||||||||0|||2008-09-11 11:24:50
9145||0||||||||0|||2008-09-11 11:53:03
9146||0||||||||0|||2008-09-11 14:12:10
9147||0||||||||0|||2008-09-11 14:54:14
9148||0||||||||0|||2008-09-15 10:41:30
9149||0||||||||0|||2008-09-11 14:44:46
9150||0||||||||0|||2008-09-11 16:22:15
9151||0||||||||0|||2008-09-12 16:49:36
9152||0||||||||0|||2008-09-11 15:13:43
9153||0||||||||0|||2008-09-11 15:24:46
9157||0||||||||0|||2008-09-16 11:39:53
9158||0||||||||0|||2008-09-11 18:04:15
9159||0||||||||0|||2008-09-15 18:17:36
9160||0||||||||0|||2008-09-12 14:54:13
9161||0||||||||0|||2008-09-12 10:26:32
9162||0||||||||0|||2008-09-12 14:20:29
9163||0||||||||0|||2008-09-16 11:54:44
9164||0||||||||0|||2008-09-12 14:15:34
9165||0||||||||0|||2008-09-15 10:40:27
9166||0||||||||0|||2008-09-12 16:46:59
9167||0||||||||0|||2008-09-16 17:17:54
9168||0||||||||0|||2008-09-12 11:38:21
9170||0||||||||0|||2008-09-12 13:21:47
9171||0||||||||0|||2008-09-12 16:47:45
9172||0||||||||0|||2008-09-15 12:34:33
9173||0||||||||0|||2008-09-15 09:45:42
9174||0||||||||0|||2008-09-12 14:28:00
9176||0||||||||0|||2008-09-12 15:35:35
9177||0||||||||0|||2008-09-15 09:59:25
9178||0||||||||0|||2008-09-16 15:13:25
9181||0||||||||0|||2008-09-15 14:21:36
9182||0||||||||0|||2008-09-15 09:52:34
9183||0||||||||0|||2008-09-15 07:52:00
9184||0||||||||0|||2008-09-15 15:22:57
9188||0||||||||0|||2008-09-15 11:20:47
9189||0||||||||0|||2008-09-16 14:00:41
9190||0||||||||0|||2008-09-15 14:45:01
9192||0||||||||0|||2008-09-15 16:12:12
9195||0||||||||0|||2008-09-15 15:51:44
9196||0||||||||0|||2008-09-15 16:39:24
9197||0||||||||0|||2008-09-16 13:49:56
9199||0||||||||0|||2008-09-15 16:44:41
9200||0||||||||0|||2008-09-16 09:23:13
9201||0||||||||0|||2008-09-17 08:33:38
9202||0||||||||0|||2008-09-16 09:35:46
9203||0||||||||0|||2008-09-16 10:14:55
9205||0||||||||0|||2008-09-16 11:13:29
9207||0||||||||0|||2008-09-16 10:01:35
9208||0||||||||0|||2008-09-16 11:14:29
9209||0||||||||0|||2008-09-16 11:11:39
9210||0||||||||0|||2008-09-16 11:35:02
9211||0||||||||0|||2008-09-17 08:32:49
9212||0||||||||0|||2008-09-16 16:06:51
9214||0||||||||0|||2008-09-16 12:31:55
9215||0||||||||0|||2008-09-16 14:05:15
9216||0||||||||0|||2008-09-16 14:28:52
9217||0||||||||0|||2008-09-16 15:24:26
9219||0||||||||0|||2008-09-16 15:21:05
9220||0||||||||0|||2008-09-17 09:51:33
9223||0||||||||0|||2008-09-17 09:49:23
9225||0||||||||0|||2008-09-17 09:11:49
9228||0||||||||0|||2008-09-17 09:13:21
9229||0||||||||0|||2008-09-17 10:11:33
9230||0||||||||0|||2008-09-17 09:52:10

Lines missing - example :

Code:

4130    2008-09-11 14:15:40
5979    2008-09-17 09:05:50
7868    2008-09-16 11:40:27
8472    2008-09-15 16:56:59
8692    2008-09-08 17:58:21
8923    2008-09-09 09:26:18
8926    2008-09-09 09:20:25
8985    2008-09-09 17:16:14
9033    2008-09-09 09:03:02
9053    2008-09-11 13:01:17
9055    2008-09-09 14:08:28
...

---------------
OTHER TEST :
The job :
tPostgresqlInput ---(Main)---tJavaRow---(On Component OK) ---tMysqLInput----(Main)---tMap---(Main)---tlogRow

tPostgresqlInput

Code:

select * from "HIST" where "type_msge" = 'end' and "msge" = 'success'
order by "date" DESC LIMIT 1

tJavaRow

Code:

context.maDate=row4.date;                    --> context.maDate is date type                        
System.out.println(context.maDate);                --> Tue Sep 09 16:06:26 CEST 2008

tMysqlInput

Code:

select * from tableA

tMap
with the following filter
row1.last_acti.getTime()-context.maDate.getTime()>=0

The result :

Code:

2832||0||||||||0|||2008-09-17 09:40:51
2837||0||||||||0|||2008-09-17 09:26:50
3981||0||||||||0|||2008-09-17 09:27:18
4047||0||||||||0|||2008-09-17 09:27:37
4674||0||||||||0|||2008-09-17 09:28:16
4826||0||||||||0|||2008-09-17 09:28:34
4858||0||||||||0|||2008-09-17 09:29:01
5294||0||||||||0|||2008-09-17 09:29:21
5495||0||||||||0|||2008-09-17 09:29:41
5612||0||||||||0|||2008-09-17 09:30:02
5622||0||||||||0|||2008-09-17 09:30:23
5982||0||||||||0|||2008-09-17 09:40:20
5984||0||||||||0|||2008-09-17 09:33:12
6459||0||||||||0|||2008-09-17 09:02:07
6654||0||||||||0|||2008-09-17 09:24:10
6694||0||||||||0|||2008-09-17 09:47:13
6977||0||||||||0|||2008-09-17 09:36:26
7016||0||||||||0|||2008-09-17 09:33:44
7274||0||||||||0|||2008-09-17 08:57:44
7387||0||||||||0|||2008-09-17 08:56:46
7439||0||||||||0|||2008-09-17 08:56:07
7587||0||||||||0|||2008-09-17 09:30:13
7747||0||||||||0|||2008-09-17 09:35:34
7755||0||||||||0|||2008-09-17 09:22:31
7797||0||||||||0|||2008-09-17 09:21:33
7954||0||||||||0|||2008-09-15 14:27:58
8034||0||||||||0|||2008-09-09 17:12:56
8126||0||||||||0|||2008-09-17 09:35:56
8280||0||||||||0|||2008-09-17 08:49:22
8400||0||||||||0|||2008-09-17 09:21:07
8448||0||||||||0|||2008-09-17 09:38:26
8752||0||||||||0|||2008-09-17 08:44:03
8812||0||||||||0|||2008-09-17 09:12:29
8817||0||||||||0|||2008-09-15 11:01:54
8823||0||||||||0|||2008-09-17 09:39:33
8888||0||||||||0|||2008-09-16 11:19:38
8910||0||||||||0|||2008-09-17 08:38:45
8928||0||||||||0|||2008-09-17 08:41:18
8938||0||||||||0|||2008-09-17 09:11:21
8949||0||||||||0|||2008-09-10 17:18:40
8970||0||||||||0|||2008-09-15 14:26:35
8971||0||||||||0|||2008-09-17 09:47:55
8988||0||||||||0|||2008-09-11 15:41:44
8992||0||||||||0|||2008-09-16 11:28:25
9016||0||||||||0|||2008-09-17 09:07:33
9023||0||||||||0|||2008-09-12 16:39:04
9039||0||||||||0|||2008-09-12 10:35:28
9049||0||||||||0|||2008-09-11 14:03:45
9051||0||||||||0|||2008-09-15 10:01:55
9068||0||||||||0|||2008-09-11 11:35:31
9071||0||||||||0|||2008-09-16 11:56:23
9073||0||||||||0|||2008-09-11 14:03:05
9075||0||||||||0|||2008-09-17 09:24:38
9076||0||||||||0|||2008-09-15 09:33:54
9088||0||||||||0|||2008-09-17 08:36:17
9090||0||||||||0|||2008-09-17 09:26:55
9092||0||||||||0|||2008-09-11 14:02:15
9093||0||||||||0|||2008-09-10 09:15:35
9094||0||||||||0|||2008-09-16 11:38:03
9096||0||||||||0|||2008-09-11 14:48:02
9097||0||||||||0|||2008-09-12 09:51:21
9105||0||||||||0|||2008-09-11 10:01:13
9106||0||||||||0|||2008-09-11 10:03:01
9107||0||||||||0|||2008-09-09 16:35:41
9109||0||||||||0|||2008-09-17 09:40:05
9110||0||||||||0|||2008-09-09 17:00:55
9112||0||||||||0|||2008-09-10 17:33:30
9114||0||||||||0|||2008-09-11 12:09:32
9116||0||||||||0|||2008-09-11 09:38:14
9117||0||||||||0|||2008-09-11 10:02:29
9118||0||||||||0|||2008-09-15 14:22:45
9119||0||||||||0|||2008-09-10 13:44:59
9120||0||||||||0|||2008-09-11 10:01:23
9122||0||||||||0|||2008-09-11 10:08:22
9124||0||||||||0|||2008-09-11 10:10:35
9125||0||||||||0|||2008-09-11 13:56:45
9126||0||||||||0|||2008-09-11 10:13:45
9127||0||||||||0|||2008-09-16 15:04:13
9128||0||||||||0|||2008-09-11 10:11:46
9129||0||||||||0|||2008-09-11 15:00:12
9131||0||||||||0|||2008-09-11 10:45:53
9132||0||||||||0|||2008-09-15 14:31:14
9134||0||||||||0|||2008-09-16 14:00:03
9135||0||||||||0|||2008-09-11 10:45:53
9136||0||||||||0|||2008-09-11 10:45:02
9137||0||||||||0|||2008-09-11 15:00:16
9138||0||||||||0|||2008-09-11 10:26:37
9139||0||||||||0|||2008-09-11 14:41:28
9140||0||||||||0|||2008-09-11 16:35:07
9141||0||||||||0|||2008-09-11 16:01:31
9142||0||||||||0|||2008-09-11 11:00:31
9143||0||||||||0|||2008-09-11 11:24:50
9145||0||||||||0|||2008-09-11 11:53:03
9146||0||||||||0|||2008-09-11 14:12:10
9147||0||||||||0|||2008-09-11 14:54:14
9148||0||||||||0|||2008-09-15 10:41:30
9149||0||||||||0|||2008-09-11 14:44:46
9150||0||||||||0|||2008-09-11 16:22:15
9151||0||||||||0|||2008-09-12 16:49:36
9152||0||||||||0|||2008-09-11 15:13:43
9153||0||||||||0|||2008-09-11 15:24:46
9157||0||||||||0|||2008-09-16 11:39:53
9158||0||||||||0|||2008-09-11 18:04:15
9159||0||||||||0|||2008-09-15 18:17:36
9160||0||||||||0|||2008-09-12 14:54:13
9161||0||||||||0|||2008-09-12 10:26:32
9162||0||||||||0|||2008-09-12 14:20:29
9163||0||||||||0|||2008-09-16 11:54:44
9164||0||||||||0|||2008-09-12 14:15:34
9165||0||||||||0|||2008-09-15 10:40:27
9166||0||||||||0|||2008-09-12 16:46:59
9167||0||||||||0|||2008-09-16 17:17:54
9168||0||||||||0|||2008-09-12 11:38:21
9170||0||||||||0|||2008-09-12 13:21:47
9171||0||||||||0|||2008-09-12 16:47:45
9172||0||||||||0|||2008-09-15 12:34:33
9173||0||||||||0|||2008-09-15 09:45:42
9174||0||||||||0|||2008-09-12 14:28:00
9176||0||||||||0|||2008-09-12 15:35:35
9177||0||||||||0|||2008-09-15 09:59:25
9178||0||||||||0|||2008-09-16 15:13:25
9181||0||||||||0|||2008-09-15 14:21:36
9182||0||||||||0|||2008-09-15 09:52:34
9183||0||||||||0|||2008-09-15 07:52:00
9184||0||||||||0|||2008-09-15 15:22:57
9188||0||||||||0|||2008-09-15 11:20:47
9189||0||||||||0|||2008-09-16 14:00:41
9190||0||||||||0|||2008-09-15 14:45:01
9192||0||||||||0|||2008-09-15 16:12:12
9195||0||||||||0|||2008-09-15 15:51:44
9196||0||||||||0|||2008-09-15 16:39:24
9197||0||||||||0|||2008-09-16 13:49:56
9199||0||||||||0|||2008-09-15 16:44:41
9200||0||||||||0|||2008-09-16 09:23:13
9201||0||||||||0|||2008-09-17 08:33:38
9202||0||||||||0|||2008-09-16 09:35:46
9203||0||||||||0|||2008-09-16 10:14:55
9205||0||||||||0|||2008-09-16 11:13:29
9207||0||||||||0|||2008-09-16 10:01:35
9208||0||||||||0|||2008-09-16 11:14:29
9209||0||||||||0|||2008-09-16 11:11:39
9210||0||||||||0|||2008-09-16 11:35:02
9211||0||||||||0|||2008-09-17 08:32:49
9212||0||||||||0|||2008-09-16 16:06:51
9214||0||||||||0|||2008-09-16 12:31:55
9215||0||||||||0|||2008-09-16 14:05:15
9216||0||||||||0|||2008-09-16 14:28:52
9217||0||||||||0|||2008-09-16 15:24:26
9219||0||||||||0|||2008-09-16 15:21:05
9220||0||||||||0|||2008-09-17 09:51:33
9223||0||||||||0|||2008-09-17 09:49:23
9225||0||||||||0|||2008-09-17 09:11:49
9228||0||||||||0|||2008-09-17 09:13:21
9229||0||||||||0|||2008-09-17 09:52:41
9230||0||||||||0|||2008-09-17 09:52:10

Lines Missing :

Code:

4130    2008-09-11 14:15:40
7868    2008-09-16 11:40:27
8472    2008-09-15 16:56:59
8985    2008-09-09 17:16:14
9053    2008-09-11 13:01:17
9081    2008-09-10 15:12:01
9101    2008-09-10 16:52:15
9111    2008-09-09 16:59:10
9113    2008-09-10 09:19:21
9115    2008-09-14 09:36:19
9121    2008-09-10 15:14:20
9123    2008-09-15 11:53:02
9130    2008-09-10 16:01:34
9133    2008-09-11 08:23:25
9144    2008-09-12 16:51:08
9154    2008-09-11 15:53:45
9155    2008-09-16 09:08:12
9156    2008-09-15 16:52:09
9169    2008-09-12 16:47:19
9175    2008-09-15 11:20:30
9179    2008-09-12 16:48:53
9180    2008-09-15 11:58:21
9185    2008-09-15 10:40:04
9186    2008-09-15 11:54:55
9187    2008-09-15 11:33:43
9191    2008-09-15 15:15:17
9193    2008-09-15 16:12:58
9198    2008-09-16 12:13:39
9201    2008-09-16 11:06:41
9204    2008-09-16 10:15:15
9206    2008-09-16 12:14:08
9211    2008-09-16 15:14:37
9213    2008-09-16 14:23:01
9218    2008-09-16 14:17:34
9221    2008-09-16 15:26:01
9222    2008-09-16 16:12:06

Any idea ?
I hates date !!!

THANKS A LOT FOR YOUR ATTENTION.
Best regards.

jcoutin
2008-09-15 16:33:41

Hello,

Try to add in Tmap an input filter :

MysqlInput.last-date.after(context.dateComparaison)

(maybe you'll have to add TalendDate.parseDate for dateComparaison)

José

CAu
2008-09-15 16:21:56

Hello,

Environment : TOS 2.4.1- Postgresql 8.1.4(DWH)  MySQL(Data source) JAVA

I want to load data from a table of a mysql database to the datawarehouse (Postgresql).

No problem to load all the data but when I want to load only the data which date-last-activity > date of the last success job execution, the job load ALL the data and not the last updated lines.

The job :
tPostgresqlInput ---Main---tJavaRow---OnComponentOK----tMySqlInput

The tPostgresqlInput component :

Code:

select * from "HIST" where "type_msge" = 'end' and "msge" = 'success'
order by "date" DESC LIMIT 1

The table HIST contains all the job execution information and is made with a tStatCatcher. The aim is to have the date of the last job success execution.

The tJavaRow component :

Code:

context.maDate=row4.date;
System.out.println(context.maDate);
System.out.println(TalendDate.formatDate("yyyy-MM-dd",context.maDate));

The type of the variable context.maDate is Date
System.out.println(context.maDate);                  The result : Tue Sep 09 16:06:26 CEST 2008
System.out.println(TalendDate.formatDate("yyyy-MM-dd",context.maDate));        The result : 2008-09-09

The tMysqlInput component :

Code:

select *
from tableA
where last-date >'"+TalendDate.formatDate("yyyy-MM-dd",context.maDate)+"';

PROBLEM : ALL the rows are loading.


If the tMysqlInput component is :

Code:

select *
from tableA
where last-date > '2008-09-09';

It's OK. Only the lines updated after or the '2008-09-09' are loaded.

If the tMysqlInput component is :

Code:

select *
from tableA
where last-date < '"+TalendDate.formatDate("yyyy-MM-dd",context.maDate)+"';

PROBLEM : No line.

------------------------------------------------------------------------------------
Other test : with a STRING context variable


The tPostgresqlInput component :               The aim is to have the date of the last job success execution.

Code:

select * from "HIST" where "type_msge" = 'end' and "msge" = 'success'
order by "date" DESC LIMIT 1

The tJavaRow component :

Code:

context.dateComparaison=row1.date.toString();       
System.out.println(context.dateComparaison);                             --> The result : Tue Sep 09 16:06:26 CEST 2008
System.out.println(ConvDate.convertDate(context.dateComparaison));     -->The result : 2008-09-09

The ConvDate routine :

Code:

package routines;
import java.text.SimpleDateFormat;
import java.text.ParsePosition;
import java.util.Locale;
public class ConvDate {
public static String convertDate(String ladate){
SimpleDateFormat sdf = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy", Locale.US);
SimpleDateFormat sdff = new SimpleDateFormat("yyyy-MM-dd", Locale.US);
return sdff.format(sdf.parse(ladate,new ParsePosition(0)));
}
}

The tMysqlInput component:

Code:

select *
from tableA
where last-date > '"+ConvDate.convertDate(context.dateComparaison)+"';

PROBLEM : ALL the lines are loaded.

If I try :

Code:

select *
from tableA
where last-date > '2008-09-09';

It's OK. Only the lines updated after or the '2008-09-09' are loaded.

If I try :

Code:

select *
from tableA
where last-date < '"+ConvDate.convertDate(context.dateComparaison)+"';

PROBLEM : no row.


---------------------------------------------
OTHER test with tFlowToIterate

tPostgresqlInput--(row1)-->tFlowToIterate--(Iterate)-->tMySqlInput



tPostgresqlInput component :
Only one column in the schema, the date with the pattern "yyyy-MM-dd"

Code:

"select date from \"HIST_CHGT\" where \"type_msge\" = 'end' and \"msge\" = 'success'
order by \"date\" DESC LIMIT 1
"

tMysqlInput component :

Code:

select *
from tableA
where last_acti >'"+TalendDate.formatDate("yyyy-MM-dd",row1.date)+"';

result :
All the lines are loaded.

------
Tue Sep 09 16:06:26 CEST 2008                     --> System.out.println(row1.date);
2008-09-09                                                    --> System.out.println(TalendDate.formatDate("yyyy-MM-dd",row1.date))
1|tata|1|3||2004-11-15 13:49:16.0|2004-12-15                --> The last_acti field is the last of the line
... all the lines

Any idea ?
Do you have an other method ???
THANKS A LOT.

Board footer

Powered by FluxBB