Hi,
I am integrating AS400 to Oracle. I am using WmDB package from AS400 to webMethods and Jdbc adapter from webMethods to Oracle. My problem is, 3 fields which I extract from AS400 are String fields which contain date,for example hire_date etc., Now I have to insert this field into a date field on Oracle. Is there any service like String to Date in webMethods. I am using Integration Server/Developer 6.0.1 and Windows NT. Pls…help me…!!!
Thanks,
Lavanya

you can do with in JDBC templage something like to_date(“MM-DD-YYYY”, ?)
for hire_date column

Hi Ashesh,
Ya tried to_date, but I am querying against DB2 table. to_date is not supported in DB2. I also tried something like this DATE(‘SUBSTRING(HIRE_DATE,5,2) || “/” || SUBSTRING(HIRE_DATE,7,2) || “/” ||
SUBSTRING(HIRE_DATE,1,4)’) HIRE_DATE
But not working
pls…help…

Thanks,
Lavanya

Hi lavanya,

which format your hire date coming from dB2?If you pass this date 

into Oracle you have to convert dd-MMM-yyyy HH:mm:ss(format).for that
purpose there is one built in service wmPublic folder dateTimeFormat.
if you passing this date to JDBC adapter service you have to convert yyyy-MM-dd HH:mm:ss.S(S is optional).Then also you can use above service.This helps you.

Thanks,
SriniK

Hi lavanya,

   In jdbc adpapter service there is no need of converting from  

string to date.you can pass proper format.In your insert template
you have to select InputFieldType is java.lang.String Expression as ?
than map wmDB service output hiredate string after using WmPublic.date
:dateTimeFormat service.

Thanks,
SriniK

Hi Srini,
Thanks for your response. I was trying like that. But when I select the data to check whether my insert is working fine or not, i get NULL for all the date fields. It means that the String which I am entering is not being inserted into the Date Field. What Can I do now?
Pls…help me…
Thanks,
Lavanya

Hi lavanya,

   you check using step through.Before that you have to debug first 

using saveToPipeline,ExitFlow,RestorePipeline.Enable saveToPipeline,
ExitFlow.Run your wmDB service.After that disable saveToPipeline,ExitFlow.Now enable restorePipeline.Go through your
JDBC Insert service.If it fails go to catch block.Then see error
in getLastError service.Before that you have to check your JDBC
insert service passing one hireDate in the format yyyy-MM-dd HH:mm:ss.
if it works,Then you can go thorugh check using above process.

Thanks,
SriniK

Hi Srini,

The main problem is when I insert into Oracle. I wrote an adapter service which inserts the data into Oracle. The output I get from publish is a String which contains a date. I must insert this date into the Date field of Oracle. It is throwing an exception when I am trying to insert. I must convert the String into Date before inserting. Please do help me. I have debugged everything. Everything is fine…I am able to publish…I am not able to insert the data into Oracle…Hope I am clear with my problem.
Thanks,
Lavanya

Hi Lavanya,

    You have created adpter service using JDBC(InsertTemplate). 

Here i am giving you steps for JDBC AdapterService.

1)You have created AdapterService using InsertSQL template
2)select Schema.tableName.
3)select insert button.
4)select pallatiser at right side.choose columns from pop menu.
5)select expression as ? top window.
6)select inputTypeField as java.lang.String.
test this service passing some value.
Here i tested from my machine.It is working.
Here I am using Sun JVM not IBM.I applied wb601sp1developer fix.

Thanks,
SriniK

I am really trying hard in all possible ways to insert the data into Oracle. I am not able to insert the date fields. All the other fields are being inserted. Plse… HELP !!!

Thanks,
Lavanya

Hi lavanya,

Sorry Pallatiser is left side.One question do you have permissions
to insert into database.Make sure it.

Thanks,
Srinik

Hi Srini,

Yes I do have all the permissions to insert, select and delete data in the Oracle. What else can I do? Please do let me know anything which you can think of about this issue.

Thanks,
Lavanya

Hi lavanya,

 what exception you are getting?Is it your cursor going to catch 

block?did you tested your adapterservice manually passing values at run time?if yes what format you are passing date into data base.Try with
selecting ResultFieldType as java.lang.String.Otherwise open ticket
to webMethods support.

SriniK

Hi Srini,
Now there is some network problem in my company and I am not able to get into the Development Box. I can only test my service after I get into it. Very well, after I have created the insert service I have tested it and it was working fine. I have developed other interfaces also and it worked well. In this partcular interface am facing this problem. I will start trying again once the Network is up and if am not able to get then will open a ticket with tech support. If you get any ideas pls…do let me know. Any help would be greatly appreciated.
Thanks,
Lavanya

Hi lavanya,

               Now I tested several times using insert template  

my data going.I didn’t have any problem with JDBC Adapter.I hope your
problem may be small.You will get your solution after your network up.
Pass us after you got your solution.

sriniK

Sure Srini,
Will definetely let you know once the network is up. Hopefully it works here too.
Crossing my fingers …!!!

Thanks,
Lavanya

Hi Srini,

This thread really worked. You may have a look at it.
[url=“wmusers.com”]wmusers.com
Thanks,
Lavanya

Hi Lavanya,

    If you are using JDBCAdapter service there is no need to write 

Custom java service.I Passed so many times out put of wmDB service
to JDBCAdapter input template.In my data base column is Date type
I have Created Insert template in that If you click on Insert button
you will get 2 half window.Top window it contains column,columnType,
Expression.Bottom half window contains column,columnType,inputField,
inputFieldType.Here in the bottom window you have to take inputFieldType java.lang.String (not java.util.Date or java.sql.TimeStamp).It worked for me.Now also it is working.If
not works,then either you have to write custom Java service or
apply fix from webMethods.How ever your case your problem solved.
Thanks for reply.

           Recently I faced Problem.I worked on 6.0(fcs version). 

I developed some notifications (oct’02).That time they worked fine.
recently my Administrator installed patches(fix_1,fix_2 on testing,
staging,Prod environments).On my localhost i am applied any patches.
when I try to create some new notifications on test environment
it is not allowing me to do.Then we contacted webMethods they
released patch for developer (wb601sp1developer).I thought your
problem also same as me.This is the reason i advised you to contact
webMethods.

Thanks,
SriniK

Thanks for the information Srini. It was very informative

Lavanya