Support ORACLE date type problems.

hwhwhw

• September 27, 2007 00:00

• Answered

Follow

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

• 

  avackova

  • September 27, 2007 07:13

  0

  Comment actions Permalink

  Could you pls send log from execution?
• 

  hwhwhw

  • September 28, 2007 03:19

  0

  Comment actions Permalink

  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
• 

  avackova

  • October 01, 2007 08:40

  0

  Comment actions Permalink

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

  alci

  • October 04, 2007 18:49

  0

  Comment actions Permalink

  Hi hwhwhw :)
  
  did you solve your problem ? (I think I encounter the same one)
• 

  hwhwhw

  • October 08, 2007 03:01

  0

  Comment actions Permalink

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

  alci

  • October 08, 2007 10:10

  0

  Comment actions Permalink

  That's also what I see here...
  
  Agata, should we open a bug report ?
• 

  avackova

  • October 08, 2007 10:31

  0

  Comment actions Permalink

  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.
• 

  alci

  • October 08, 2007 12:49

  0

  Comment actions Permalink

  I have submited a bug report :
  
  http://bugs.clovergui.net/view.php?id=194
• 

  mzatopek

  • October 09, 2007 15:54

  0

  Comment actions Permalink

  Thanks, we will try to solve this issue.
  
  Martin
• 

  bmuller

  • November 15, 2007 00:38

  0

  Comment actions Permalink

  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?
• 

  avackova

  • November 15, 2007 07:56

  0

  Comment actions Permalink

  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.