Jdbc Adapter Data types Mapping

, ,
1

Hi All
I am working on jdbc adapter (wm6.1),the problem here is jdbc data types,my oracle Database is having date coloum with DATE as date type
and i can insert the date in following way yyyy-MM-dd hh:mm:ss (Date data type)
but i have in my requirement is dd/mm/yyyy hh:mm:ss AM/PM
when i am not able to change JDBC data type for that (i.e TIMESTAMP data type).
second question is how can i append AM/PM using JDBC adapter .
any suggestions will be highly appreciated
thanks
bye

2

Kitt,

Use this dateformat which will solve your problem along with (AM/PM)
dd/MM/yyyy HH:mm:ss a (‘a’ will give you whether it is AM/PM automatically)

And for changing the dateformat use builtin service (pub.date:dateTimeFormat)service (set inputs currentPattern,newPaterrn.
So map this new dateformat to your AdapterService/Field.

HTH,
RMG

3

Thanks RMG,

I already used those services to get the correct format for
date and time in webMethods but when we map input values through JDBC adapter of the format dd/MM/yyyy HH:mm:ss a its giving me the error
and when i am giving it in format yyyy-MM-dd hh:mm:ss its correctly inserting those values .is there any other way to insert date in that format or we need to write java code for that.

Regards,

4

Kitt,

This change should resolve your problem,

In your insert JDBC Adapter Service set the “InputFieldType” option for Date that you are mapping,there is an option to use (java.lang.String).So this Date string will take any format and internally your DB receives the Dateformat as expected.

And map your new dateformat to the AdapterService/DateField(String)

HTH,
RMG.

5

Thanks RMG for the quick response,

I guess I already did that before,i already changed the Data type
with Column variable DATED with input Field Type is JAVA.lan.string.

I just got one more Question If You can help me out with this
i inserted those dates in format yyyy-mm-dd hh:mm:ss.fffffffff
i am able to extract that values with respect to yyyy-mm-dd
SQL Select * from Table_name Where t1.DATED= DATE’2004-08-25’
is working fine.
But if i need to extract these value depend upon the time values hh:mm:ss same logic is not working .
Select * from Table_name Where t1.DATED= DATE’2004-08-25 12:12:12.fffffffff’

I will appreciate.
Thanks.

6

Kitt,

If you want query depends on date,time values then your sql statement syntax should be this way.
Select * from Table_name Where DATED= to_date(‘2004-08-25 12:12:12’,‘yyyy-mm-dd hh:mi:ss’)

So you can test this statement using JDBC Adapter Custom/DynamicSQL option or use some other SQL client.It will work.

HTH,
RMG

7

Thanks RMG for that to_date function, its working fine.
I need to figure out the first problem now .

I will appreciate your help.

8

Thanks RMG for that to_date function, its working fine.
I am still researching on that DATE problem.

Thanks.

9

Glad it worked,
And I think your first problem means new dateformat,AM/PM problem sounds solved right based on my posts and your responses.

goodluck,

10

hi,
The first problem is not yet solved, I checked my flow service
after changing for the Column variable DATED with input Field Type is JAVA.lan.string. i still facing the same problem

the error is ::-

com.Wm.app.b2b.server.ServiceException[ART.117.4002]
Adapter Runtime(Adapter Service):
[ADA.1.314] Cannot set data for the input field DATED
Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff

i hope u got my point
thanks.

11

Kitt,

This error indicates that you are not mapping the correct date format.so please check in the mapping before invoking the Adapter Service and see whether the dateformat is messingup in the pipeline .

HTH,
RMG

12

If not test inserting the data manually providing with right format by running the AdapterService and see if it throws the same error.
So if everything is fine then some thing in messed up in the mapping itself before invoking Insert Adapter Service.

Give it a shot.

13

thanks for replying ,
I am running the Adapter service just by giving the
manual data I gave Date format yyyy-mm-dd hh:mm:ss this service is working but not for the other format, Input has to be in this above format.
if i give this as an input to JDBC adapter service for DATED variable
"yyyy-mm-dd hh:mm:ss a "for (Am/Pm) its not recognizing “a” (giving error).I assume that there there is some other way to do that I think some thing has to do with ‘fffffffff’ got any idea?

"yyyy-mm-dd hh:mm:ss.ffffffff "
bye.

14

Kitt,

If you are inserting with dateformat yyyy-mm-dd hh:mm:ss then there is no need of specifying AM/PM,since the Oracle itself will take care of adding AM/PM stamp for DataType ‘Date’ when you insert the values.

I tested here the same and checked in the DB for Datefield.

So why you need to explicitly map the AM/PM stamp?

Regards,

15

HI,
yeah that true ,Oracle will take care of AM/Pm but when i give
time for example 4:50:50 its by default taking it as 4.50.50 AM
instead of PM so I think Oracle has to be set up correctly or its some other issue.
Did you got the correct AM/PM stamp ,and did you try to give different format other then date format yyyy-mm-dd hh:mm:ss .

Thanks.

16

Kitt,

yes i did tried giving the date format with
yyyy-mm-dd hh:mm:ss with AM/PM stamp from the adapter service,i seen the same error which you are getting.And after sometime i got that Oracle itself is putting AM/PM stamp in the Date FieldType after inserting.

May be you have to take this to your oracle folks and investigate on this problem 4.50.50 AM showing instead of PM.

HTH,
RMG

17

Thanks for your time!!
i will investigate over it.

18

Hi ,
I want to format date from YYYYMMDD to YYYY-MM-DDThh:mm:ss.sTZD.
I using dateTimeFormat service by providing current pattern and new pattern but I am getting error. Could anyone can help.
Thanks in advance.
sreenivas

19

Hi All
I am working on jdbc adapter (wm4.6), the problem here is jdbc data types, my oracle Database is having date column with DATE as date type and I can not insert the date and time in any format. I tried yyyy-MM-dd hh:mm:ss (Date data type) and many different patterns but I can not insert date and time in the same Oracle filed. The error I got is:

java.sql.SQLException: ORA-01830: date format picture ends before converting entire input string

Can anyone please help me out?

Thanks in advance