hello, all
There is a problem when insert the Date type columns in oracle. I am using BatchInsert adapter service. When the column type is Date, i write to_char(?,‘yyyymmdd’) in the expression column and set the input data type to string, but it doesn’t work. Each time i got this error:
com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service lywConvertProc.importProc.Db:testInsert.
[ADA.1.314] Cannot set data for the input field “IISM_ETA”
Does anyone know how to solve this problem?
Thanks
nancy

PS：manager, this content was firstly posted in JMS section by mistake. Could you please help to delete that? thanks.

Is the format string you’re using correct for your database? I think Oracle wants yyyy-mm-dd.

Nancy,

I believe you’re using the wrong function. If you have a string and you want to insert it as a date, then you need the to_date function (not to_char). As Rob mentioned, however, Oracle allows you to insert a date as a string as long as you use their default format, which from what I remember is dd-MMM-yy (ex. 14-JUN-06)

• Percio

Hi Nancy,
I had the same problem while inserting date field defined as string. Once i define to_date function it worked fine, so should be case with you.
Cheers,
KK

Yes indeed it should work with to_date. let us know how it went.

Nancy,

Yet another possibility that you may want to consider is to:

1. Change the “Input Field Type” of your DATE column from java.lang.String to java.util.Date in the adapter service
2. Convert your date string to a java.util.Date object prior to invoking the adapter service

With this approach, you would not have to worry about calling the to_date function. The advantage here is that if you ever decided to move to a database that did NOT support the to_date function, you shouldn’t have to change your adapter service.

I thought for sure there was a WmPublic/pub.date service that provided this functionality (ie. convert a date string into a Date object given a certain pattern.) However, it doesn’t look like this service exists. Nevertheless, you could still easily create a Java service to accomplish this task by using the SimpleDateFormat class, for example. If you need further help, let me know.

• Percio

Hi Nancy,
IMO, I will use java only if I cannot do something in FLOW easily.

You can use the service pub.date:dateTimeFormat to format your date string in the format supported by your DB. This way you can keep the “Input Field Type” of your DATE column as java.lang.String and don’t have to use to_date or other conversion function supported by DB.

HTH,
Bhawesh.

Bhawesh,

Agreed. The problem that I’ve come across, however, is that sometimes the default format accepted by the DB only allows for month, day, and year. Therefore, if the requirement is also for the time (hours, minutes, etc.) to be inserted as part of the date, then another route must be taken.

• Percio

Hi Percio,
The output of the service pub.date:dateTimeFormat is based on the pattern one supply and so it could be dd or MM or yyyy or HH or mm or ss or any combination of these. So if the DB only allows for month, day, and year, one will only supply these things as your desired output pattern e.g. dd-MMM-yyyy or whatever…

Regards,
Bhawesh

Bhawesh,

You misunderstood what I said. Let me try to be clearer.

In Oracle, the default format for DATE is DD-MON-YY, right? Therefore, if I want to insert a date into Oracle but I also want to specify the time (HH24:MI:SS, for example), I cannot simply use the default format to insert the date as a string because, as you can see, time is not part of the default format.

Without changing the database’s default, one could still insert the time along with the date by using the to_date function. My last suggestion was just an alternative to using the to_date function, just in case Nancy was looking for one.

Thanks,
Percio

Hello all! I have problems inserting date with batchinsert in oracle database. I use java.util.Date class in batchinsert. The problem is that i cannot see time but only date. I changed the oracle’s default format with alter session command but still doesn’t work. Can anyone suggest something? Thank you in advance.

Use java.sql.Date.

I tried with java.sql.Date. First I tried in batchinsert with java.sql.date and it didn’t help. Next I tried with the service which converts a string into object but problem was that the returned object is always null ( I don’t know why ). So where to use java.sql.Date?

My mistake. I’m only half remembering the solution for this from while back. What is the data type of the column? Depending on DB driver version and IS fixes, you may or may not be able to use the timestamp data type within the JDBC adapter service. If you cannot select timestamp within the service, you may need to apply a JDBC fix and apply a setting to the connection config to get that to happen. I don’t remember the details exactly but there is info on Advantage.

Thank you for your replies. There are three columns - first one is timestamp and others are dates. I looked in advantage but still I can’t find anything which can help. I can select timestamp within the service ( for the field ) ( I use version 6.1 of integration server ).