SQL 2005 Insert Error

[Former Member]

Hi Folks-

It has been a while and I hope this finds you all doing well.

I am working oin a customer application and we are experiencing intermitment MS SQL 2005 insert errors. We have isolated the cause as the use of the special character, the apostrophe (i.e. " " ") in a material/item description. The underlying MES application can accept the apostrophe along with the SAP MM back-end, but not MII, The MII version is 12.0.6 Build 14. The application also uses the Java Plug-in version 1.6.0_07, which is also the Java Runtime version. I am not sure of the SQL jdbc being used but was hoping that perhaps there was a version that could "handle" this special character issue or if there was a non-programmatic way to address this. At this point we are anxious about changing Master Data.

Any insights or suggestions would be appreciated. Thanks in advance and I look forward to your responses.

Kevin Fitzgerald

Invensys Operation Management

[Former Member · Accepted solution]

Unfortunately this isn't an MII compatibility/version issue.

You can try modifying your sql insert query to use the REPLACE function, e.g.:

REPLACE(<description>, '"', '""',)

.

[Former Member · Accepted solution]

Thanks Christian- still hoping to find something with a bit more finesse. We shall see......

Kevin

[Former Member · Accepted solution]

Hi Kevin,

The problem is about entering apostrophes in SQL using MII SQL queries.

The solution would be:

1. Do a check in the string before sending the input string to SQL query.

2. If the string contains special character (example ' ) then replace ' with '' (two ttimes ').

By doing this, we will not change any of the master data and correct data will be inserted successfully.

Hope this is useful.

Thanks and Best Regards,

Kedar Shewalkar

[Former Member · Accepted solution]

Hi Kevin,

Just to give more details:

Rest of the characters including ! @ #$%^&*()_+?><.. work fine on MII SQL queries except u2018.

(There is logical reason for this as well u2013 Because MII SQL queries take string parameters in apostrophes u2018 so if any input string already contains u2018 then it creates an extra u2018. To resolve this we need to add one more u2018.)

The correct solution is to include extra escape character (u2018).

Best Regards,

Kedar

[Former Member · Accepted solution]

Hi Kedar-

This is great information and I thank you for your insights. I do have what I hope are 2 brief follow-on questions:

1. Does the single quote/apostrophe represent a general escape character that can be used in all cases? Are there others (i.e. \n, etc.)

2. Your special characters cited above do not include the asterik, *. My customer has also decided to use this special character in a new product description (go figure!!). Can this be "escaped out" via the same means?

I have been able to successfully use the java function stringreplace to alter the text as you suggest- even for a local and I hope transaction variable. Any additional comments per my questions above would certainly advance the cause.

Thanks again and I look forward to your comments. Take care and be safe.

Kevin

[agentry_src · Accepted solution]

Hi Kevin,

Perhaps you should have the customer take a look at this thread? There have been many times that I have seen customers who are careless about do's and don'ts regarding database practices.

It is pretty frustrating to see people with preventable self-inflicted wounds. Also see it in xml structures, especially in TagNames (initial character being numeric or even sometimes a special character)from historians, but those tend to be legacy (and much older) systems being integrated, so more understandable.

Regards,

Mike

[Former Member · Accepted solution]

Hi Kevin,

About the questions you asked:

Single quote (apostrophe) can act as en escape character for single quote (')only. Rest all the characters including * can be added into the database successfully as it is.

Is the query failing for any other characters except ' ?

I don't think it will fail for any other characters.

Best Regards,

Kedar

[Former Member · Accepted solution]

This is grat Kedar- your assistance is appreciated. We have created and tested a solution baed on your guidance and are positioning with the client. Thanks again.

Kevin