cancel
Showing results for 
Search instead for 
Did you mean: 

Quotes question - JDBC and AS400

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (6)

Answers (6)

Former Member
0 Kudos

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.

bhavesh_kantilal
Active Contributor
0 Kudos

Hi,

Nice to know that it worked Another learning for all of us here at SDN.

Close this thread by rewarding points,

cheers,

Bhavesh

Former Member
0 Kudos

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

Former Member
0 Kudos

Hi Santana,

Can you try to use SQL_DML instead of INSERT in the statement so that you have flexibility to write your own SQL statement i think this might solve your problem.

Regards,

Sri..

bhavesh_kantilal
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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.

bhavesh_kantilal
Active Contributor
0 Kudos

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

Former Member
0 Kudos

I ran into a very similar problem while writing an IBM WebSphere Portlet. We were using the OS400 Toolkit too. We did not find any settings in the JDBC driver or metadata that help to quote strings more appropriately.

Former Member
0 Kudos

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

bhavesh_kantilal
Active Contributor
0 Kudos

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

moorthy
Active Contributor
0 Kudos

HI,

Use String Concat Function from the standard function available in the Mapping.

Hope this helps,

Regards,

Moorthy