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);
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.
Hope this helps.