on 05-23-2012 5:20 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Sachin,
Have you tried with the escape tag # ?
Regards.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.