Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Infopath - Gathering Data using Particular Character Set

Posted on 2010-11-12
6
Medium Priority
?
725 Views
Last Modified: 2012-06-21
Hi,

I am currently involved in a project which uses an Infopath form to gather data and then submits that data to an Oracle database. (Infopath Form is hosted on Sharepoint)

If a user populates the form fields by typing in the data, there's no problem All the data is entered in to the DB correctly.

However, if a user copied and pasted text in to the form from Word for example, then there can be problems. For example, single quotes, tabs and/or other special characters often appear as squares or upside down question marks (¿).

This seems to be because the character that is being passed by Infopath is not a valid character in the Oracle DB character set.

Does anybody have any advice on how to rectify this?

Thank you in advance

Ben
0
Comment
Question by:Beircheart
  • 3
  • 3
6 Comments
 
LVL 28

Accepted Solution

by:
Clay Fox earned 1500 total points
ID: 34121256
You would probably need to usesome conditional rules which would check for special characters and then remove them.
You can use a concat function to combine a substing-before the special character and substring-after the special character.

You would need one of these per special character type or you could recursively check until they are all gone.
0
 

Author Comment

by:Beircheart
ID: 34121385
Thank you for your response clayfox but unfortunately it is a very large form with over 100 fields so this solution would not be ideal for my circumstance.

I may need to fix this (Using a similar solution to what you suggested) as it is being entered in to the DB. i.e. Insert \ Update statements are triggered on Submit, these statements could strip charcters as you described. Better again, there may be a way to convert the strings to another character set within the statement?? Reaching?

The only problem with this is that I don't have access to these statements at the moment. An external contractor developed that aspect and their keeping it in a black box at the moment. If I touch it and anything goes wrong, they'll just say I broke it and wash their hands of it...
0
 
LVL 28

Assisted Solution

by:Clay Fox
Clay Fox earned 1500 total points
ID: 34121468
I assume you are using web services to move the data into Oracle. Ther emay be a way to have it cleaned on the server side.

I am guessing the InfoPath is using rich text controls. If you used text boxes it would reformat for you I am pretty sure. The trade off is that you would lose any special formatting but I am not sure Oracle can perserve that anyway.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Beircheart
ID: 34121874
That's correct, web services are being used to populate the DB.

In Infopath however, I am using standard Text Box controls rather than Rich Text Boxes so unfortunately that doesn't do the trick.

Do you think modifying the web service is the way to go? Or have you any other thoughts on this?

Thanks again for your help clayfox
0
 
LVL 28

Expert Comment

by:Clay Fox
ID: 34129620
Not sure if you could fix it moving data from one field to another.  

Can you not edit the infopath form?

I would screen for the characters if it was me.
0
 

Author Closing Comment

by:Beircheart
ID: 34195901
clayfox, even though you answered everything I asked in a timely manner, I'm only awarding a B because I can't imagine this solution being very useful to someone else due to the fact that there is no code or explanation of how to implement any of the suggested fixes. It just so happens that I think I know how to do it whereas someone else may not.

Thank you for your help

Ben
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month15 days, 16 hours left to enroll

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question