database Date insert fails

Hi,

I have two date columns in DCR and trying to deploy the same to database columns for datatype1 and datatype2.
For datatype1 it works fine but fails for datatype2.

datatype1- log -

DBD: db-datetime-format is not specified for [EXPIRE_DATE]. Using default converter.
DBD: Converted JDK DateFormat [MM/dd/yyyy] to Oracle DateFormat [MM/DD/YYYY].
DBD: db-datetime-format is not specified for [PUBLISH_DATE]. Using default converter.
DBD: Converted JDK DateFormat [MM/dd/yyyy] to Oracle DateFormat [MM/DD/YYYY].

datatype2- log -

DBD: WARNING:Value for [EFF_END_DATE] exceeds column width [ 10 > -1]
DBD: Column EFF_END_DATE not found in array
DBD: Column: EFF_END_DATE, literal: 03/19/2012, Index: 4,Converting '...........' to LONGVARCHAR
DBD: Column EFF_START_DATE not found in array
DBD: WARNING:Value for [EFF_START_DATE] exceeds column width [ 10 > -1]
DBD: Column EFF_START_DATE not found in array
DBD: Column: EFF_START_DATE, literal: 03/19/2012, Index: 5,Converting '...........' to LONGVARCHAR

DBD: *******STACK TRACE*************
DBD: ERROR:
java.sql.SQLException: ORA-01843: not a valid month

The date is not getting converted as in datatype1. and even the column width is taken -1 from somewhere... not able to trace the exact error.

These columns have been added newly to the table previously it was working fine but with the new cols it doesn't.......

for two more newly added cols getting an error ---

DBD: WARNING:Value for [DECOMMISSION] exceeds column width [ 1 > -1]
DBD: Column DECOMMISSION not found in array
DBD: Column: DECOMMISSION, field: GenSet/0/MainContainer/0/DecommissionFlag/0, Index: 2,Converting '...........' to LONGVARCHAR
DBD: Column IS_DEALER not found in array
DBD: WARNING:Value for [IS_DEALER] exceeds column width [ 1 > -1]
DBD: Column IS_DEALER not found in array
DBD: Column: IS_DEALER, field: GenSet/0/MainContainer/0/IsDealer/0, Index: 3,Converting '...........' to LONGVARCHAR

These fields has values 'Y' or 'N' and the config file is -

column name="DECOMMISSION" data-type="VARCHAR2(1)" value-from-field="GenSet/0/MainContainer/0/DecommissionFlag/0" allows-null="yes" is-url="no" />
column name="IS_DEALER" data-type="VARCHAR2(1)" value-from-field="GenSet/0/MainContainer/0/IsDealer/0" allows-null="yes" is-url="no" />
column name="EFF_END_DATE" data-type="DATE" value-from-field="GenSet/0/expirationdate/0" data-format="MM/dd/yyyy" allows-null="yes" is-url="no"/>
column name="EFF_START_DATE" data-type="DATE" value-from-field="GenSet/0/publishdate/0" data-format="MM/dd/yyyy" allows-null="yes" is-url="no"/>

All this configuration and exact data works on dev env for both the datatypes but on other env it doesn't

Not able to trace if there is any configuration issue from DB or TS.
Smiley Sad

Find more posts tagged with

Comments

MSelvan

Mention your db-date-time format using db-datetime-format attribute in DB schema.

Ex:
db-datetime-format="yyyy-MM-dd HH:mm:ss.S"

MSelvan

Some thing like the below:

column name="CREATED_DATE" data-type="DATETIME" data-format="MM/dd/yyyy hh:mm:ss a" db-datetime-format="yyyy-MM-dd HH:mm:ss.S" value-from-field="Content/0/dateTimeHidden/0" allows-null="yes" is-url="no"/>

Shilpax

Tried the same... did not help.... Smiley Sad

MSelvan

As per log your DB Date format seems to be DD/MM/YYYY [03/19/2012].Try with the following one to avoid java.sql.SQLException: ORA-01843: not a valid month

column name="EFF_END_DATE" data-type="DATE" value-from-field="GenSet/0/expirationdate/0" data-format="MM/dd/yyyy" db-datetime-format="DD/MM/yyyy" allows-null="yes" is-url="no"/>

Shilpax

Making this change did not help me...
as I mentioned it is working for some other template with same data and config details... but not for this one.. Smiley Sad

Adam Stoller

When you changed the schema for the DB - did you drop and rebuild the table? If not, I suggest you try that.

Shilpax

The tables are owned by some other team and have been just altered to have new cols.
Will try for this option and get back to you.

Shilpax

Attaching here with the whole log file.

Note- all this config files and all worked fine in dev env where tables were just altered and not re created.

saravanaprasad

Hi,

Try using the below to address the conversion issues.

to correct the problem add this line to dbschema.cfg

column name="START_DATE" data-type="DATETIME" value-from-field="TeamSite/Metadata/START_DATE" data-format="yyyy-MM-dd HH:mm zzzz" db-datetime-format="yyyy-MM-dd HH:mm:ss" allows-null="yes" is-url="no

notice data-format="yyyy-MM-dd HH:mm zzzz" uses Zone but the db-datetime-format="yyyy-MM-dd HH:mm:ss" does not. The current API does not convert the zone format currently.

You can also obtain a description of the SimpleDateFormat Java class from the Sun Java
API documentation Web site:
http://java.sun.com/j2se/1.3/docs/api/java/text/SimpleDateFormat.html