Solved

Infopath - Gathering Data using Particular Character Set

Posted on 2010-11-12
6
710 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:
clayfox earned 500 total points
Comment Utility
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
Comment Utility
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:clayfox
clayfox earned 500 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Beircheart
Comment Utility
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:clayfox
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now