We have a requirement to insert a string with the format yyyy.MM.dd.HH.mm.ss in to oracle db with column of data type “DATE” . We are doing a batch insert and this column is a part of primary key of the table . So we need to insert exact string in the DB to ensure this.Please help us in this . Any pointers would be help ful.
Locale locale = (Locale)IDataUtil.get(idatacursor, “locale”);
String p = (String)IDataUtil.get(idatacursor, “pattern”);
String v = (String)IDataUtil.get(idatacursor, “value”);
Date date = null;
if (v == null) {
date = new Date();
}
else {
SimpleDateFormat format = null;
if (p == null)
format = new SimpleDateFormat();
else {
if (locale == null)
format = new SimpleDateFormat(p);
else
format = new SimpleDateFormat(p, locale);
}
date = format.parse(v, new ParsePosition(0));
}
IDataUtil.put(idatacursor, “date”, date);
idatacursor.destroy();
There is absolutely no need to use a Java service for this formatting. Use pub.date:dateTimeFormat
But I have to wonder why that specific format is necessary? Is that the default format of the DB? If so, that’s kind of odd.
Since the column is DATE column I’d simply use a java.util.Date object. No need to mess with string formatting, except to parse a string to get the Date object. For this you’ll need a Java service:
[highlight=java]IDataCursor idc = pipeline.getCursor();
String inString = IDataUtil.getString(idc, “inString”);
String pattern = IDataUtil.getString(idc, “pattern”);
if( (pattern == null) || pattern.trim().length() == 0) )
pattern = " yyyy-MM-dd’T’HH:mm:sszz"; // W3C format as default
if( (inString != null) && (inString.length() > 0) )
{
java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat(pattern);
java.util.Date d = sdf.parse(inString, new java.text.ParsePosition(0));
IDataUtil.put(idc, “date”, d);
}
idc.destroy();[/highlight]
The likely reason that you’re seeing PK constraint errors is in how the Oracle JDBC driver and the adapter maps Date objects. Although the DATE data type column can handle time portions, the driver and the adapter (depending on versions in use) can impact how the date object is handled.
The adapter will map the input java.util.Date object to a corresponding java.sql object as specified in the adapter service. Typically, the only type available is DATE. The trouble is java.sql.Date only supports the date portion, no time. That’s why you’re getting the PK constraint–the records are getting inserted with only the date portion and no time.
Depending upon the version of the driver and settings on the adapter connection, you can get TIMESTAMP in the SQL Type list for insert/update operations.
If you cannot get TIMESTAMP available in the list (which is compatible with DATE column), then you can use a Custom SQL template where you can select the data type you want, including TIMESTAMP. The downside there is you have to do much more manual configuration and batch operations are not supported.
All that said, it may be easier to pass a string with the appropriate date/time format and let the DB worry about the parsing. The key is in configuring the adapter service to pass the string to the DB and not a java.util.Date/java.sql.Date. Whether or not you can do that in the insert/update templates I think again depends upon the JDBC driver version and the adapter version/fixes.