Support ORACLE date type problems

CloverDX Platform
1

Support ORACLE date type problems.
SQL> DESC K3;
Name Type Nullable Default Comments
-— --------- -------- ------- --------
C1 NUMBER(4)
C2 DATE Y

SQL> DESC K4;
Name Type Nullable Default Comments
-— --------- -------- ------- --------
K1 NUMBER(4)
K2 DATE Y

SQL> select c1,to_char(c2,‘yyyy-mm-dd hh24:mi:ss’) from k3;

C1 TO_CHAR(C2,'YYYY-MM-DDHH24:MI:
----- ------------------------------
1 2007-09-24 00:00:01
2 2007-09-24 12:00:00
3 2007-09-29 00:00:01
1 2007-09-24 00:56:01
2 2007-09-24 22:54:00

test.grf file context:

<?xml version="1.0" encoding="gb2312"?>

test133_DB_OUTPUT_TABLE_0_0__MetaData.fmt file context:

<?xml version="1.0" encoding="gb2312"?>

run test.grf after , Results have not hours minutes seconds

SQL> select k1,to_char(k2,‘yyyy-mm-dd hh24:mi:ss’) from k4;

K1 TO_CHAR(K2,'YYYY-MM-DDHH24:MI:
----- ------------------------------
1 2007-09-24 00:00:00
2 2007-09-24 00:00:00
3 2007-09-29 00:00:00
1 2007-09-24 00:00:00
2 2007-09-24 00:00:00

2

Could you pls send log from execution?

3

2007-9-27 11:15:56 org.jetel.component.DBOutputTable$OracleAutoKeyGenerator prepareStatement
WARN: Columns are null
2007-9-27 11:15:56 org.jetel.component.DBOutputTable$OracleAutoKeyGenerator prepareStatement
INFO: Getting generated keys switched off !
2007-9-27 11:15:56 org.jetel.graph.TransformationGraph init
INFO: Initializing DB connection:
2007-9-27 11:15:56 org.jetel.graph.TransformationGraph init
INFO: DBConnection driver[oracle.jdbc.driver.OracleDriver]:url[jdbc:oracle:thin:@192.168.1.133:1521:orcl133]:user[zking] … OK
2007-9-27 11:15:56 org.jetel.graph.TransformationGraph run
INFO: Starting WatchDog thread …
2007-9-27 11:15:56 org.jetel.graph.runtime.WatchDog run
INFO: Thread started.
2007-9-27 11:15:56 org.jetel.graph.runtime.WatchDog run
INFO: Running on 2 CPU(s) max available memory for JVM 260160 KB
2007-9-27 11:15:56 org.jetel.graph.Phase init
INFO: [Clover] Initializing phase: 0
2007-9-27 11:16:07 org.jetel.component.DBOutputTable$OracleAutoKeyGenerator prepareStatement
WARN: Columns are null
2007-9-27 11:16:07 org.jetel.component.DBOutputTable$OracleAutoKeyGenerator prepareStatement
INFO: Getting generated keys switched off !
2007-9-27 11:16:07 org.jetel.graph.Phase init
INFO: [Clover] phase: 0 initialized successfully.
2007-9-27 11:16:07 org.jetel.graph.runtime.WatchDog executePhase
INFO: Starting up all nodes in phase [0]
2007-9-27 11:16:07 org.jetel.graph.runtime.WatchDog executePhase
INFO: Sucessfully started all nodes in phase!
2007-9-27 11:16:29 org.jetel.graph.runtime.WatchDog watch
INFO: Execution of phase [0] successfully finished - elapsed time(sec): 21
2007-9-27 11:16:29 org.jetel.graph.runtime.WatchDog$PrintTracking printProcessingStatus
INFO: ----------------------** Final tracking Log for phase [0] **---------------------
2007-9-27 11:16:29 org.jetel.graph.runtime.WatchDog$PrintTracking printProcessingStatus
INFO: Time: 27/09/07 11:16:29
2007-9-27 11:16:29 org.jetel.graph.runtime.WatchDog$PrintTracking printProcessingStatus
INFO: Node Status Port #Records #KB Rec/s KB/s
2007-9-27 11:16:29 org.jetel.graph.runtime.WatchDog$PrintTracking printProcessingStatus
INFO: ----------------------------------------------------------------------------------
2007-9-27 11:16:29 org.jetel.graph.runtime.WatchDog$PrintTracking printProcessingStatus
INFO: DB_OUTPUT_TABLE_0 FINISHED_OK
2007-9-27 11:16:29 org.jetel.graph.runtime.WatchDog$PrintTracking printProcessingStatus
INFO: %cpu:… In:0 5 0 1 0
2007-9-27 11:16:29 org.jetel.graph.runtime.WatchDog$PrintTracking printProcessingStatus
INFO: DB_INPUT_TABLE_0 FINISHED_OK
2007-9-27 11:16:29 org.jetel.graph.runtime.WatchDog$PrintTracking printProcessingStatus
INFO: %cpu:… Out:0 5 0 1 0
2007-9-27 11:16:29 org.jetel.graph.runtime.WatchDog$PrintTracking printProcessingStatus
INFO: ---------------------------------** End of Log **--------------------------------
2007-9-27 11:16:29 org.jetel.graph.runtime.WatchDog run
INFO: Forcing garbage collection …
2007-9-27 11:16:34 org.jetel.graph.runtime.WatchDog printPhasesSummary
INFO: -----------------------** Summary of Phases execution **---------------------
2007-9-27 11:16:34 org.jetel.graph.runtime.WatchDog printPhasesSummary
INFO: Phase# Finished Status RunTime(sec) MemoryAllocation(KB)
2007-9-27 11:16:34 org.jetel.graph.runtime.WatchDog printPhasesSummary
INFO: 0 FINISHED_OK 21 10506
2007-9-27 11:16:34 org.jetel.graph.runtime.WatchDog printPhasesSummary
INFO: ------------------------------** End of Summary **---------------------------
2007-9-27 11:16:34 org.jetel.graph.TransformationGraph run
INFO: WatchDog thread finished - total execution time: 38 (sec)
2007-9-27 11:16:34 org.jetel.graph.TransformationGraph run
INFO: Graph execution finished successfully

4

Hello,
it could happened with old CloverETL versions. Pls download the newest version (2.3) and try again.

5

Hi hwhwhw :slight_smile:

did you solve your problem ? (I think I encounter the same one)

6

I now use is Version 2.3,But there remains the problem.

7

That’s also what I see here…

Agata, should we open a bug report ?

8

The time is truncated, because most of databases treats DATE type as only date; it is used TIMESTAMP type for storing date and time. I’m not sure if usage of Timestamp class couldn’t cause problems in other databases. Log it to bug tracker pls.

9

I have submited a bug report :

http://bugs.clovergui.net/view.php?id=194

10

Thanks, we will try to solve this issue.

Martin

11

Bug 194 is a fairly big deal for us, as we store the date and time of the entry and modification of each object. Looking at the code, the T (datetime) data type is there but not hooked up. Is the fix for this imminent, or should I add the code myself at this point?

12

This bug was fixed in the last release (2.3.2). If you set requested format string on metadata no lost should occur.