Error in JDBC call :Could not execute statement for table/stored proc

[Former Member]

Hello All,

Scenario: Proxy to JDBS synchronous scenario.

My scenario is I am calling a stored procedure in receiver adapter to return back no of records in the table for particular date range

Error Message: Could not execute statement for table/stored proc. "Get_count_record" (structure "InsertStatement") due to java.sql.SQLException: Unsupported parameter type 'datetime' for parameter 'FROM_DATE' found

<InsertStatement>

<AuditTable action="EXECUTE">

<table>Get_count_record</table>

<INTERFACE_TYPE hasQuot="no" ISINPUT="true" TYPE="integer">1</INTERFACE_TYPE>

<FROM_DATE hasQuot="no" ISINPUT="true" TYPE="datetime">2010-08-19

14:28:38.000</FROM_DATE>

<TO_DATE ISINPUT="true" TYPE="datetime">2010-08-19 14:28:38.000</TO_DATE>

</AuditTable>

</InsertStatement>

[stefan_grube]

>: Unsupported parameter type 'datetime' for parameter 'FROM_DATE' found

Check in your database, how exactly the type of the parameter is spelled and use same spelling in XML.

[Former Member]

Hi Stefen, already checked the database (SQL server)and the datatype name is datetime which is same as i have used.

[stefan_grube]

I have checked the stored procedures in our system (MS SQL), we do not use datetime.

We do following: we declare a parameter as varchar, in the stored procedure we have this statement:

update table set

DateField = cast(@DateField as datetime)

Maybe you could do similar.

[rodrigoalejandro_pertierr]

as stefan sugguest. seems there is a bug with the types. in others implementation the solution we apply together with DB team was define the all fields type date or datetime as varchar.

[former_member241146]

Hi.

The FROM_DATE format is incorrect, maybe you should use a concatenation with Value + DB Transformation Function.

You can retrieve the TYPE for this field, after this do as following:

-> Concatenate DB Function (Like dataTransf(DD/MM/AAAA), or DATA_TRANS(dd/mm/aaaa)) plus value. The result is :

dataTransf(value,"DD/MM/AAAA").

-> Map the result to the TARGET FROM DATE in the Message Mapping.

Regards.

Bruno.

[rodrigoalejandro_pertierr]

Hi Ravijeet,

This error is beacuse of the mismatch in the format.Check in the database what is the type that is defined for FROM_DATE'.It might not be dateTime.

Please do also check if SQL accepts the type as dateTime.

These are the reason why it throwing the exception "Unsupported parameter type 'dateTime' for parameter FROM_DATE' found".

Trying to change the type to xsd:date in XI and execute it.Please do also take care the format inw hich u send the date.

Please do let me know if u need furthur information.

Edited by: Rodrigo Alejandro Pertierra on Sep 22, 2010 2:26 PM