JDBC StoredProcedureService Problem

, ,
1

Hi,

 Anybody Pl help me when i am trying to execute one stored procedure using JDBC Adapter.I got below error.My storedProcedure

needs StartDate,EndDate parameters,DateTimeStamp.when i executed this
storedProcedure using call service in wmDB package,It is working fine.
There i passed date as dd-MMM-yyyy format.Input parameter Type of storedProcedure for startDate,EndDate is DATE.
Here in JDBC Adapter i am passing Startdate,EndDate in dd-MMM-yyyy(eg 25-MAY-2003).AdapterService not recognising MAY.
ART Exception is MAY.If i passed date as yyyy-MM-dd format(eg.2003-05-25).Adapter service throwing below exception.

2003-08-21 17:17:36 EDT WmSriniSamp:Test com.wm.pkg.art.error.DetailedServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service WmSriniSamp:Test[ADA.1.316] Cannot execute the SQL statement “{call PA.PKGTESTPUBLISH.TESTTRADEPUBLISH (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }”. " (17041) Missing IN or OUT parameter at index:: 6" Missing IN or OUT parameter at index:: 6 Stack trace data … 70eacc6e1558b298f7a44e9018 NULL 70eacc6e1558b298f7a44e9018
2003-08-21 17:17:36 EDT WmSriniSamp:Test[SCC.0121.0026E] delisted transaction commit failed: javax.transaction.RollbackException: Already marked for rollback xid = pc20002836b/1061495075401 Stack trace data … 70eacc6e1558b298f7a44e9018 NULL 70eacc6e1558b298f7a44e9018
2003-08-21 17:17:36 EDT WmSriniSamp:Test[ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service WmSriniSamp:Test [ADA.1.316] Cannot execute the SQL statement “{call PA.PKGTESTPUBLISH.TESTTRADEPUBLISH (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }”. " (17041) Missing IN or OUT parameter at index:: 6" Missing IN or OUT parameter at index:: 6 Stack trace data … 70eacc6e1558b298f7a44e9018 NULL 70eacc6e1558b298f7a44e9018
2003-08-21 17:17:36 EDT WmSriniSamp:Test
[ADA.1.316] Cannot execute the SQL statement “{call PA.PKGTESTPUBLISH.TESTTRADEPUBLISH (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }”. " (17041) Missing IN or OUT parameter at index:: 6" Missing IN or OUT parameter at index:: 6 Stack trace data … 70eacc6e1558b298f7a44e9018 NULL 70eacc6e1558b298f7a44e9018
2003-08-21 17:17:36 EDT WmSriniSamp:Test
Missing IN or OUT parameter at index:: 6

Thanks,
SriniK

2

hi srinik, the jdbc driver can’t use the date format as “dd-MMM-yyyy” for oracle and the stored procedure in webMethods only use the string type as parameter in or out.you may make a string such as “2003-08-12” as your parameter ,and in your procedure format the string with to_date(‘2003-08-12’,‘yyyy-MM-dd’)

3

Hi zhagwei,

 Thanks for your reply.I applied developer servicePack1 on my m/c.

Earlier i didn’t applied any service packs on My localbox(individual Dev box).But our admins applied sp1,sp2 on Dev(combined dev env),test,staging,Prod environments.Now I applied all service packs
on my local m/c.Now I changed my ParamJDBCType parameter in my service
for startDate,endDate as Varchar.In my storedProcedure these
parameters are Date type.Now I passed date in the format(dd-MMM-yyyy).
My storedProcedure service is working.

–sriniK

4

hi srinik,would you tell me how did you do to passed the date in the format(dd-MMM-yyyy),I’ve been try to do this but failed util use another format. what’s the version of your DB and jdbc? thank you

5

Hi zhangwei,

       Today I saw your posting.Here I am using IS6.0.1 with Patches

SP!,SP2,developerSP1.In my database date(startDate,EndDate) fields are
of type DATE.If used DB call service,It is working fine for me if I pass Those dates are dd-MMM-yyyy(eg. 01-AUG-2003)format.In case
If I use JDBC Adapter StoredProcedure Service,If i have taken StartDate,EndDates are of type(VARCHAR in Param JDBC Type)after clicking parameter tab of JDBC Adapter Service.Now you pass Those
Dates are of type dd-MMM-yyyy(ex 01-AUG-2003).This works fine for me.
Try it.If you applied Service packs then only it works.

—SriniK

6

Hey Srini,

Check the permissions those may cause the same Error.