How to handle fieldnames ending with # in JDBC receiver?

[Former Member]

Hello Experts,

I am developing a scenario JDBC to IDOC. I have 2 tables for header & line item. I have to retrieve a header record first using sender JDBC & then for that header need to fetch the corresponding lineitems from second table.

We cannot have data types defined in XI with fieldnames containing # or any other special charecter. Both DB tables contain fieldnames ending with #.

For Sender JDBC, I managed it in SQL query using :

Select abc, xyz# as xyz .... from tbname

But for receiver JDBC we need to define a data type with the typical format ( Doccument format required for JDBC receiver).

Please let me know how to handle this for JDBC receiver.

Thanks in Advance & hope for a quick replies.

Abhijeet.

[Former Member · Accepted solution]

Abhijeet,

How about using joins between header and line item table rather than having 2 different select query?

As yuo said you can used sql statement to manage "#"

"Select abc, xyz# as xyz .... from tbname"...so you will have one single select query with join for header and line item.

Nilesh

[Former Member · Accepted solution]

Hi Nilesh,

Thanks for quick response. I will try for this option.

Is this the only way to handle this situation?

Each table involved contains around 100 or more fields, so to have a join becomes a complex job.

Please let me know about any other alternatives if possible.

Abhijeet.

[Former Member · Accepted solution]

Hi Abhijeet,

If you can write a query with join( you can call join query using JDBC adapter), i think this would be a fastest way and good approch rather than having 2 JDBC call.

Another approch would be writing SP( stored procedure and then you can have your complete logic here). You can call SP using JDBC adapter.

Eventhough query would be complex...it will be one time job.:)...I am not sure about data type for handling field names with special characters.

Let me know if you need more details.

Nilesh