Error during conversion of query result to XML: java.lang.ArrayIndexOutOfBoundsException: 101 error in JDBC sender channel

[former_member193393]

Hello All,

We are facing the below error in JDBC sender adapter when trying to pick records from a table. The error is as below:

Error: Error during conversion of query result to XML: java.lang.ArrayIndexOutOfBoundsException: 101

Checked the data in the table but didn't find any issues with the data i.e., the DATE fields are not null etc., We are facing this issue in prod from last couple of (2) days and everything was fine earlier. The db table structure is as given below:

Replicated recent (2 days) data to QA instance and there the same jdbc adapter was picking up the data successfully. Finally in worse case, for test purpose, connected PI QA jdbc channel to prod db table and encountered the same error as mentioned above.

Seemed to be an issue with the db table / data, but couldn't figure it out why java.lang.ArrayIndexOutOfBoundsException: 101 is being throwed in the jdbc sender channel while picking the data.

Could someone pls throw some light on the issue.

Br,

Saiganesh.

[Former Member]

We've found this error punctually accessing to MsSql databases with poor performance and abundant data locks. After   our basis  team had revised and improved parameterization of these databases this error disappeared.

I hope this help you...

Carme

[former_member193393]

Hi Carme,

As suggested, will try to chk reg the abundant data locks on the db table. But could you pls let me know a bit clear reg improved parameterization of the db i.e., what exactly has been done reg improved parameterization etc.,

Br,

Saiganesh.

[former_member437549]

HI Sai Ganesh,

This type of error occurs during the data conversion between back-end database table to XML.

Check the available data whether any special characters are there in data.

This might resolve your problem.

-Regards,

Siva Kumar.

[former_member214909]

Hi Saiganesh,

You need to check the length of the each field that you select from the table and also you can compare the value of the fields of previous successful message and the new which is failed. 

[former_member192343]

HI, did you see this ArrayIndexOutOfBoundsException (Java Platform SE 7 )?

Btw, have you any updates after select statment?

[former_member193393]

Hello Mikhail,

Went thru the link. But didn't get how tat helps in my case. Fyi the select & update statements in the jdbc adapter are as below:

SELECT PLANT, MATERIAL_CODE, WORKCENTER, OPERATION_NO, to_char( LAB_DATE,'DD-MON-RR HH24:MI:ss') LAB_DATE, SHIFT, PROPERTY_NO, VALUE, ERROR_DESC, PROCESS_FLAG, UOM, UPLOADED_DATE, PROCESSED_DATE, TAP_NO from DBTABLE WHERE PROCESS_FLAG = '0' order by PLANT, MATERIAL_CODE, OPERATION_NO, LAB_DATE, SHIFT, to_number(PROPERTY_NO) ASC

UPDATE DBTABLE SET PROCESS_FLAG = '1' where PROCESS_FLAG = '0'

Br,

Saiganesh.

[former_member192343]

The error says that the query is trying to access some array variable with index greater then 100, but what variable is it? is it complete select query?

[Former Member]

Hi Ganesh,

  Can you write the select statement like:

   SELECT PLANT, MATERIAL_CODE, WORKCENTER, OPERATION_NO, LAB_DATE, SHIFT, PROPERTY_NO, VALUE, ERROR_DESC, PROCESS_FLAG, UOM, UPLOADED_DATE, PROCESSED_DATE, TAP_NO from DBTABLE WHERE PROCESS_FLAG = '0' order by PLANT, MATERIAL_CODE, OPERATION_NO, LAB_DATE, SHIFT, PROPERTY_NO ASC.

    just remove the to_char function and try to execute .

Regards,

Anirban

[Former Member]

Hello Ganesh,

how does your PI structure look like? Is the field for ERROR_DESC maybe only 100 characters long?

Regards,

Jörg

[former_member193393]

Yes Mikhail,

Thats the complete select query

Br,

Saiganesh.

[former_member193393]

Hello Anirban,

Removed to_char for LAB_DATE field but not luck.

Br,

Saiganesh.

[former_member193393]

Hello Jörg,

Below is my PI structure. Excluded ERROR_DESC but still facing the same error.

fyi.. this was working fine till a couple of days back. Also this issue is only when picking up the data from prod db instance. When replicated recent (2 days) data to QA instance, there the same jdbc adapter was picking up the data successfully.

In worse case, connected PI QA jdbc channel to prod db table and encountered the same error.

Br,

Saiganesh.

[Former Member]

Hi Sai

Have you tried to use the same query in the oracle data base ??

Try the query in data base and see if it returns any rows or not. If the same problem occurs in data base also then you might need to work with DB team to fix it.

Check this blog below...

JDBC - Oracle ArrayIndexOutOfBoundsException - Stack Overflow

[Former Member]

Hello Saiganesh,

is there a view or a stored procedure on the db that you call (implicitly) to select the data? Maybe that one was changed.

Regards,

Jörg

[former_member193393]

No Jörg. It was a simple select statement straight away on a table.

Br,

Saiganesh.

[former_member193393]

Hi Dada,

Executing the same select statement on the related db table in toad is returning me the records where as from PI it is throwing java.lang.ArrayIndexOutOfBoundsException: 101 error.

Also, getting this error in prod environment only. When replicated the same data to QA instance db table, there the same jdbc adapter (PI QA) was picking up the data successfully.

Referred the blog but it tells abt the error during insertion where as our issue is while selection.

Br,

Saiganesh.