Database Adapter: Hidden Treasure-1

Dealing with special characters

Problem: On working over one of my clients project I faced a peculiar issue. The issue was, I was trying to map my source column database(My case the database was AS/400) table to the destination column database(Oracle). My source table column name was containing $ signs like P$id,P$name.
This blog is maining written to deal with such problem.

Solution:I raised this issue on oracle forum and parallely I also did some research on this. What I came to know is not all special characters are allowed in XSD element/attribute names e.g. $, #, etc..
But finally I solved the problem.Let me show this with an example:

1) Create a dummy table with column name containing $ sign.

create table test(
p$id int,
p$name varchar2(20)
);

2) Create a BPEL or ESB project. Use database adapter in your project. You should be sure, what your project is going to do.

3) When the database adapter configuration is done. Goto the project folder and look for corresponding toplink and XSD files. We can find two files corresponding to it in our project folder.It will be basically <Service name>_table.xsd file and <Service name>_toplink_mappings.xml file.

4)Open the <Service name>_table.xsd file and change the element name for p$id and p$name to p_id and p_name. Save it.

5) Open the <Service name>_toplink_mappings.xml. Look for the tag <opm:attribute-mappings>. Only replace the "$" sign with an "_" for column name. I have made that text bold for better visibility.
Here I am pasting my piece of code.

<opm:attribute-mappings>
<opm:attribute-mapping xsi:type="toplink:direct-mapping">
<opm:attribute-name>p_id</opm:attribute-name>
<opm:field table="TEST" name="P$ID" xsi:type="opm:column"/>
<opm:attribute-classification>java.math.BigDecimal</opm:attribute-classification>
</opm:attribute-mapping>
<opm:attribute-mapping xsi:type="toplink:direct-mapping">
<opm:attribute-name>p_name</opm:attribute-name>
<opm:field table="TEST" name="P$NAME" xsi:type="opm:column"/>
<opm:attribute-classification>java.lang.String</opm:attribute-classification>
</opm:attribute-mapping>
</opm:attribute-mappings>

6) Save it. Thats it.

Note:
No toplink mapping is used in case you are dealing with custom SQL. Conversion from $ value to _ is done automatically. So when DB adapter creates XSD file, it creates attributes with p_id and p_name.

Happy Learning...