This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes A stange error while storing single quote in a table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "A stange error while storing single quote in a table" Watch "A stange error while storing single quote in a table" New topic
Author

A stange error while storing single quote in a table

saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
here i am inserting some data into a servlet using textarea from a jsp page

I copying the text into textarea from ms word document file

the text contains single quotes so while i am copying it into text area the single quotes are not taken as single quotes(may be other ascii code ,i think so)

and in the servlet i am receving the text from the textarea and displaying it to console.

in tomcat console where ever the text contains single quotes they are converted into question marks

why it happens like that?



I want the exact solution

please help me
this is very much important for me..



cinux


A = HARDWORK B = LUCK/FATE If C=(A+B) then C=SUCCESSFUL IN LIFE else C=FAILURE IN LIFE
SCJP 1.4
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
That's common copying from MS Word. Word doesn't use standard single quotes (sometimes). If you look on screen or in printing carefully you may see that balanced single quotes around a word slant inward at both ends. I think you can turn this off (look for smart quotes in Word help) but if you can't force users to do that you may have to find the character values of those things and replace() them out.


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

Originally posted by saikrishna cinux:
I want the exact solution


All right then. The single quotes from word aren't true single quotes, so the system is classifying them as unknown or ? characters.

Word likes to convert to formatted text. For example, double quotes as in, "See Spot Run", are converted such that the first set of quotes faces right and the second set faces left. You must convert them to normal quotes either in Word or your application before your system/database will recognize them.

Don't forget to convert them to double single quotes such as '' instead of ' since a single quote is a reserved character in most DMS's.


My Blog: Down Home Country Coding with Scott Selikoff
Brian Cole
Author
Ranch Hand

Joined: Sep 20, 2005
Posts: 862
Originally posted by Scott Selikoff:
Don't forget to convert them to double single quotes such as '' instead of ' since a single quote is a reserved character in most DMS's.


Better yet, just use a PreparedStatement instead of
a plain Statement for the insert and let JDBC handle
escaping them for you.

PreparedStatement ps = conn.prepareStatement(
"INSERT INTO tab (col) VALUES (?)");
ps.setString(1, myStringThatMayHaveUnescapedChars);
ps.executeUpdate();

I'm not sure why so few JDBC programmers know to do this.


bitguru blog
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703
    
    5

Originally posted by Brian Cole:
I'm not sure why so few JDBC programmers know to do this.


Primarily, because I avoid PreparedStatements in most situations (except for INSERT statements with many arguments). I like fully automated solutions like JDO/Hibernate or fully manual solutions like direct JDBC. To me, PreparedStatement is one of those things that isn't quite automated and isn't quite manual, so I tend to avoid it.

That, and it doesn't support SQL IN clauses well.
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
The problem is not with database
i using prepared statement for storing the fields into the database

As soon as i receive the text using httprequest.getparameters("txt");
i am displaying it on the console.

so, the problem is with textarea not with database


the textarea is not able to handle the word document data as richtextformat (word pad).

Is there any way to handle the text as richtextformat. in html or in other technologies like java serverfaces


thanx in advance


cinux
saikrishna cinux
Ranch Hand

Joined: Apr 16, 2005
Posts: 689
I got the solution my self
I request the author of this site must keep this as a FAQ's in JDBC section

This problem is the most frequesnt one.

I am explaining it again
when ever we copy the data from microsoft word document to Textarea of HTML page then the single Quotes(') and double Quotes (") will be changed to some other ASCII char
so, In the servlet we take the request from the user using String

data=request.getParameter("textarea");
char ch1=(char)146;//here 146 is the ASCII code of singleQuote(singlequote of ms word not an actual single quote)

char ch[] =new char[]{ch1};
StringBuffer sb1=new StringBuffer();
String token=new String(ch);
StringTokenizer st1 = new StringTokenizer(data,token);
while(st1.hasMoreTokens())
{
sb1.append(st1.nextToken() +"\'");
}
System.out.println(sb1.toString());//here it diplays actual single quote




In the same way we need to convert the doubleQuote with it's ASCII code.


I hope you will accept my request.


Thanking you sir,

regards,
cinux
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

Originally posted by saikrishna cinux:

I request the author of this site must keep this as a FAQ's in JDBC section


The Java Ranch FAQ is actually a WiKi, so you can add it yourself.
See http://faq.javaranch.com/view?SandBox


The future is here. It's just not evenly distributed yet. - William Gibson
Consultant @ Xebia. Sonny Gill Tweets
Louise O'Connor
Greenhorn

Joined: Aug 07, 2012
Posts: 1
You can also just do a replace
i.e.
public static String removeWordEditing(String input) {
if (input == null) {
return "";
}
char leftQuote =(char)145;
char rightQuote = (char)146;
char leftDoubleQuote =(char)147;
char rightDoubleQuote = (char)148;

return input.replace(leftQuote+"", "'")
.replace(rightQuote+"", "'")
.replace(leftDoubleQuote+"", "'")
.replace(rightDoubleQuote+"", "'");
}


adding in all the chars you want to convert. Going to Symbol->More Symbols in Word doc shows you their ascii codes.

Louise.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: A stange error while storing single quote in a table
 
Similar Threads
Executing query from java program
unnable to get the return key from jsp
Grails question
Request to the moderator
how to handle the text which is copied from doc file