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"?>
<Graph name="hwTesting">
<!--hw write-->
<Phase number="0">
<Edge id="TASK_EDGE_0" fromNode="DB_INPUT_TABLE_0:0" toNode="DB_OUTPUT_TABLE_0:0" metadata="test133_DB_OUTPUT_TABLE_0_0__MetaData" />
<Node id="DB_OUTPUT_TABLE_0" type="DB_OUTPUT_TABLE" dbTable="K4" dbFields="K1;K2" dbConnection="testorcl133" maxErrors="0" />
<Node id="DB_INPUT_TABLE_0" type="DB_INPUT_TABLE" dbConnection="testorcl133" sqlQuery="select c1, c2 from k3"/>
</Phase>
<Global name="hwTesting">
<Metadata id="test133_DB_OUTPUT_TABLE_0_0__MetaData" fileURL="c:/test/test133_DB_OUTPUT_TABLE_0_0__MetaData.fmt"/>
<Connection id="testorcl133" dbConfig="c:/test/testorcl133.conf" type="JDBC"/>
</Global>
</Graph>
test133_DB_OUTPUT_TABLE_0_0__MetaData.fmt file context:
<?xml version="1.0" encoding="gb2312"?>
<Record name="K4" type="delimited">
<Field name="K1" type="numeric" format="" nullable="false" default="" size="4" length="4" scale="0" delimiter=";"/>
<Field name="K2" type="date" format="yyyy-MM-dd HH:mm:ss" nullable="true" default="" size="0" length="0" scale="0" delimiter="\n"/>
</Record>
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
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"?>
<Graph name="hwTesting">
<!--hw write-->
<Phase number="0">
<Edge id="TASK_EDGE_0" fromNode="DB_INPUT_TABLE_0:0" toNode="DB_OUTPUT_TABLE_0:0" metadata="test133_DB_OUTPUT_TABLE_0_0__MetaData" />
<Node id="DB_OUTPUT_TABLE_0" type="DB_OUTPUT_TABLE" dbTable="K4" dbFields="K1;K2" dbConnection="testorcl133" maxErrors="0" />
<Node id="DB_INPUT_TABLE_0" type="DB_INPUT_TABLE" dbConnection="testorcl133" sqlQuery="select c1, c2 from k3"/>
</Phase>
<Global name="hwTesting">
<Metadata id="test133_DB_OUTPUT_TABLE_0_0__MetaData" fileURL="c:/test/test133_DB_OUTPUT_TABLE_0_0__MetaData.fmt"/>
<Connection id="testorcl133" dbConfig="c:/test/testorcl133.conf" type="JDBC"/>
</Global>
</Graph>
test133_DB_OUTPUT_TABLE_0_0__MetaData.fmt file context:
<?xml version="1.0" encoding="gb2312"?>
<Record name="K4" type="delimited">
<Field name="K1" type="numeric" format="" nullable="false" default="" size="4" length="4" scale="0" delimiter=";"/>
<Field name="K2" type="date" format="yyyy-MM-dd HH:mm:ss" nullable="true" default="" size="0" length="0" scale="0" delimiter="\n"/>
</Record>
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
-
Could you pls send log from execution? -
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 -
Hello,
it could happened with old CloverETL versions. Pls download the newest version (2.3) and try again. -
Hi hwhwhw :)
did you solve your problem ? (I think I encounter the same one) -
I now use is Version 2.3,But there remains the problem. -
That's also what I see here...
Agata, should we open a bug report ? -
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. -
I have submited a bug report :
http://bugs.clovergui.net/view.php?id=194 -
Thanks, we will try to solve this issue.
Martin -
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? -
This bug was fixed in the last release (2.3.2). If you set requested format string on metadata no lost should occur.
Please sign in to leave a comment.
Comments 11