cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC Adapter - # character in element name

former_member184619
Active Contributor
0 Kudos

Hi,

We are having a scenarios to insert/update/delete data to DB2 AIX v9.7. Some database fields names are having an # character, for e.x. - SSKEY#SF. But, when we are trying to create a data type in PI for the corresponding database field, It is giving an error i.e. name contains invalid character, It permits use of only [a(A)-z(Z), 0-9 and “_”].

I have tried using XSD option. But that is also not allowing to have # character. Please provide your inputs.

Regards,

Sachin Dhingra

Accepted Solutions (1)

Accepted Solutions (1)

baskar_gopalakrishnan2
Active Contributor
0 Kudos

One simple approach is using the jdbc structure as follows...

<StatementName>  

<anyName action=” SQL_QUERY” | “SQL_DML”>

<access>SQL-String with optional placeholder(s)</access>

<key>

  <placeholder1>value1</placeholder1>

  <placeholder2>value2<placeholder2>

</key>

</anyName > 

</StatementName>

This way you pass the query with database table names in the access tag as string.  Pass values using placeholder.

Example in access tag use constant and write string something like this

select * from tablename where ddd#uut = '$placeholder1'

refer sap help link

http://help.sap.com/SAPHELP_NW04s/helpdata/EN/2e/96fd3f2d14e869e10000000a155106/content.htm

former_member184619
Active Contributor
0 Kudos

Hi Baskar,

I guess placeholder is used for Value for a particular field, I have a # character in field name itself.

For. ex. Can I use as below:

<access>UPDATE Customers SET CompanyName=’$#NAME$’</access>

<key>

  <#NAME>value1</#NAME>

<key>

and also, this field might not be a key field as well.

Regards,

Sachin Dhingra

baskar_gopalakrishnan2
Active Contributor
0 Kudos

Hi Sachin

               Placeholder names should be created  with proper allowed characters like this ... test1, test2 or so ... They don't represent exactly db column names. They just use for value substitution.

In this case, Your column name CompanyName is database column name.

Example:

Update customers set purch#aseID = '$test1'  where  ite#mId ='$test2' and na#me ='$test3'

where purch#aseID, ite#mid and na#me are  database column names handled in the query itself.

Use constant and write your string and assign to access tag

Note: I have written complex joining using three tables query using the above structure. We did have one & character in a  db column like this.

Option 2:

Otherwise why don't use Stored Procedure and do at the db level. Your problem is solved.  The same link what I provided would help for creating stored procedure and take help from dba to write and do it at db level itself. That is another possible approach.  Defining proper names is our issue. SAP would have to follow that in creating structure.

Hope this helps

former_member184619
Active Contributor
0 Kudos

Hi Baskar,

I have got your point, But for me there is around 100 fields in the table and also database action is dynamic (INSERT/DELETE/UPDATE).

Do i need to create a string with all fields or there is a way around?

Regards.

Sachin Dhingra

baskar_gopalakrishnan2
Active Contributor
0 Kudos

Follow the option 2  Stored procedure

Answers (4)

Answers (4)

former_member184619
Active Contributor
0 Kudos

Hi All,

Thank you for your inputs. We have asked SAP for the same as well. They have suggested two options:

1. Stored Procedure on DB side

2. SQL_DML on PI side, as replied by Baskar.

Regards,

Sachin Dhingra

rajasekhar_reddy14
Active Contributor
0 Kudos

Ask your data abse team to chnage field names .

If they are not willing to do then ask them to create one more staging table in data base with valid name(according to PI) and then insert data to Stanging table then request them to process further.

you can try using XSLT mapping but still i dont recommend.

Regards,

Raj

former_member184619
Active Contributor
0 Kudos

Hi Raj/Greg,

Thanks for your inputs, I have the XSLT option. But structure being so big and having a multiple table update. I don't want to use that.

Client is not willing to make any change on Database. They have asked us to raise it to SAP

Please suggest any other option.

Regards,

Sachin Dhingra

former_member184619
Active Contributor
0 Kudos

Hi Raj,

Can you brief me the cons of going with XSLT approach.

Sachin

baskar_gopalakrishnan2
Active Contributor
0 Kudos

You can use XSLT mapping to modify the structure element.  This is not a problem. Only downside I see it if you add new columns of this naming convention then you have to modify in addition to structure, xslt mapping as well. 

Plus XSLT mapping does have slight performance issues compared to java mapping. If your structure is very complex and you want to navigate nested structure or so then XML parsers does save the structure in cache.  Whereas Java mapping is not and it is so powerful in performance. But I'm not seeing big drawbacks in using XSLT.

BTW, since you meant the query is complex and describing naming convention issues, Why dont you consider using my second option  towards Stored Procedure.

Hope that helps

former_member184619
Active Contributor
0 Kudos

Thanks Baskar for detailed reply, I am open to stored procedure already. But client should be ready for that .

Regards,

Sachin Dhingra

sunil_singh13
Active Contributor
0 Kudos

Sachin,

Why cant you create a structure with a unique placeholder for "#" in the datatype/External Defination and then use graphical mapping  in first step and in the second step use a java mapping and have a simple line replaceAll (UniquePlaceHolder, #) and then adapter will take care of building the query.

Thanks,

Sunil Singh

former_member184681
Active Contributor
0 Kudos

Hi,

You can try with a little workaround: in the data type, use some other characters combination, like ___ (three underscores, just to be sure), and use an additional Java/XSLT mapping to change the tag name in the XML payload after the "main" mapping.

Regards,

Greg

iaki_vila
Active Contributor
0 Kudos

Hi Sachin,

Have you tried with the escape tag &#35; ?

Regards.

former_member184619
Active Contributor
0 Kudos

Hi Vila,

Should I use the same in XSD and import it ?

Regards,

Sachin Dhingra

former_member184619
Active Contributor
0 Kudos

Hi Vila,

I have tried using the same, It is giving me below error, after importing:

Unable to convert imported document to WSDL

Reason:

Unable to recognize document as valid XSDCheck selected category

Although, imported document shows the element name is XSD : <xsd:element name="LMKEY#LM" type="xsd:string">

Regards,

Sachin Dhingra