inserting formated date string in oracle DB

, ,
1

Hi All,

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.

Thanks and Regards,
Nithin

2

Try a Java service :

IDataCursor idatacursor = pipeline.getCursor();

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();

3

Thanks for your reply,

I tried the java service but it behaves the same it takes only date while inserting so its throwing unique constraint exceptio…:frowning:

4

No…
The inputs should be:
value - the string date
pattern - the date pattern yyyy.MM.dd.HH.mm.ss

The output should be a date so that you can insert it into the DB as Date.

5

yes i have given the same :See below the data iam inserting :

2010.08.27.12.00.00 — first string coverted to date.
2010.08.27.12.05.00 – second string converted to date.

But it is throwing unique constraint exception .

Thanks

Nithin

6

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.

Hope this helps.

7

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.