on 05-09-2006 6:59 PM
Hello everybody, I got a question for you about quotes in string types.
I'm trying to send data from my R3 to DB2 in AS400 and when the XI creates the SQL statement to insert the data it requires that each string is enclosed between quotes, otherwise an error will occur.
In the mapper I only assign directly the source field to the target field without any operation and this way my string won't be enclosed between quotes, my question is, Is there a way to do the same operation but getting in the target field the string between quotes?
I tried with patterns and got what I was looking for, but I'm looking for a solution that applies to all strings where I only do it once and forget it for ever.
Regards
Hello everyboy, I'm happy to tell you that this topic was solved, what we did was to add a parameter in the connection string:
translate binary=true
with this the XI is adding automatically the quotes for each string of our data type, this will save us a lot of time insted of adding the quotes manually.
Thank you everybody for your help.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Bhavesh, in fact that's my scenary but for some reason, I don't know why, XI is not doing correctly the SQL statement conversion, in my case is doing something like:
insert into tablename(name,company) values(bhavesh,wipro)
What I'm doing is receive a message and within BPM I execute a mapping that splits the message in 2, one message for a different file in my DB2; after that I'm sending each message to DB2 (still in the BPM) and there's where the error occurs.
Mr. Riedel, how did you solve the issue in your case?
Thank you all
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi ,
like mentioned by shree, try using SQL_DML and check it out.
For SQL _DML, just check this link,
http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm
Regards,
Bhavesh
Thanks everybody!!
I have tried the concat function and it works fine but if I had a data type with 100 strings I must do the same operation for each string, what is what I'm trying to avoid.
About the escape symbol for apostrophe my XI system is replacing it each time it founds it; what I did here was to tell XI to replace the apostrophe with another apostrophe so the result will be the same as the input.
I have checked the SQL statement that XI is executing in my DB2 and XI is not adding any apostrophes for each string field what would be what my database is expecting, I don't know if that is something that I should configure in the JDBC driver... I'm using the Toolbox driver.
What I ment with "do it once and forget it for ever" was for example, to configure some kind of parameters in XI or the JDBC one time without the need to do something extra in the mapper.
Does anyone have an idea why XI is not adding the quotes or if there is such parameter?
Thanks a lot.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi,
if this is your JDBC target datatype,
<root>
<statement>
<tablename action="insert">
<access>
<name>bhavesh</name>
<company>wipro</company>
</access>
<tablename>
</statement>
</root>
Internally XI converts this into SQL query,
<i>insert into tablename(name,company) values('bhavesh','wipro')</i>
http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm
Am still not able to understand why you have to give the QUOTE ' manually in your mapping.
Please let me know if i have missed something here.
Regards,
Bhavesh
Santana
The apostrophe character () is a reserved character in SQL syntax and is therefore replaced by an escape character if it occurs within value strings. This replacement character can be database-specific. Typical replacement characters are \ or (default value). For further info go through :
http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm
http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm
Also for additonal info go through:
---Satish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Internally XI will convert the data under your STATEMENT tag into an SQL query of the format expected by your corresponding Database. You need not manually insert the single quote for Strings/ Charatcers. this will be handeld by XI itself.
If you want to pass a special symbol as part of your data to be inserted into the database, just append that character along with your data and XI will do the rest.
Regards,
Bhavesh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI,
Use String Concat Function from the standard function available in the Mapping.
Hope this helps,
Regards,
Moorthy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
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.