JDBC Adaper throwing error

[Former Member]

Dear All,

I have created a JDBC to JDBC scenario. Theunderlying oracle table has both Date and Number sin it. The sender sends the data, but when the receiver tries to create the record in the table, it throws the following exception.

literal does not match format string

Any idea why is it giving problem? Should we specifically do something during the data type creation or during mapping to make the date format acceptable to oracle underlying database? Do we have to do the same thing for the integer as well?

Your earliest help is highly appreciated.

Thank You,

Mansoor.

[Former Member]

Hi Mansoor,

The XML data type should be xsd:string

In the mapping, simply call the to_date function.

For example if your date format is YYYY.MM.DD then it should look like this:

to_date('2009.12.31','YYYY.MM.DD')

Obviously in a real life scenario you are expected to use data from the XML source, not a hard-coded date like this one. Therefore the concatenation will require the following 3 sub elements:

to_date('

<here goes the xml element value>

','YYYY.MM.DD')

Best regards

Ofer

[Former Member]

just a minor correction- I was assuming oracle DBMS, but there should be an equivalent function for every DBMS imaginable

[Former Member]

Hi,

Try inserting manually the data records at the receiver end. That should give you an idea of all the formats.

Then you can use the same data and check the payload whether anything is getting converted or not.

Regards

Krish

[Former Member]

The type field in the database and the type field of the element should be of the same? I thought if these two are of the same type, XI would automatically convert the payload to the format which underlying database understand.

Best regards,

Mansoor.

Edited by: Mansoor Ali P on Apr 16, 2009 3:12 PM

[Former Member]

Hi Mansoor,

In your target DB, have you to populate a Date or a DateTime field ?

coz one of this type (sorry I don't remind me) wait a specific format. if this one is not good, you will have your error.

is it easy to test: in your mapping use a constant with the attempted format to check.

Regards.

Mickael

[Former Member]

Thank Mickael,

So I should use a constant to send the data in the appropriate format right?

I will try that, but are you sure I can assign a constant data (which will be a string type) to an element of type xsd:date?

My main problem is after doing each of these changes I need to request the administrator to refresh the cache. That is why I am asking the above questiomn, because the administrator is not around, I need to wait for doing thi. Otherwise the changes are not getting reflected.

Best Regards,

Mansoor.

[Former Member]

Hi Mansoor,

in Data Type, personally i used "String" for the Date or DateTime and yes it was ok.

then yes, to test you can use a constant in your mapping

you can also desactivate the link in the mapping... if the target DB field is not mandatory and if NULL is accepted fy this field on DB.

Regards.

Mickael

[Former Member]

Hi!

Just compare the format of the fields in the database and in your message and I think you will see the cause

e.g. date format in database = YYYYMMDD and in your message it is MM/DD/YYYY

e.g. integer format in the database '000000nnn' and in your message 'nnn '.

Regards,

Volker